VBA Interview Questions and Answers with Examples, macro codes – Download Free PDF File. Top Most 100+ commonly asked Basic and Advanced VBA Interview Questions and Answers Covered for Freshers and Experienced VBA Developers.Important ,Latest, Logical, Technical, Programming, Simple and Tough questions and answers from MS Excel VBA, Access VBA, PowerPoint VBA, MS Outlook and Word VBA Questions and Answers with tests and Quizzes.Top Most VBA Interview Questions & Answers with Examples

100+ VBA Interview Questions and Answers Explained with proper examples by topic. FAQs are useful for all Excel VBA users to refer Excel VBA quickly. Please find the below are more than 100 VBA FAQs:Frequently Asked Interview Questions and Answers explained with examples which we have experienced.

Basic VBA Interview Questions and Answers

Here are the most commonly asked Basic VBA Questions and Answers covered from Basics of VBA Programming.

  • VBA stands for Visual Basic for Applications.
  • VBA is Programming language available in MS Office Tools.
Data types helps to declare Variables with specific data, this helps to VBA to know the type of the data and assign the memory based on the DataType of the Variable.
  1. Boolean
  2. Byte
  3. Currency
  4. Date
  5. Double
  6. Integer
  7. Long
  8. LongLong
  9. LongPtr
  10. Object
  11. Single
  12. String
  13. Variant
Variant data type is default data type and it can hold any type of data. This will allocate maximum memory to hold the Varian Type. We use Variant data-type when we dot know the type of the data or to accept the multiple data types in a single variable.
We can define variable in different levels:

  • Local Level: Variables which are defined with DIM statement in a procedure or functions
  • Module Level: Which are defined with DIM statement on top of a module, can be accessed in entire module
  • Global Level: Which are defined Public statement at top of any module, can be accessed in entire project.

Read More Basic VBA Interview Questions and Answers

More Basic VBA Interview Questions

Advanced VBA Interview Questions and Answers

Here are the top most Advanced VBA Questions and Answers covered from Advanced concepts of VBA Programming.

Option Explicit force the variables to be declared befre using the variable. This is one of of the best practices for VBA developers to avoid the type errors and build the error free applications.
Code Module: Default Modules which we use to write all procedures and functions
UserForms: UserForms helps to develop GUI (Graphical User Interface) applications.
Class Modules: Class module allows to create new objecs and define methods, properties and events. Also, it will allow to enhance the existing objects.
Procedures or Subroutines will not return a value; functions will return values.
Arguments can be passed in two ways in VBA Subroutines and Functions:
ByVal: When an argument is passed By Value, the value assigned to the argument is passed to the procedure. And any changes that are made to the argument inside a procedure, it will be lost when the procedure is ends.

ByRef: When an argument is passed By Ref, the actual address assigned to the argument is passed to the procedure. And any changes that are made to the argument inside the procedure will be passed when the procedure ends.
And By Ref is default in VBA.

Workbook, Worksheet modules are Class modules.

Read More Advanced VBA Interview Questions and Answers

More Advanced VBA Interview Questions

Top Most Excel VBA Interview Questions and Answers

We have covered most frequently asked Excel VBA Interview Questions and Answers, divided into different sections. Take your own time to understand the questions and answers. Please ask us if you have any further questions.
Please let us know your interview experience and share your experience. Please let us know if you want to add any questions, which we have missed here.

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 Editor.
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