VBA Interview Questions & Answers

Let us see the top VBA Interview Questions & Answers. Which helps us to go through VBA interview questions quickly before going to an interview.

What is VBA?

VBA Stands for Visual Basic for Applications.
VBA is an event driven programming language. It is introduced by Microsoft. We can write codes and develop MS Office applications using VBA in MS Excel, MS Word, MS Access and MS PowerPoint.

When we use VBA?

We use Excel VBA to automate repetitive tasks, to build very powerful tools, to create reports, add-ins, and many more. We can interact with the user by creating Userforms, or custom dialog boxes.
We have great feature in VBA is Record macro. It helps to create VBA macro code for which we perform actions in Excel. Once we create a macro, we can edit the code and we can customize as required.

How to activate the Developer tab in Excel menu?

Here are the instructions to activate the developer tab in Excel menu.

Go to File Tab

Select Options

Select Customize Ribbon from the left side options.

Check the check box for the developer.

Click OK.

How to Record a Macro in Excel?

Here are the step-by-step instructions to record a macro in Excel.

Open Excel.

Click on the Developer Tab from Excel Ribbon.

Click on Record Macro from the Code group. Now you can see the following Record Macro dialogue box on the screen.

Here we can specify Macro name, Shortcut key, macro location and description.

How to use comments in Visual Basic Editor?

Comments are ignored by the interpreter and compiler and does not affect performance while execution. It is good programming practice or habit to begin all procedures or functions with a comment in VBE to describe what it does. 

You can add comments to your code by preceding the text with the REM keyword. However, the Single Quote (‘) symbol and the Comment or Uncomment buttons are easier to use. It occupies less space in memory. Commented line always highlight in green color in the code window.

How to debug VBA code in Visual Basic Editor?

We can debug code in different ways.
Execution: Place the Cursor in a procedure and Press F5 to Execute.
Step by Step Execution: Place the cursor in a procedure and Press F8 to execute the program step by step.
Toggle Breakpoints: Place the cursor at the required statement and press F9 to toggle the breakpoint.
Printing in Immediate Window: Use ‘Debug.Print’ to print in the Immediate Window.
Executing in Immediate Window: Write your expression in the immediate window preceded by question mark and enter to execute the expression.

What are the scope of variables in VBA?

We usually define variables in three different levels.
Global Level: These variables can be accessed in entire project. Which are defined using Public statement at the top of any module.
Module Level: These variables can be accessed in entire module. Which are defined using DIM statement on the top of a module.
Local Level: These variables which are defined with DIM statement in a procedure or functions

What are the rules to name a variable in VBA?

We must follow the following rules while declaring a variable name.

Variable Name must be less than 255 characters long.

VBA keywords or reserved words cannot use as a Variable Name.

It should not begin with number, always must begin with a letter.

Variables consists of only letters, numbers and underscore characters.

It doesn’t allow space in the variable name.

Variable name doesn’t allow period(.)

We cannot use special characters like @,#,$.%,&,!, etc.

What are the available data types in VBA?

Here are the available VBA data types in Excel VBA. These are divided into two categories. The first one is numeric data type and other one is non-numeric data type.

Numeric Data Types are Byte, Integer, Long, Single, Double, Currency and Decimal.

Non-Numeric Data Types are String(Fixed Length), String(Variable Length), Date, Boolean, Object, Variant(Numbers) and Variant(Characters).

What is a Variant Data Type in VBA?

The default data type is Variant data type and it can hold any type of data. This will allocate maximum memory to hold any type of data. We use Variant data-type when we don’t know the type of the data or to accept the multiple data types in a single variable.

What are the available constants in VBA?

Constants has a meaningful name and assigned or set a value to it. Once constant value is declared, we can’t change or assign to a new value throughout the script execution. We use Const keyword to declare a constant value. We can declare a constant variable any where in the module. It can be declared at Module level or procedure level or class module. We can also use private or public while declaring constant variable.

Ex: Const iCnt As Integer = 111

What are the objects, methods, events and properties in VBA?

Objects: An Object is nothing but a class. And object can have Child Objects, Methods, Properties and Events.

Methods: Methods are procedures or actions available for a particular object. Example, Close, Open, Save methods of Workbook Object.

Events: Events are triggers available for an Objects. Example, Workbook Open Event, Workbook Before Close Event.

Properties: Properties are characteristics of an Object. Example: Path, Name of Workbook Object.

What is an Option Explicit in VBA?

An Option Explicit force the variables to be declared before using the variable. This is one of the best practices for VBA developers to avoid the type errors and build the error free applications.

What is an Option Base in VBA?

An Option Explicit statement is used at the module level to declare the default lower bound for an array subscripts. It contains the value either 0 or 1. The default option base value is 0.

What is the difference between Procedure and Function?

Procedures will not return a value and functions will return values.

What are the different types of modules in VBA?

Code Module: Default module is code module. We write all procedures and functions in the code module.
UserForms: UserForms helps to develop Graphical User Interface(GUI) applications.
Class Modules: Class module allows to create new objects and define methods, properties and events. Also, it allows to enhance the existing objects as well.

What is the difference between ByVal and ByRef in VBA?

Arguments can be passed in two ways in VBA procedures or 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.

What are the commonly used functions in VBA?

Commonly used functions in VBA are MsgBox, InputBox, Left, Right, Trim, Len, Mid, InStr, Split, IsNumeric, Round, Now, Format etc.

What are the built-in class modules in VBA?

The built-in class modules are Workbook, Worksheet are Class modules.

What are the best practices to fasten the VBA macro?

There are several best practices to fasten VBA macro by using following tips.

Declare the variables to respective data type and avoid using ‘Variant’ Data Type.

Turn off automatic calculations

Turn off screen updating

Use with statement

Use vbNullString instead of “”

Disable events

Release memory objects at the end of the procedure.

Watch at You tube

You can also watch VBA Interview Questions and Answers at YouTube.

References:

100+ VBA Interview Questions and Answers with Examples
Basic VBA Interview Questions and Answers
Advanced VBA Interview Questions and Answers
MS Excel VBA Interview Questions and Answers with Examples
MS Access VBA Interview Questions and Answers with Examples
MS PowerPoint VBA Interview Questions and Answers with Examples
MS Word VBA Interview Questions and Answers with Examples
MS Outlook VBA Interview Questions and Answers with Examples

Instructions to Run VBA Macro Code or Procedure:

You can refer the following link for the step by step instructions.

Instructions to run VBA Macro Code

Other Useful Resources:

Click on the following links of the useful resources. These helps to learn and gain more knowledge.

VBA Tutorial VBA Functions List VBA Arrays VBA Text Files VBA Tables

VBA Editor Keyboard Shortcut Keys List VBA Interview Questions & Answers Blog