Excel VBA Macros Interview Questions & Answers - I

Excel VBA Macros Interview Questions & Answers

1. What is the file extension of excel workbooks with macros in Excel 2003, 2007, 2010 ?

Ans : Excel 2003 = xls Excel 2007 = xlsm Excel 2010 = xlsm

2. Why we are using macros ?

Ans : If you perform a task repeatedly in Microsoft Excel, you can automate the task with a macro. Moreover, macro can perform lots of tasks which we can't do manually ( like looping statement)

3. What is the shortcut to goto VBA screen ?

Ans: ALT + F11

4. Can we record a looping statement ? Give some examples of looping statements ?

Ans: No. For loop, Do While Loop, Do Until Loop

5. How to add a module to a VBA project?

Ans: Right Click on VBA project in VBA screen > Insert > Click on Module

6. a,What is the meaning of "Option Explicit"? b, Where it should be used ?

Ans : a, Option Explicit makes the declaration of Variables Mandatory (it forces us to declare all variables used in our codes) Line explicit function makes the compiler to identify all the variables which are not specified by the dim statement. This command significantly reduces the problem of type errors. This is used extensively because VBA deals with information rich applications in which type errors are common.

b. It can be used inside a module, before starting any sub procedures

7. How we will hide a sheet permanently ? (How to hide a worksheet so that a normal user cannot unhide it)?

Ans :
Use Sheet's visible property and set it to xlSheetVeryHidden. We can do it in 2 ways.
1. VBA screen > worksheet properties > visible > change to xlSheetVeryHidden
or 2. Use code sheet1.Visible = xlSheetVeryHidden

8. a, If I require a macro to run everytime when a workbook opens, can this be recorded ?
Ans: No
b, So, where should I add codes for this ?
Ans : goto VBA screen > double click on THISWORKBOOK > then select > Workbook_Open

9. What is the difference between thisworkbook and activeworkbook?
Ans: ThisWorkbook refers to the workbook where code is being written while ActiveWorkbook refers to the workbook which is in active state with active window. In case of only one workbook open, ActiveWorkbook is same as ThisWorkbook

10. Now I have an object variable named "Wkbook", tell the code to assign the activeworkbook to this object ?
Ans : SET Wkbook = ACTIVEWORKBOOK

Click here for more in Excel VBA  Macros Interview Questions

Important concepts in VBA Macros

Essential concepts to remember

In this section, I note some additional concepts that are essential for would-be VBA gurus.

• Objects have unique properties and methods.

Each object has its own set of properties and methods. Some objects, however, share

some properties (for example,

Name) and some methods (such as Delete).

• You can manipulate objects without selecting them.

This might be contrary to how you normally think about manipulating objects in Excel.

The fact is that it’s usually more efficient to perform actions on objects without selecting

them first. When you record a macro, Excel generally selects the object first. This is

not necessary and may actually make your macro run more slowly.

• It’s important that you understand the concept of collections.

Most of the time, you refer to an object indirectly by referring to the collection that it’s

in. For example, to access a

Workbook object named Myfile, reference the

Workbooks

collection as follows:Workbooks(“Myfile.xlsx”)

This reference returns an object, which is the workbook with which you are concerned.

• Properties can return a reference to another object.

For example, in the following statement, the

Font property returns a Font object contained

in a

Range object. Bold is a property of the Font object, not the Range object.

Range(“A1”).Font.Bold = True

• There can be many different ways to refer to the same object.

Assume that you have a workbook named

Sales, and it’s the only workbook open.

Then assume that this workbook has one worksheet, named

Summary. You can refer to

the sheet in any of the following ways:

Workbooks(“Sales.xlsx”).Worksheets(“Summary”)

Workbooks(1).Worksheets(1)

Workbooks(1).Sheets(1)

Application.ActiveWorkbook.ActiveSheet

ActiveWorkbook.ActiveSheet

ActiveSheet

The method that you use is usually determined by how much you know about the workspace.

For example, if more than one workbook is open, the second and third methods

are not reliable. If you want to work with the active sheet (whatever it may be), any of

the last three methods would work. To be absolutely sure that you’re referring to a specific

sheet on a specific workbook, the first method is your best choice.

VBA code to close all Excel workbooks except the active workbook

VBA code to close all Excel workbooks except the active workbook:

If you had worked in Excel with several workbooks at a time, you might have came across a question like this.

How to close all other workbooks except the active workbook in one shot instead of going to each workbook and close ?

Microsoft Excel Does'nt provide a direct way to close several workbooks at a time.

But we can use VBA codes to accomplish this task.

How :

1. Select the workbook you wish to retain
2. Go to VBE
3. Add one Module
4. Copy paste the below sub procedure to a module in your code
5. Run the Macro Whenever you want to close other workbooks
6. Remember to Save the file as MacroEnabled

Sub CloseAllSheetsExActive()
Dim WBs As Workbook
For Each WBs In Application.Workbooks
If Not WBs.Name = ThisWorkbook.Name Then WBs.Close (False)
Next WBs
End Sub

Note : Macro will close all the workbooks ignoring all the changes you have made without asking whether to save it or not. If you use TRUE instead of FALSE after close method, it will save all the changes without asking. If you don't use TRUE or FALSE, a dialog box will show to ask whether you wish to save or close without saving.

How to add formula to a worksheet range using VBA Codes

How to add formula to a worksheet range using VBA


Adding formulas to ranges is common task when you workaround with Excel VBA.

If you are good in entering formulas in Excel, you can become an expert in adding VBA formulas too.

Eg :

We have some values in a range A1:A20 and We have some values in B1:B20 also. We need to get the difference of all values in B column with corresponding Cell in Column A, in the Column C.

In Excel we will enter a formula = B1-A1 in the cell C1 and do a fill down or a copy paste till C20. It's the simplest formula when we work in Excel.

But How to get the same formulas in C1:C20 using VBA codes ?

You can use two ways.

1. Enter Activesheet.Range("C1:C20").Value = "RC[-1] + RC[-2]" in your code.
2. Enter Activesheet.Range("C1:C20").FormulaR1C1= "RC[-1] + RC[-2]" in your code.

Both codes with do the same task. Note, VBA is using R1C1 reference in entering the formulas to Excel. It's better to Get a basic idea about how R1C1 reference style works, before you start working with formulas in VBA.

Solution: If you have any doubt in VBA, first try to record the task which you want to automate and see the codes. Same way, you record the task of entering the above formulas using macro recorder. See the codes.

What is the use of Option Explicit while writing VBA Code/ How to avoid typing mistakes on variable names in VBA Code / How to find misspelled variable in a VBA procedure

What is the use of Option Explicit while writing VBA Code/ How to avoid typing mistakes on variable names in VBA Code / How to find misspelled variable in a VBA procedure ?


We use so many variables while writing VBA codes and we will do so many tasks using these variables, like assigning a value, retrieving the assigned value, etc.

Probably, a variable will be used several times in a sub. If you have made a spelling mistake while typing the variable name, VBA will not detect it automatically. VBA will consider the misspelled word as an object variable.

To avoid this we can use the key word Option Explicit. This should be used out side any procedure and before starting of any sub.

If we have used this, while running any sub under that VBA will detect any undeclared variables in that sub and stop running the sub asking to declare it. Without delcaring the variable, VBA will not allow you to execute the sub.

Note: You don't need to type this keyword always. Goto VBE > Tools > Options > Click on 'Require variable declaration'. VBA will automatically add this keyword to any modules.

How to exit from a sub / How to stop executing a sub while running a macro

Everyone knows we can use the key combination CTRL + BREAK to stop running the execution of macros.

But while coding you will come across the following scenarios.

1. How to stop a macro in between a sub without executing further codes.

2. How to exit the execution upon one condition

3. How to stop running the current sub and return to the main sub


Solution : We have two key words 'Exit Sub' and 'End'

When to use this ?

Very simple ......

If we use 'END' in codes, all the executions will be stopped. No more codes will be processed.

But, The 'Exit Sub' can be used to stop running a sub and return to the place where it's called.

Eg :  Here we have 2 subs, The Main Sub calls the Process1 sub while running. In Process1 sub, we have a condition. If condition does not meet, it will return to the main sub and execute further codes. If I would have used 'END' instead of Exit Sub, the macro will stop running and no more codes will be processed.

Sub Main()
     Process1
     codes ....
     codes...
End Sub

Sub Process1()
If Activecell.value <> 1 Then Exit Sub
Codes .....
Codes.....
End Sub