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