Excel VBA interview Questions & Answers

1. What is the shortcut to go to VBA screen?

Ans: ALT + F11

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 file extension of excel workbooks with macros in Excel 2003, 2007, 2010 ?

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

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. What is the meaning of "Option Explicit"? Where it should be used?

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

7. Where it should be used?

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

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

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

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

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

13. Code to find a Last used Row in a column or Last used column of a Row.

Ans:                                                

Last Row in a column can be find using End(xlUp) from the last Cell and Last Column in a row can be find using End(xlToLeft). For e.g. Range("A1048576").End(xlUp).Row gives last used row of Column A.

14. How to debug a VBA code?

Ans:

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

15. What is the difference b/w Functions and Subroutines?

Ans: There are so many differences. I noted few.
i) Subroutines never return a value but function does return values.
ii) A function could not change the values of actual arguments whereas a subroutine could change them.

iii) A subroutine cannot be used in a worksheet

iv) A subroutine can manipulate the any objects. A Function Cannot.

v) A function will not be listed in the names of macros

16. Draw basic Excel Object Model?

Ans:

Application --> Workbooks --> Worksheets --> Range / Chart

17. Union is used for ___?

Ans:

To unite the different ranges depending on the logic. It is similar to set union, here range works as set. For eg. Set nrange = Union (rng1, rng2).

18. What are properties, methods, events and objects?

Ans:

All the controls in the Toolbox except the Pointer are objects in Visual Basic. These objects have associated properties, methods and events. 
A property is a named attribute of a programming object. Properties define the characteristics of an object such as Size, Color etc. or sometimes the way in which it behaves.
A method is an action that can be performed on objects. For example, a dog is an object. Its properties might include long white hair, blue eyes, 5 pounds weight etc. A complete definition of dog must only encompass on its looks, but should also include a complete itemization of its activities. Therefore, a dog's methods might be move, jump, play, breath etc.
Visual Basic programs are built around events. Events are various things that can happen in a program. Let us consider a Textbox control and a few of its associated events to understand the concept of event driven programming. The Textbox control supports various events such as Change, Click, Mouse Move and many more that will be listed in the Properties dropdown list in the code window for the Textbox control. We will look into a few of them as given below.
* The code entered in the Change event fires when there is a change in the contents of the Textbox
* The Click event fires when the Textbox control is clicked.
* The Mouse Move event fires when the mouse is moved over the Textbox.