Most Important VBA Interview Questions and Answers (Q&A)

1. What does VBA stand for?

VBA stands for Visual Basic for Applications, which is a programming language that helps you to automate almost every activity in Excel.

2. What Kind of Language Is VBA?

VBA is an object-based language (often called object-oriented) where you first define an object and then define the activity which you want to perform. For example, if you want to select a range, you need to define the range first and then the method to select it.

3. What is an Object in VBA?

VBA is an object-oriented language. For example, when you refer to a worksheet, you are referring to an object, and only then can you use its properties and methods. You can learn more about these objects here.

4. What is the Object Hierarchy in Excel?

In Excel, all the objects are structured in a hierarchy. Following is the complete picture that can help you to understand it.

Each of these objects further comes with properties and methods that you can use to perform activities.

5. How to Activate the Developer Tab?

Before you access the Visual Basic Editor and other options, you need to activate the developer tab using the following steps.

1. File Tab → Options → Customize Ribbon.
2. Tick the check box for Developer under Main Tabs.
3. Click OK.

On a Mac, go to Excel → Preferences (Settings in newer versions) → Ribbon & Toolbar, and tick Developer in the list of main tabs.

6. How to Use the Visual Basic Editor?

Once you activate the Developer tab, you can access the Visual Basic Editor from there. You can also open it with the keyboard shortcut Alt + F11.

When you click the “Visual Basic” button, you will get a Visual Basic window that opens separately.

Here’s a complete guide that can help you to understand how to use the Visual Basic Editor to write VBA codes.

7. Where Can You Write a Macro (VBA Code)?

When you open the Visual Basic Editor, you have a code window there, where you can write code. For a normal macro, first insert a module (Insert → Module) and then write the code in its code window.

8. What are the Ways to Write a Macro (VBA Code) in Excel?

One of the best ways to write a macro is to write it straight in the code window.

The other way is to record a macro with the macro recorder. Excel writes the code for the steps you perform, and you can then open it in the code window and edit it (see question 11).

9. How to Add a Comment in a VBA Code?

To add a comment, you need to start the comment line with an apostrophe (‘) and then write the comment. Once you are done with it, press Enter to move to the next line (learn more about adding comments from here).

10. How to Run a Macro?

You can run a macro from the list of macros. On the Developer tab, there’s a button called Macros (shortcut Alt + F8), and when you click on this button, it gives you a dialog box with a list of macros that you have in all the open workbooks.

Apart from this, there are multiple ways that you can use to run a macro, like pressing F5 in the Visual Basic Editor. Make sure to check out this tutorial to know more.

11. How to Record a Macro in Excel?

In Excel, there is a macro recorder that can help you to record almost all the activities that you perform and generate the code in the background. If you see, on the Developer tab, there’s a button with the name “Record Macro”.

When you click on this, it shows a dialog box to enter the details of the macro that you are going to record, and once you do that, click OK to start recording. When you are done, click “Stop Recording” on the same tab.

 Here is a complete guide to learn how to record a macro.

12. How to Save a Macro in Excel?

When you write a macro, it automatically gets saved in the workbook where you have written it, and there is no need to save it separately. But you need to save the workbook in the macro-enabled format (.xlsm). The binary format (.xlsb) can hold macros too, but the normal .xlsx format can’t.

And when you try to save a workbook that has a macro in some other format (like .xlsx), Excel alerts you that the VBA project can’t be saved in that format.

13. What is a Module?

VBA module is a container with its own code window where you can write a macro. When you export a module, it is saved as a “.bas” file. You can insert, delete, and even import and export a module into the workbook.

14. What is a Procedure in VBA?

When you write a macro in VBA, that is called a procedure. A procedure can be a single line of code or can be multiple lines of code.

In the above example, we have a procedure with the name “vba_border” that has a single line but has split that single line into multiple lines.

15. What are the Types of Procedures?

There are two main types of procedures that you can use in VBA: the first is the “Sub” procedure and the second is “Function”. (There’s also a third type, the “Property” procedure, which you mostly use in class modules.)

If you want to write a “Sub” procedure, you need to start the code with the keyword “Sub”, and for the function procedure, you need to start it with the keyword “Function”. The difference is that a function can return a value, so you can use it in other code or even in a worksheet cell, while a Sub just performs the steps.

16. What is a Variable?

As the name says, a variable is something that is not fixed. In VBA, you can use a variable to store a value, and you can change that value anytime while executing the code.

17. How to Declare a Variable in VBA?

To use a variable, you need to declare it first by using the “Dim” keyword. In the following code example, you can see that we have used the “Dim”, the name of the variable, and then the data type.

In the above example, we have used a Dim statement to declare the “startDate” variable and specified Date as the data type. And after that, we have specified a date value for the variable. You can learn more about variables from here.

18. What are the VBA Data Types?

While declaring a variable (or even a constant), you should specify the data type. Declaring the data type helps VBA to understand which type of value you want to store in the variable. Let’s say you want to store a date in the variable, as we have done in the previous example; then you need to specify the data type as Date. If you don’t specify any data type, VBA uses Variant, which can hold any type of value but uses more memory.

Following are the data types you will use most of the time:

• Integer: whole numbers from -32,768 to 32,767.
• Long: bigger whole numbers (up to about 2.1 billion). It’s the better choice for row numbers.
• Double: numbers with decimals.
• Currency: money values with up to four decimals.
• String: text.
• Date: dates and times.
• Boolean: True or False.
• Object: an object like a range, worksheet or workbook (you assign it with Set).
• Variant: any type of value.

19. What is Option Explicit?

Option Explicit is a statement that you can use at the start of a module, and once you use this statement, VBA notifies you if you skip declaring a variable.

The above screenshot is the best example where we have used the variable “a” without actually declaring it, and while executing the code, there is a compile error message that says “Variable not defined”. As you can see, at the top, we have used the Option Explicit statement.

To add it to every new module automatically, go to Tools → Options in the Visual Basic Editor and tick “Require Variable Declaration”.

20. What is a Constant?

Like a variable, a constant also stores a value. But, in the case of a constant, you can’t change the value you have stored in it while the code runs. To declare a constant, you need to write it the same way that you declare a variable, but instead of “Dim”, you need to use the keyword “Const” and assign the value in the same line (see the following example and here’s the link to learn more).

21. How to Refer to a Workbook in a VBA Code?

To refer to a workbook, you need to use the workbook object and then specify the name of the workbook to which you want to refer.

In the above example, we have used the workbook object, and then we have referred to “book1” using its file extension. Following are some of the examples that you can learn to work with workbooks in Excel VBA.

Save a Workbook | Delete a Workbook | Refer to ThisWorkbook | Rename a Workbook | Activate a Workbook | Combine Workbooks | Protect a Workbook (Unprotect) | Check IF a Workbook is Open | Open a Workbook | Check IF an Excel Workbook Exists in a Folder| Create a New Workbook (Excel File) | Close a Workbook

22. How to Refer to a Worksheet in a VBA Code?

To refer to a worksheet, you need to use the worksheet object and then specify the name of the worksheet that you want to refer to. In the following example, we have used the worksheet object and then specified “Sheet1”.

As you can see, we have used “Sheets” and “Worksheets” to refer to the same worksheet. But you need to understand the difference between sheets and worksheets (Sheets also includes chart sheets, while Worksheets has only worksheets), and here’s the complete guide for this.

Count Sheets | Activate a Sheet | Loop through all the sheets

• Check IF a Sheet Exists
• Clear an Entire Sheet
• Protect and Unprotect a Sheet
• Delete a sheet
• HIDE/UNHIDE a Sheet
• How to RENAME a Sheet in Excel using VBA
• How to COPY a Sheet in Excel using VBA
• How to ACTIVATE a Sheet using VBA

23. How to Select a Range of Cells using a Macro?

To select a range or a cell, you need to use the range object to specify the range first and then use the Select method, and when you run that code, Excel will select the range you have specified.

As you can see in the above example, we have specified the range A1 and then used the Select method. Following are some other ways to refer to and select cells and ranges using VBA.

Sub selectRanges()
    Range("A1:C10").Select           ' a range of cells
    Cells(2, 3).Select                ' row 2, column 3 (C2)
    Range("A1").CurrentRegion.Select  ' the whole data block around A1
    Range("A:A").Select               ' an entire column
End Sub

Keep in mind that you don’t need to select a cell to work with it. Writing Range(“A1”).Value = 10 works without selecting, and it makes your code faster.

24. How to Get Value from a Cell using a Macro?

To get a value from a cell, you need to use the Value property and then assign that value to a different cell (or a variable) to get it. In the following example, we have used the range object to specify A1, and then we have used the Value property.

And after that, we have assigned it to cell A2, and when you run this code, you get the value from cell A1 into cell A2.

25. How to Enter a Value in a Cell using VBA?

To enter a value in a cell you need to use the range object first, and then specify the value that you want to enter. In the following example, we have a code to enter a value in cell A1. First, we used the range object and then used an equal sign to specify the value “Yes” to enter.

As you can see, we have enclosed the value in double quotation marks as it was a text value.

26. How to Show a Message to a User?

In VBA, “MsgBox” is a function that you can use to show a message box to the user and get a response with the buttons that you have on the message box. See the following example.

MsgBox(prompt, [buttons], [title], [helpfile, context])

You can learn more about the message box from this guide.

27. How to Get a Response from a User?

To get a response from the user, you can use the InputBox function, which shows a box where the user can type a response and then click OK to submit it. The function returns what the user typed as text (or an empty string if the user clicks Cancel).

Sub getResponse()
    Dim userName As String
    userName = InputBox("What is your name?", "Your Name")
    If userName <> "" Then MsgBox "Hello " & userName
End Sub

If you want the user to enter a number or select a range, use Application.InputBox instead, as it lets you set the type of input with its Type argument (1 for a number, 8 for a range).

28. What is a Loop in VBA?

A loop is a set of code that repeats itself multiple times. In VBA, a loop is a way of telling VBA to repeat the lines of code that are within the loop. There are different methods of writing a loop in VBA.

• For Next
• For Each Next
• Do While Loop
• Do Until Loop

Here’s a simple For Next loop that enters the numbers 1 to 10 in cells A1 to A10:

Sub simpleLoop()
    Dim i As Long
    For i = 1 To 10
        Cells(i, 1).Value = i
    Next i
End Sub

29. How to Test Conditions in VBA?

In VBA, to test conditions, there are two major methods you can use: the IF statement and Select Case. Both methods are a little different from each other. With the IF statement, you can test one condition at a time (unless you use ElseIf or a nested IF), and with Select Case, you can test multiple conditions against one value.

More: Exit IF | IF And | IF Not | IF OR

30. What is an Array in VBA?

An array in VBA is like a mini database where you store and organize data.

 To use an array, first you need to declare it.

An array can be static, where you set its size while declaring it (Dim myArray(1 To 5) As String), or dynamic, where you declare it without a size and set the size later with ReDim. Use ReDim Preserve if you want to keep the values that are already in the array.

31. Do We Have Functions in VBA?

Yes, we have functions in VBA. Using these functions is the same as using the functions in the worksheet. There are arguments for which we need to define the values to get the result (check out this list of functions).

32. Do We Need to Deal with Errors in VBA?

Just like in all the coding languages, you do need to deal with errors in VBA, and the best way to deal with these errors is to understand how to debug your codes and have clarity on all the possible errors that a user can face in the future. There are four types of errors that you could face:

1. Syntax Errors
2. Compile Errors
3. Runtime Errors
4. Logical Errors

To handle runtime errors in your code, you can use the On Error statement: On Error Resume Next skips the line with the error, and On Error GoTo sends the code to a label where you handle the error.

Following is the list of errors that you need to deal with in different situations.

Subscript Out of Range (Error 9) | Runtime (Error 1004) | Object Required (Error 424) | Out of Memory (Error 7) | Object Doesn’t Support this Property or Method (Error 438) | Invalid Procedure Call Or Argument (Error 5) | Overflow (Error 6) | Automation error (Error 440) | VBA Error 400 | Type Mismatch Error (Error 13)

33. How to Debug a VBA Code?

There are two different ways to debug the code in VBA. The first is to use the Compile VBAProject option (Debug → Compile VBAProject). When you use this option, it scans each line of the code and alerts you back if there is an error in the code.

The second way is to run each line of code one by one to check if there is an error in the code. For this, you can use the Step Into option, or you can also use the shortcut key F8.

Apart from these two, you can add a breakpoint (F9) on a line to pause the code there, use Debug.Print to see values in the Immediate Window (Ctrl + G), and use the Locals and Watch windows to check the values of your variables while the code runs.

34. How to Use a Worksheet Function in VBA?

In VBA, there is a property called “WorksheetFunction” that you can use to get access to most of the worksheet functions.

And once you choose a function to use, you can specify values for the arguments of that function. Functions that VBA already has on its own (like Date or Left) are not in this list, so you use the VBA version for those.

35. What is a User Defined Function (UDF)?

UDF is a custom function that you can create in Excel by using VBA’s function procedure. In the following example, we have written code to create a function that can return the day name from a date.

If you see, in the above example, we have defined an argument (InputDate) for the function and then used the value from that argument to calculate the result of the function. Check out this link to learn more about it.

36. How to Use a Personal Macro Workbook?

You can save your macros in the personal macro workbook to access them from all the workbooks. When you open Excel, the personal macro workbook (PERSONAL.XLSB) opens every time in the background, and it gives you access to the codes that you have saved in it.

When you record a macro there is an option that you can choose to store that code in the personal macro workbook. That is one of the easiest ways to create a personal macro workbook if it’s not there already. You can learn more about it here.

37. What is the Use of the “With” Statement?

By using the With statement, you can refer to an object once, and then use it to access its multiple properties and use multiple methods.

As you can see in the above example, where we have referred to the range A1’s font property using the “With” statement and then referred to three of its properties. The best part about using the With statement is that you do not need to refer to the main object again and again. You can learn more about “With” from here.

38. What is the use of the GoTo Statement in VBA?

In VBA, you can use the GoTo statement to jump from one line of code to another. To use this statement, you need to create a label somewhere in the code and then use the GoTo statement to tell VBA to jump to that label. Refer to the following example and if you want to learn more check out this tutorial.

39. What are Some Useful Tasks that You Can Do using VBA?

Following are some of the major tasks that you can perform using VBA code.

Add Borders | Font | Add Line Break | Add a Comment | Add a New Line | Merge Cells | Wrap Text | Wait and Sleep Commands to Pause and Delay | Status Bar | Screen Updating | Generate Random Numbers | Named Range | Immediate Window | Create and Write to a Text File | Concatenate | Clear Contents | AutoFit

40. What is the Difference Between ThisWorkbook and ActiveWorkbook?

ThisWorkbook always refers to the workbook that has the code in it. ActiveWorkbook refers to the workbook that is active (on top) at the moment, which can be a different one. If your code should always work on its own file, use ThisWorkbook.

41. What are Events in VBA?

An event is an action that Excel can respond to, like opening a workbook, changing a cell, or selecting a sheet. You can write an event procedure that runs on its own when that action happens. These procedures go into the code window of the object, like ThisWorkbook or a sheet, not into a normal module.

Private Sub Workbook_Open()
    MsgBox "Welcome! Today is " & Format(Date, "dddd")
End Sub

This code goes in the ThisWorkbook code window, and it shows a message every time you open the workbook.

42. How to Make a VBA Code Run Faster?

Following are the most common ways to speed up a macro:

• Turn off screen updating at the start of the code and turn it back on at the end.
• Set the calculation to manual while the code runs and back to automatic at the end.
• Don’t select or activate cells and sheets; work with them directly.
• Read a large range into an array, work on the array, and write it back in one go.
• Use the With statement to refer to an object only once.

Sub fasterCode()
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    ' your code goes here
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
End Sub

43. What is the Difference Between ByVal and ByRef?

These two words tell VBA how to pass an argument to a procedure. ByRef (the default) passes the variable itself, so if the procedure changes it, the change shows up in the code that called it. ByVal passes a copy of the value, so the original variable stays as it is.

44. How to Protect a VBA Code with a Password?

In the Visual Basic Editor, go to Tools → VBAProject Properties → Protection tab, tick “Lock project for viewing”, enter a password, and click OK. Then save and close the workbook. The next time you open it, the code can’t be viewed without the password.

45. Why are Macros Blocked When I Open a File from the Internet?

In Microsoft 365 on Windows, Excel blocks macros by default in files that come from the internet (like email attachments and downloads), and it shows a red “Security Risk” bar without an Enable Content button. If you trust the file, close it, right-click it, choose Properties, tick “Unblock” on the General tab, click OK, and open it again. You can also save trusted files in a Trusted Location.

46. Does VBA Work in Excel for the Web and on Mac?

VBA works in the desktop versions of Excel on Windows and Mac (some Windows-only features, like ActiveX controls, don’t work on Mac). It doesn’t run in Excel for the web or in the mobile apps. For automation in Excel for the web, Microsoft offers Office Scripts, which use a JavaScript-based language and can also run from Power Automate.