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

Last Updated: March 24, 2024
puneet-gogia-excel-champs

- Written by Puneet

1. What does VBA stand for?

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

2. What Kind of Language VBA is?

VBA is an object-oriented language 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.

define-the-range

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 you can use the 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.

object-hierarchy-in-excel

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.

activate-the-developer-tab
  1. File Tab → Options → Customize Ribbon.
  2. Tick the check box for the developer.
  3. Click Ok.

6. How to use the Visual Basic Editor?

Once you activate the developer tab, you can access the visual basic editor from there.

access-the-visual-basic-editor

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

visual-basic-window

Here’s a complete guide that can help you to understand to use the visual basic editor to write VBA codes.

7. Where you can Write a Macro (VBA Code)?

When you open the visual basic editor, you have a code window there, where you can write code.

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.

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 enters to move to the next line (learn more about adding comments from here).

add-a-comment

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

run-a-macro

Apart from this, there are multiple ways that you can use to run a macro, 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 all code at the backend. If you see, on the developer tab there’s a button with the name “Record Macro”.

record-a-macro

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

click-ok-to-start-recording

 Here is a complete guide to learning 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, there is no need to save it separately. But you need to save the workbook in macro format (.xlsm) format.

save-a-macro-in-excel

And when you try to save a workbook in some other format that is having a macro, Excel alerts you.

13. What is a Module?

VBA module is a “.bcf” extension file that has its code window where you can write a macro. You can insert, delete, and even import and export a module into the workbook.

vba-module

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.

procedure-in-vba

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 types of procedures that you can use in VBA, the first is the “Sub” procedure and the second is “Function”.

types-of-procedures-in-vba

If you want to write a “Sub” procedure you need to start the code with the keyword “Sub” and for the function procedure

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

use-a-variable

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

18. What are the VBA Data Types?

While declaring a variable (or even a constant) you need to specify the data type. Declaring the data type helps VBA to understand which type of value you want to store in the variable. Let us say if you want to store a date in the variable as we have done in the previous example, you need to specify the data type as the date.

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, we can notify you if you skip declaring a variable.

option-explicit

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 “variable not defined”. As you can see at the top we have used the option explicit statement.

20. What is a Constant?

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

constant-in-vba

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.

refer-to-a-workbook

In the above example, we must use the workbook object, and then we have referred to the book1 using the 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 FolderCreate 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 you need to specify the name of the worksheet that you want to refer to. In the following example, we have used the worksheet object and then specify the “Sheet1”.

refer-to-a-worksheet

As you can see we have used “Sheets” and “Worksheets” to refer to the same worksheet, hey you need to understand the difference between sheets and worksheets, and here’s the complete guide for this.

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

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 you the select method, and when you run that code Excel will select the range you have specified.

select-a-range-of-cells-using-macro

As you can see in the above example, we have specified the range A1 and the use of the select method. Following are some of the important tasks that you can do with cells and a range using VBA.

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

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

get-value-from-a-cell-using-macro

And after that, we have assigned it to cell A2 and when you 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 use enter. In the following example, we have a code to enter a value in cell A1. First, we used the range object and then use an equal sign to specify the value “Yes” to enter.

enter-a-value-in-a-cell

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])

vba-msgbox-function

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 input box that allows the user to enter a response in the input bar and then press okay to submit it.

28. What is a loop in VBA?

A loop is a set of code, that repeats itself multiple times. In VBA, their loop is a way of defining a line of code that is within a loop to repeat it. There are different methods of writing a loop in VBA.

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

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 (until you use a nested IF) and with the select case, you can test multiple conditions.

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.

an-array-in-vba

 To use an array first you need to declare it.

declare-an-array

31. Do we have functions in VBA?

Yes, we have functions in VBA. Using these functions are the same as the functions we use 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 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 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 VBA project option. When you use this option, it scans each line of the code and alerts you back if there is an error in the code.

debug-a-vba-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 the option, or you can also use the shortcut key F8.

step-into-the-option

34. How to use a worksheet function in VBA?

In VBA, there is a property called “Worksheet Function” that you can use to get access to all the worksheet functions.

use-of-worksheet-function-in-vba

And once we choose a function to use can you specify values for the arguments for that function?

specify-values-for-the-arguments

35. What is a User Define 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.

user-define-function

If you see, in the above example, we have defined an argument (InputDate) for the function and then used 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 personal macro workbook opens every time and it gives you access to the courts that you have saved in it.

personal-macro-workbook

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 statement, you can refer to an object once, and then you use it to access its multiple properties and use multiple methods.

use-of-the-with-statement

As you can see in the above example, where we have referred to the range A1’s font property using the “With” statement further he referred to three properties. the best part about using the with statement is 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 go-to statement to jump from one code to another. To use this statement, you need to create a label somewhere in the code and then use the go-to 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.

goto-statement-in-vba

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