MS Excel VBA Interview Questions and Answers with Examples

MS Excel VBA Interview Questions and Answers explained with Example macros. Here are the list of most frequently asked basic MS Excel VBA interview questions. All the questions are answered and explained with simple examples. These questions will help you to answer basic questions to deal with MS Excel VBA Programming/Development.

Please find the below basic Object model of Excel.
Application –> Workbooks –> Worksheets –> Range / Chart

A macro is nothing but set of instructions which are stored in Visual Basic module in a VBA Editor. It helps in automating common repetitive tasks on daily, weekly or monthly basis by running macro. Using macros, you can save lot of time, increase productivity and on time delivery to customers.

You can use the ‘Alt+F11’ key to go to VBA editor screen

Please find the following steps to stop recording macro in the workbook.
Step 1: Go To Developer tab from the main ribbon of Excel window.
Step 2: Click on ‘Stop Recording’ command button to stop from the recording macro.

Please find the following steps to delete macros from the workbook.
Step 1: Go To Developer tab from the main ribbon of Excel window.
Step 2: Click on the Macros command button to see the available macros in the active workbook.
Step 3: Once you click on the Macros command button, Macro dialog box will appear on the screen.
Step 4: Select macro name which you want to delete macro and then click on ‘Delete’ command button.
Step 5: Now, It will show the confirmation dialog box. Click on Ok to delete the macro.

This is one of the most commonly asked Excel VBA Interview Questions and Answers. You can use Workbook_Open() Event to run macros automatically in Excel VBA while opening Workbook.

To get Workbook_Open() Event in Excel, please find the following steps.
1. Go To VBA Editior.
2. Click on ‘ThisWorkbook’ from the Project Explorer.
3. Now, you can see two drop down lists in the right side.
4. Select ‘Workbook’ from the first drop down list and then choose ‘Open’ from the second drop down list.
5. Now, you can see the following code.

Private Sub Workbook_Open()
     ‘Your Statements…..
End Sub

6. You can add the code in-between the above lines to run a macro.
7. Save and close the workbook
8. Now, reopen the workbook to test the macro.
Example:

Private Sub Workbook_Open()
    MsgBox “Workbook has Opened Successfully.”, vbInformation
End Sub

In the above example, the macro will run automatically when we are opening workbook. Now, it will display message like “Workbook has Opened Successfully.”.

Or we can also define a procedure named Auto_Open() in any code module, this will execute while opening the macro file.

You can use Workbook_Open() Event to show UserForm automatically in Excel VBA when we open Workbook.

To get Workbook_Open() Event in Excel, please find the following steps.
1. Go To VBA Editior.
2. Click on ‘ThisWorkbook’ from the Project Explorer.
3. Now, you can see two drop down lists in the right side of the VBA Editor window.
4. Select ‘Workbook’ from the first drop down list and then choose ‘Open’ from the second drop down list.
5. Now, you can see the following code.

Private Sub Workbook_Open()
     ‘Your Statements…..
End Sub

6. You can add the code in-between the above lines to run a macro.
7. Save and close the workbook
8. Now, reopen the workbook to test the macro.
Example:

Private Sub Workbook_Open()
    ‘ Here “MyForm” is the UserForm name.
    MyForm.Show
End Sub

In the above example, the macro will show the UserForm(Named ‘MyForm’) automatically when we open Workbook.
Note: Before running above macro add UserForm and then assign the name of the UserForm to ‘MyForm’.

ByVal vs ByRef in VBA is also one of the most frequently asked Excel VBA Interview Questions and Answers.
ByVal:
Specifies that an argument is passed in such a way that the called procedure or property cannot change the value of a variable underlying the argument in the calling code.
ByRef:
Specifies that an argument is passed in such a way that the called procedure can change the value of a variable underlying the argument in the calling code.
Note: Default value is ByRef. It is good practice to include the ByRef declaration if you are going to change the value of the parameter.

Please find the different looping statements which are available in Excel VBA.
For…. Next loop, Do While…. Loop, Do until Loop, Do….Loop Until..,Do While Not…Loop, While…. Wend loop

Please find the following steps to add UserForm or Module or Class Module to the VBA Project.
Add UserForm:
Step 1: Go To Insert menu in the VBA Editor window.
Step 2: Click on ‘UserForm to add to the Project. Now you can see added UserForm in the Project Explorer. Default UserForm name will be ‘UserForm1’. You can change the UserForm name with using properties

Add Module:
Step 1: Go To Insert menu in the VBA Editor window.
Step 2: Click on ‘Module’ to add to the Project. Now you can see added Module in the Project Explorer. Default module name will be ‘Module1’. You can change the module name with using properties.

Add Class Module:
Step 1: Go To Insert menu in the VBA Editor window.
Step 2: Click on ‘Class Module’ to add to the Project. Now you can see added Class Module in the Project Explorer. Default Class module name will be ‘Class1’. You can change the class module name with using properties.

We can create object variable and it can use in entire procedure. Please find the following examples to create object for workbook, worksheet, etc.
Create object for Workbook:

Sub Create_Object_Workbook()
    Dim Wb As Workbook
    Set Wb = ActiveWorkbook
    MsgBox Wb.Sheets(1).Name
End Sub

Explanation: In the above example, I have created and assigned to Active Workbook to ‘Wb’ objet. And then I have used it in the next statement(MsgBox Wb.Sheets(1).Name) to display first worksheet name.

An array is a set of variables that are similar type. Using an array, you can refer to a specific value of an array by using array name and the index number (also called subscript).

We can create and define size of an array variable in the following way.
Dim ArrayName (IndexNumber) As Datatype
Example: Dim aValue(2) As Integer
In the above statement ‘aValue’ is an array name and ‘2’ indicates an array size.

We can assign values to an array in the following way.
‘Declare an array variable
Dim aValue (2) As Integer
aValue(0)=“first”
aValue(1)= “Second”
aValue(2)= “Third”
‘Or
Dim aValue () As Integer={“ first “,”Second”,”Third”}

Data Type: A data type tells, what kind of variable we are going to use in a procedure or function. The information that specifies how much space a variable needs called a data type.
Before using variable, we need to know how much space the variable will occupy in memory, because different variables occupy different amount of space in memory.

We can declare the variable in the following way.
Dim VariableName as Datatype

Example:
Dim iCnt as Integer

Where iCnt represents VariableName and Integer represents Datatype.

The following are different data types which are available in Excel VBA.
Byte, Boolean, Integer, Long, Single, Double, Currency, Decimal, Date, Object, String, Variant and User defined data types.
For more information please find the following link.

Read More …

UserForm Controls:
Button, Combo Box, Check Box, Spin Button, List Box, Option Button, Group Box, Label, Scroll Bar, etc,.
ActiveX Controls:
Command Button, Combo Box, Check Box, List Box, Text Box, Scroll Bar, Spin Button, Option Button, Label, Image, Toggle Button.

Please find the following steps to assign macro to a button.
Step 1: Go to the Developer tab from the excel ribbon menu, go to Forms Control group.
Step 2: Click on Button from the Form Controls.
Step 3: Click the worksheet location where you want the button to appear.
Step 4: Drag the button in the sheet.
Step 5: Right click on the button, click on Assign Macro.
Step 6: Assign Macro Dialog box will appear now, click the name of the macro that you want to assign to the button. Click on OK.
Step 7: You can format the control by specifying control properties.
Step 8: Click on button to test. Now, your macro should run.

Understanding the scope of variables is very important for VBA Developers, it is also one of the most frequently asked Excel VBA Interview Questions and Answers.

When we are working with variables, it is important to understand the Scope of a Variable. The Scope describes the accessibility or life time or visibility of a variable.
There are four levels of Scope:
• Procedure-Level Scope
• Module-Level Scope
• Project-Level Scope
• Global-Level Scope
For more information please find the following link.

Read More …

We need to find Last used Row with data if we want to perform certain task on each row of worksheet. Please find the following statements to find last row in the worksheet.

Find last row in the worksheet:

Dim lastRow As Long
lastRow = ActiveSheet.Cells.SpecialCells(xlLastCell).Row
For more information please find the following link.

Read More …

We need to find Last used Column with data if we want to perform certain task on each column in the worksheet. Please find the following statements to find last column in the worksheet.

Find last column in the worksheet:

Dim lastColumn As Long
lastColumn = ActiveSheet.Cells.SpecialCells(xlLastCell).Column
For more information please find the following link.

Read More …

We have several best practices to follow while coding VBA. This is also one of the most frequently asked Excel VBA Interview Questions and Answers. This helps interviewer to understand your real time experience in VBA.
We can fasten the execution of macros or VBA Procedures by following the below tips.
1. Declare the variables and avoid using ‘Variant’ Data Type.
2. Turn Off Screen Updating
3. Turn Off Automatic Calculations
4. Disable Events
5. Use With Statement
6. Use vbNullString instead of “”.
7. Release memory objects at the end of the procedure.

Enabling and Disabling the Screen updating will be used in almost all projects. Questions on understanding the screen updating are also one of the most frequently asked Excel VBA Interview Questions and Answers.
Here is the approach to enable or disable screen updating or screen flickering.
In order to stop the screen flickering, stop the screen updating at Staring of the procedure:

Application.ScreenUpdating = False

You have to set back screen updating as True Before ending of the procedure:

Application.ScreenUpdating = True

Here is the approach to stop trigger or display alerts or error warnings.
In order to stop triggers, disable the display alerts at Staring of the procedure:

Application.DisplayAlerts = False

You have to set enable display alerts before ending of the procedure:

Application.DisplayAlerts = True

Please find the below different types of error handling techniques.
1. On Error Resume Next
2. On Error Goto Err_Lbl
3. On Error Goto 0

You can find a specific file exist or not in the following two ways.
1. Using FileSystemObject:
Here is the example to check file exist or not using ‘FileSystemObject’.

Sub Check_File2()
    Dim FSO
    Dim sFileName As String
    
    sFileName = "C:/Test/Workbook.xls"    
    Set FSO = CreateObject("Scripting.FileSystemObject")
    
    If Not FSO.FileExists(sFileName) Then
        MsgBox "File Does Not Exists."
    Else
        MsgBox "File Exists."
    End If
End Sub

2. Using Dir Function:
Here is the example to check file exist or not using ‘Dir’ function.

Sub Check_File1()
Dim sFileName As String
sFileName = "C:/Test/Workbook.xls"
If Dir(sFileName) <> "" Then
    MsgBox "File Exists."
Else
    MsgBox "File Does Not Exists."
End If
End Sub

For more information please find the following link.

Read More …

You can save the workbook using following example. In the below example we are adding new workbook and then assigned it to object named Wkb. Finally we are saving workbook with using Save method of workbook object.

Sub Save_Workbook()

    Dim Wkb As Workbook
    Set Wkb = Workbooks.Add
    Wkb.Save    
End Sub

Please find the following link for more information on saving workbook using Excel VBA.

Read More …

You can change the existing file name of workbook using the following example. In the below example we are adding new workbook and then assigned it to object named Wkb. Finally we are changing workbook name with using SaveAs method of workbook object.

Sub SaveAs_Workbook()
    Dim Wkb As Workbook
    Set Wkb = Workbooks.Add
    ActiveWorkbook.SaveAs Filename:="C:Test.xlsm"    
End Sub

Please find the following link for more information on changeing the existing file name.

Read More …

Please find the following statements to delete a file from the specified location.

Sub sbDelete_File()
    Dim FSO
    Dim sFile As String
        
    sFile = "C:Test.xlsm"
    'Set Object
    Set FSO = CreateObject("Scripting.FileSystemObject")
    
    'Check File Exists or Not
    If FSO.FileExists(sFile) Then
        FSO.DeleteFile sFile, True
        MsgBox "Deleted The File Successfully", vbInformation, "Done!"
    Else
        MsgBox "Specified File Not Found", vbInformation, "Not Found!"
    End If
End Sub

Note: Before deleting file from the specified location, we have to check whether file is exists or not in the specified location. In the above example we are using statement:’ FSO.FileExists(sFile)’ to check for the file.
Please find the following link for more information on deleting a file in a specified location.

Read More …

You can copy a file from one location to another location in the following way.

Sub Copy_File()
    Dim FSO
    Dim sFileName As String
    Dim dFileName As String    
    sFileName = "D:Test.xlsx"  'Source File Location Name
    dFileName = "E:Test.xlsx"  'Destination File Location Name    
    Set FSO = CreateObject("Scripting.FileSystemObject")
    FSO.CopyFile sFileName, sFileName, True    
End Sub

Please find the following link for more information on copying a file from one location to another location.

Read More …

You can move a file from one location to another location in the following way.

Sub Move_File()    
    Dim sFileName As String
    Dim dFileName As String    
    sFileName = "D:Test.xlsx"  'Source File Location Name
    dFileName = "E:Test.xlsx"  'Destination File Location Name    
    Name sFileName As sFileName
End Sub

Please find the following file extension names available in Excel.
Xls,xlsx,xlsm,xla,xlam, etc.,

Here is the example to delete entire row(fifth row) in the active worksheet.

Sub Delete_EntireRow()
    Rows(5).EntireRow.Delete  ‘ 5th row
    ‘or
    Rows(“1:5”).EntireRow.Delete   ‘ first 5 rows
End Sub

Please find the following link for more information on deleting rows in a worksheet

Read More …

Here is the example to delete entire column(s) in the active worksheet.

Sub Delete_EntireRow()
    Columna(“E”).EntireColumn.Delete  ‘ 5th column
    Columna(“A:E”).EntireColumn.Delete  ‘ first 5 columns
End Sub

Please find the following link for more information on deleting columns in a Worksheet.

Read More …

Please find the following example to change the cell(3rd row, 2nd column) color.

Sub Cell_Color()
    'Using Cell Object
    Cells(3, 2).Interior.ColorIndex = 5 ' 5 indicates Blue Color
End Sub

Please find the following link for more information on changing the cell color.

Read More …

Please find the following example to change the range(A1 toB5) color.

Sub Cell_Color()
    'Using Range Object
    Range("A1:B5").Interior.ColorIndex = 5  ' 5 indicates Blue Color
   End Sub

Please find the following link for more information on changing the range color.

Read More …

Please find the following example, it will take you through how to hide or unhide single or multiple row(s).
Hide Row(S): 

Sub Hide_Row()
    Rows("5").EntireRow.Hidden = True  'Hides 5th Row(Single Row)
    Rows("1:5").EntireRow.Hidden = True 'Hides 1 to 5 Rows(Multiple Rows)
End Sub

UnHide_Row(S):

Sub UnHide_Row()
    Rows("5").EntireRow.Hidden = False  'UnHide 5th Row(Single Row)
    Rows("1:5").EntireRow.Hidden = False  'UnHides 1 to 5 Rows(Multiple Rows)
End Sub

Please find the following link for more information on hide or unhide rows.

Read More …

Please find the following example, it will take you through how to hide or unhide single or multiple column(s).
Hide column (S):

Sub Hide_ Column ()
    Rows("5").EntireColumn.Hidden = True  'Hides 5th Row(Single Row)
    Rows("1:5"). EntireColumn.Hidden = True 'Hides 1 to 5 Rows(Multiple Rows)
End Sub

UnHide_ Column (S):

Sub UnHide_ Column ()
    Rows("5"). EntireColumn.Hidden = False  'UnHide 5th Column (Single Column)
    Rows("1:5"). EntireColumn.Hidden = False  'UnHides 1 to 5 Columns(Multiple Columns)
End Sub

Please find the following link for more information on hide or unhide columns.

Read More …

Please find the following examples to hide or unhide worksheets.
Hide Sheet:

Sub Hide_Sheet()
    Sheets("Sheet1").Visible = True
    'or
    Sheets("Sheet1").Visible = 2 'to very hide the worksheet
End Sub

UnHide Sheet:

Sub UnHide_Sheet()
    Sheets("Sheet1").Visible = False
End Sub

Please find the following link for more information on hide

Read More …

There is an option “Allow users to edit ranges” can be used for lock cells such that only specific users can modify them

Here is the example to open Workbook named: “D:Test.xlsx”

Sub Open_Workbook()
    Workbooks.Open "D:Test.xlsx"
End Sub

In the above example we are using ‘open’ method of workbook object to open workbook.
Please find the following link for more information on opening workbook.

Read More …

Here is the example to close opened Workbook. Here Workbook name is: “D:Test.xlsx”

Sub Close_Workbook()
    Dim Wkb As Workbook
    Set Wkb = Workbooks.Open("D:Test.xlsx")
    Wkb.Close savechanges:=True
End Sub

In the above example we are using ‘close’ method of workbook object to close workbook.
Please find the following link for more information on closing workbook.

Read More …

All these three programming languages are developed based on ‘BASIC’ programming language.
VB5: VB6 is a Visual programming language developed to help the users to easily develop the Windows Applications.
VBA: VBA is Visual Basic Programming for developing MS Office Applications. It’s available in all MS Office Tools, such as MS Excel, MS Access, MS PowerPoint and MS Word.
VB Script: Visual Basic Programming for Web Browser (Microsoft Internet Explorer). You can write the VBScript in html pages to program the different objects in Web Pages.

Offset is formula, it is useful to find a specific range from the particular cell or range. It will change the cell or range position based on specified row and column.
Offset Syntax:

OFFSET(reference, rows, cols, [Height], [Width])

In the above example Height and Width parameters are optional.
Please find the following link for more information on Offset.

Read More …

Please find the following examples to protect or UnProtect worksheet.
Protect Sheet:

Sub Protect_Sheet()
    Sheets("Sheet1").Protect "YourPassword", True, True
End Sub

In the above example we are protecting the worksheet by using ‘Protect’ method of Worksheet object.
UnProtect Sheet:

Sub UnProtect_Sheet()
    Sheets("Sheet1").UnProtect "YourPassword”
End Sub

In the above example we are unprotecting the worksheet by using ‘UnProtect’ method of Worksheet object.
Please find the following link for more information on unprotecting worksheet.

Read More …

Here is the example to find last row in a worksheet.

Sub Find_LastRow_In_Worksheet()
    Dim lastRow As Long
    lastRow = Sheets("Sheet1").Cells.SpecialCells(xlLastCell).Row
End Sub

Please find the following link for more information on finding last row in worksheet.

Read More …

Here is the example to find last column in a worksheet.

Sub Find_LastColumn_In_Worksheet()
    Dim lastCol As Long
    lastCol = Sheets("Sheet1").Cells.SpecialCells(xlLastCell).Column
End Sub

Please find the following link for more information on finding last column in worksheet.

Read More …

A set of statements to perform a specific task which is placed in a procedure called Function or Subroutine.
Here is the difference between Function and Subroutine.
Function:
Function will perform calculations based on specified conditions and then return a single value as a result. You can use function anywhere in the procedure and you can use number of times based on the requirement. You can use ‘Exit Function’ or ‘Return’ from immediate exit from a function procedure. Please find the following function statements.

Function Function_Name()
    Statements…
End Function

Subroutine:
Subroutine cannot return any value and it will perform set of actions. We can declare any number of procedures or functions in a module. Procedures or functions can be called in another procedure procedure. You can use ‘Exit Sub’ from immediate exit from a Subroutine or procedure. Please find the following subroutine statements. 

Sub Procedure_Name()
    Statements…
End Sub

Use function procedure to return any value, Otherwise use a Sub procedure.

Using Breakpoints(F9), Step-by-step execution (F8), Debug.Print & Immediate Window and Watch window.

Solution: Any of the three methods can be used:
i) Create a class with the properties you require to return and then return the object of the class from the function.
ii) Using ByRef for the values.
iii) Return an array of the values

VBA is Objected Based Programming Language, not Object oriented language. It will not support all ruled of OOPs concepts, like Inheritance, Multithreading.

Current range will be the range used surrounded by a range. And the range which you have used in your entire worksheet is called Used Range.
For Example, you might have entered some data at Range A1:D10 and then Range A20:D30.
Current Range of A1 (or any range in A1:D10) will be Range A1:D10. Similarlly current range of A20 .. or D30 will be A20:D30.
And the used range of the worksheet will be A1:D30.

Sub CurrentRange()
MsgBox Range("A1").CurrentRegion.Address
End Sub

One worksheet can have one or more current regions, and only one used range. So, used range will be collection of current regions.

Current region will be used to change the formats or calculations to particular block of range (region). And used range will be used to deal with the entire used range in the worksheet.
Current region will connected with at least on data cell (non empty cell). And the used ranges will consider if any range or cell is used once and even if the cell is blank.
You can used the below procedure to find the used range in a worksheet.

Sub UsedRange()
MsgBox ActiveSheet.UsedRange.Address
End Sub

As explained above, once you use any range it will be considered as used cell even after deleting the content in the cell. To get the used range with the data, please refer our examples on finding actual used range in the worksheet.

It is ‘Multiline’ property of textbox cannot be changed at run time.

You can clear the content and formats using ‘Clear’ method of range object in a Range.
Example:

Sub Range_Clear()
    Range("A1:B5").Clear
End Sub

You can clear the Formates using ‘ClearFormates’ method of range object in a Range.
Example:

Sub Range_ClearFormates()
    Range("A1:B5").ClearFormates
End Sub

You can clear the Hyperlinks using ‘ClearHyperlinks method of range object in a Range.
Example:

Sub Range_ClearHyperlinks()
    Range("A1:B5").ClearHyperlinks
End Sub

You can clear the contents using ‘ClearContents method of range object in a Range.
Example:

Sub Range_ClearContents()
    Range("A1:B5").ClearContents
End Sub

Option Explicit makes the declaration of Variables Mandatory. Option Base is used at module level to declare the default lower bound for array subscripts. Default array lower bound value is 0. If the default value is 0, then the Option Base statement is not required. If used, the statement must appear in a module before any procedures. Option Base can appear only once in a module and must precede array declarations that include dimensions.

Example: Option Base 1 will make the array lower bound as 1 instead of 0.

Used at module level to declare the default lower bound for array subscripts.
Syntax: Option Base {1} Or Option Base {1}

More VBA Interview Questions & Answers:

Here is the link for more VBA Interview Questions and Answers. These are explained for examples.
100+ VBA Interview Questions