Excel VBA Interview Questions and Answers

VBA stands for Visual Basic for Applications. It is a Microsoft programming language developed by using Visual Basic. It is used to develop programs within Excel.

In Excel, macro code is a programming code which is written in VBA language. The idea behind using a macro code is to automate an action which you perform manually in Excel, otherwise.

Option Explicit makes the declaration of Variables Mandatory while Option Base used at module level to declare the default lower bound for array subscripts. For eg. Option Base 1 will make the array lower bound as 1 instead of 0.

• The Boolean data type has only two states, True and False. These types of variables are stored as 16-bit (2 Byte) numbers, and are usually used for flags.
• The Byte data type is an 8-bit variable which can store value from 0 to 255.
• The Double data type is a 64-bit floating point number used when high accuracy is needed.
• The Integer data type is a 16-bit number which can range from -32768 to 32767. Integers should be used when you are working with values that can not contain fractional numbers. In case, you’re working over 32767 rows use Long as data type.
• The Long data type is a 32-bit number which can range from -2,147,483,648 to 2,147,483,647.
• The Single data type is a 32-bit number ranging from -3.402823e38 to -1.401298e-45 for negative values and from 1.401298e-45 to 3.402823e38 for positive values. When you need fractional numbers within this range, this is the data type to use.
• The String data type is usually used as a variable-length type of variable. A variable-length string can contain up to approximately 2 billion characters. Each character has a value ranging from 0 to 255 based on the ASCII character set.

By default, Excel supports three Worksheets in a Workbook file, and each Worksheet can support up to 1,048,576 rowsand 16,384 columns of data. Workbooks, however, can have more than three Worksheets if the computer supports enough memory for the additional data.

Each worksheet in Excel can contain more than 1,000,000 rows and 16,000 columns of information, but very rarely is all that room necessary for day to day projects. Fortunately, you can limit the number of columns and rows that your spreadsheet displays. Additionally, you can restrict access to individual areas of a worksheet.

ADO: ADO Stands for ActiveX Data Objects, is Microsoft’s Client-Server technology to access the data between Client and Server.  ADO can’t access the data source directly; it will take help of OLE DB Provider to communicate with the data source. 

ODBC: Open Database Connectivity (ODBC) is a windows technology that lets a database client application connect to a external database.

OLEDB: Low level programming interface designed to access a wide variety of data access Object Linking and Embedding (OLE).

Microsoft Visual basic has its host of problems one such being macros, Macros can be created which can create a havoc for a programmer with good intentions. Also the security issue rests in the hands of the user and not the programmer as such. A user can manipulate the VBA document.

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

In Excel, Volatile functions are a type of function that will always recalculate. That means whenever Excel needs to calculate any part of the worksheet, those cells containing volatile functions will also calculate.

Microsoft Visual basic has its host of problems one such being macros, Macros can be created which can create a havoc for a programmer with good intentions. Also, the security issue rests in the hands of the user and not the programmer as such. A user can manipulate the VBA document

Format functions can be used to format many of the expressions such as currency, time, date, percentages and numbers. These functions are much simpler to use in VBA. User defined date, numeric and string formats are present in many of the applications.

Workbook, Worksheet modules are Class modules.

COM add-ins are software program`s which are integrated into an application and they add already built in features to an existing application. They have common architecture across multiple applications which help in deploying applications much faster across varied applications.

Data validation is an important concept in VBA. Application procedure and a custom dialog box can be used to correct input errors. You can assign an application procedure to the frame of a dialog box. Error message or custom dialog box can be highlighted with the first field with

Visual basic applications have very rich and flexible applications but there is one limitation when using pointer function. Windows API has limited support for function pointers because it has the ability to use but not the functional support to call back the function. But later versions have support for modules. Function pointers have inbuilt support (DLL’s) for call but not for call back.

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

Dependency trees are excel way of minimizing the calculation by tracking what has changed since last calculation. It allows Excel to recalculate only:

• Formulae/Names that have changed.
• Formulae containing Volatile Functions.
• Formulae dependent on changed or volatile formulae or cells or names.
• Excel determines dependencies by looking at the cells referred to by each formula and by the argument list of each function.
• Dependency trees are immediately updated whenever a formula is entered or changed.
• To force the dependency trees to be rebuilt and all formulae calculation use CTRL+ALT+SHIFT+F9.

By Using Data Validation.

When this property is set to True, dependencies are not loaded at open, the dependency dirty chain is not updated, and every calculation of the workbook is a full calculation rather than a recalculation.

If you have a workbook that has so many complex dependencies that loading the dependencies at workbook open takes a long time or recalculation takes longer than full calculation, you can use this property to force Excel to skip loading the dependencies and always use full calculation. Also if making a change to the workbook takes a long time in manual mode because of the time taken to dirty all the dependencies of the cell being changed, then setting Workbook.ForceFullCalculation to True will eliminate the delay.

• Although this is a workbook property the effect is at Application level rather than workbook level.
• In Excel 2007 setting the property back to False once it has been set to True has no effect on the current Excel session.
• The property is saved and restored with the workbook.
• Once this property has been set to True ‘Calculate’ shows in the status bar and cannot be removed by using calculation keys such as F9.

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.

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.

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.

A computer registry can be used to store configuration settings and application initialization. We can use Getsetting function to read registry settings and save settings function to write registry settings. Application name, section, key, setting, and default are to be specified for registry modifying. It is advisable to know about your computer settings before modifying registry settings.

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

Check the below basic Object model of Excel.

Application –> Workbooks –> Worksheets –> Range / Chart

Here is the following VBA procedure to create table in the required database using VBA.

‘Create table in the required database using VBA

Sub sbCreate_Table()

    Dim DBase As Database

    ‘Open Databse

    Set DBase = OpenDatabase(“C:\Users\PNRao\Documents\MyDatabase.accdb”)

 Create a table with three fields.

    DBase.Execute “CREATE TABLE MyTable ” _

        & “(EName CHAR, ENumber INT, ELocation CHAR);”

  DBase.Close

 End Sub

Here is the following VBA procedure to delete records in the access database table using VBA.

‘Delete records in the access database table using VBA

Sub sbDelete_Records_Table()

    Dim DBase As Database

    Dim strSQL As String

    ‘Open Databse

    Set DBase = OpenDatabase(“C:\Users\PNRao\Documents\MyDatabase.accdb”)

    strSQL = “Delete from MyTable where ENumber=12345;”

    ‘ Deleting records the table

    DoCmd.RunSQL strSQL

    DBase.Close

End Sub

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

To register component use

run command prompt type:

c: regsvr32 nameofcomp

For Activex DLL use:

RevSvr32 nameOfDll

For Activex Exe use:

NameOfExe /regServer

Here is the following VBA procedure to delete table from a specific database using VBA.

‘Delete table from a specific database using VBA

Sub sbDelete_Table()

    Dim DBase As Database

    ‘Open Databse

    Set DBase = OpenDatabase(“C:\Users\PNRao\Documents\MyDatabase.accdb”)

    ‘ Create a table with three fields.

    DBase.Execute “DROP TABLE MyTable;”

    DBase.Close

End Sub

Here is the following VBA macro to create a new Word document.

‘Create New Word Document

 Sub Create_Documnent()

    Documents.Add

End Sub