TechandMate provides in depth knowledge of VBA, automation and Macro development, advance features of excel 2013.
Online VBA Programming Tutorial covers all the excel/VBA functions and features of excel VBA like all the formulas, forms, pivot table, pivot chart, advance filter, Slicer, Go to, Remove duplicates, data validation, sorting, protection of excel sheets and workbooks via VBA coding.
TechandMate brings you the set of widespread must asked Interview questions for VBA. Let’s Learn and understand the concept of VBA in details with examples to give you better clarity.
1. Explain what is VBA or Visual Basic for Applications?
VBA is MS office scripting language of Visual Basic, It is an event driven programming language. VBA is developed in 1993 by Microsoft and stably,it was released in office 2013. VBA is closely relates to Visual basic and uses the Visual Basic Run time Library however VBA code can only run within a host application.
VBA is built in most of the office products like MS Excel, MS word, MS PowerPoint, MS Access etc. It can be used in creating Macro, developing tools and automation. VBA can control one application from another hence it can be connected through databases like MS Access and SQL server etc. to store the data
2. What are the data types in VBA?
The manner in which a program stores data in memory is referred to as the data type.
A data can be a number, text, date or time etc. so it can be specified in different data types. For example a number can be specified in integer, long or double but there is a range assigned to each data types. You can refer the below table to understand the concept in better way-
3. In VBA how we can declare variable and constant?
In VBA, it is not mandatory to declare the variable but it is good practice to declare a variable. Before using any variable you can declare the datatype by writing one liner code-
To declare a variable: You can use your own variable and your own data type as per requirement.
Dim Variable_Name as Data_Type
To Declare a Constant: Constant are declared in a similar way just you have to assign a value to the constant, Example-
ConstMaxCount = 20
4. Explain, what is “Option Explicit” in VBA?
VBA doesn’t force you to declare the variable but you use Option Explicit in your program it will force you to declare the variable before using it.
If you want to use option explicit in your program you just need to write “option explicit” in your module at the top of VBA file.
5. Why using Option Explicit in your program is a good practice?
When you use any variable without declaration, by default it will have variant data type whether it is being used for number or text. Therefore you can use any variable in your program whether it is declared or not, Excel will assign variant data type to it.
However this is not a good programming practice for the following reason-
- Memory and calculation speed
- Prevention of typo bugs
- Highlighting unexpected data values
6. How option Explicit play a vital role in Memory and calculation speed?
If you don’t declare variable datatype, by default, it will have variant datatype. This takes up the memory for many other data types while it can be managed in smaller data types like Integer or string.
It is very often programmers use thousands of variables in their program, therefore the additional memory occupied to store variant instead of Integer or String.
7. How option explicit prevent typo bugs in programming?
If you always declare VBA variable in your programming, then you can prevent typo bugs in your program, how? It is explained below with an example-
Let’s say if you have declared a variable XYZ as integer and you want to assign a value into it. Suppose by mistake you wrote WXYZ = 123, when you will execute your program, it will throw an error that you haven’t declared WXYZ as a variable. Probably it happens with a programmer and if you don’t declare option explicit in your program, it will not throw any error but ultimately this can be a big issue and you can prevent typo bugs by using Option explicit.
8. How option explicit highlight unexpected data values in programming?
If you are using option explicit in your program it will force you to use appropriate data type. If you have declared a variable for integer data type it will force you to assign integer data type. If you will assign any other data type except of integer, when you will execute the program it will throw an error to use correct value. Hence it can be prevent passing unexpected data values.
9. What is the code to find a last used Row in a column or last used Column of a row?
Find last used Row in a column:
‘use End(xlUp) to determine Last Row with Data, in one column (column B)
Dim lastRow As Long
‘Rows.count returns the last row of the worksheet (which in Excel 2007 is 1,048,576); Cells(Rows.count, “B”) returns the cell B1048576, ie. last cell in column B, and the code starts from this cell moving upwards; the code is bascially executing Range(“B1048576”).End(xlUp), and Range(“B1048576”).End(xlUp).Row finally returns the last row number.
lastRow = ActiveSheet.Cells(Rows.Count, “B”).End(xlUp).Row
Last used Column of a row:
‘use End(xlToLeft) to determine Last Column with Data, in one row (row number 2)
Dim lastColumn As Integer
lastColumn = ActiveSheet.Cells(2, Columns.Count).End(xlToLeft).Column
The codes will work as required but there is some limitation we need to know before using it-
- If the last row is very last row in excel spreadsheet (viz. row 65,536 in Excel 2003 or 1,048,576 in Excel 2007+), or if the last column is very las column, it will not consider as last row.
- If the row/ column is blank it will return 0 as output.
- This is the most commonly used method to identify the last row
10. If we want to figure out the last row whether it is very last row if excel, what should be the code for it-
In the above question you have seen the most commonly used code is not working for very las row while it is being used very often. If we want to figure out the last row in a column whether it is very last row you should refer below code-
‘UsedRange property to find the last used row number in a worksheet
Dim lastRow As Long
lastRow = ActiveSheet.UsedRange.Row – 1 + ActiveSheet.UsedRange.Rows.Count