Top Excel VBA Macros Interview Questions & Answers - Samples from a hiring expert

I spent 11+ years of my career in various version of Excel and VBA. I used to hear from lot of people (even VPs & top leaders) that Excel is going to get outdated. Funny part is, I've been listening the same words again and again past 10 years, but nothing happened.

Excel is still a primary tool for data analysis and reporting and we cannot avoid VBA when you talk about Excel. I see that it will still have an important role for at least next 10 years as we don't have an alternative available which provides simple way to get complex things done.

I would like to share some Excel VBA Macros Interview Questions & Answers, which I had used to conduct technical interviews behalf of my clients to hire candidates for them.

The Questions has been prepared keeping in mind to evaluate the candidate's depth of knowledge in Excel VBA. This question bank is helpful for both Interviewee and Interviewer as it provides a quick channel of questions and answers covering major topics of Excel and VBA.

If you are a beginner in Excel VBA, I would not suggest this for you. Because in most of the cases the interviewer will not stop asking questions by giving a one line answer. He may ask you to describe more about the facts of the topic he asked to understand how good you are in the tools.

Kindly share the feedback in comments or contact me directly macromatician[at]gmail.com

I will be updating more when I have time. Click on the below links to go to each pages.


Basic Excel VBA Macros Interview Questions

1. Basic Excel VBA Macros Interview Questions - Set 1 - January 2018 -  10 Questions
2. Basic Excel VBA Macros Interview Questions - Set 2 - February 2018 -  10 Questions


Advanced Excel VBA Macros Interview Questions:

1. Excel VBA Macros Interview Questions Part I - 10 Questions
2. Excel VBA Macros Interview Questions Part II - 10 Questions

Basic Excel VBA Macros Interview Questions & Answers - II

Part 2

11) How to add a module to a VBA project?
Ans: Right Click on VBA project in VBA screen > Insert > Click on Module

12) Why Developer tab is not visible in my application, How to enable it?
Ans : By default, the Developer is disabled in Office Applications. We can enable Developer tab from Customise Ribbon section in the Options window of Office Applications.

13) How to record a macro?
Ans : First of all, you need to enable the Developer tab. Then you can either click on the record button on the status bar or the record button on the Developer tab.

14) Why I’m not able to record macros in my application?
Ans : First of all, you need to enable the Developer tab. Also check the macro security section to Enable macros.

15) What is a comment, What all are the types of comments used in VBA ?
Ans : Comments are used to document the program logic and the user information with which other programmers can work seamlessly on the same code in future.
There are mainly two methods in VBA to represent comments.
• Any statement that begins with a single quote is treated as comment
• Or you can use statement REM instead of single quotation mark (‘)

16) Difference between VB6, VBScript, VBA, VB.NET?
Ans : VB6: Visual Basic is Windows Application Development Programming language developed by Microsoft.
VBScipt: Visual Basic Script is mainly developed for Internet Explorer. Similar to Java-script.
VBA: Visual Basic for Applications is a Macro language available in all MS Office Tools.
VB.NET is a one of the .NET programming language from Microsoft. We can develop COM Add-ins for MS Office using VB.NET or C#.NET.

17) Why VBA is called as Object Based Language, instead of Object Oriented Programming Language?
Ans :  VBA Does not support all the OOPS techniques (VBA will support Polymorphism, Encapsulation and not Inheritance). So, its generally called as Object Based Programming Language.

18) My colleague sent me a macro file. But I’m not able to run it. What’s the issue?

Ans :

There can be multiple reasons.

: - Sometimes when we open a file which is received from someone else, it opens in ‘Protected View’. We should click on Enable editing and try running the macro again.

:- Other scenario can be caused by Macro security. If the macro security is on ‘Disable macros without notification’, then we cannot run the macros.

19) How to protect a macro with a password
Ans : Go to VBA IDE, then right click on the VBA project in Project explorer, then Enter the password in Protection tab in the pop up window.

20) Mention the difference between the Subroutines and Functions in VBA?
Ans :
The difference between the Subroutines and Functions are
• Subroutines never return a value, but functions does return values
• Subroutines could change the values of the actual arguments while a function could not change the value of actual arguments
• Subroutines cannot be called from Excel formulas, but a function can be used as an Excel formula
• Subroutines are visible in Macro list (if there are not arguments given), but functions are not visible in Macro list.


Click Here to View more Excel VBA Macros Interview Questions & Answers

Basic Excel VBA Macros Interview Questions & Answers - I

1) Explain what is VBA or Visual Basic for Applications?
Ans: VBA stands for Visual Basic for Applications. It is an event driven programming language developed by Microsoft. It is mainly used to automate tasks in Microsoft office applications like MS-word, MS-Access, and MS-Excel.

2) What is macro? 
Macro is another name of a VBA program. It’s is a set of programming instructions written in VBA code which can be used to eliminate the need to repeat the steps of commonly performed tasks over and over again.

3) What is Data-types in VBA?

Data types helps to declare Variables with specific data, this helps to VBA to know the type of the data and assign the memory based on the DataType of the Variable.

4) Name some of the data types?

1. Boolean
2. Byte
3. Currency
4. Date
5. Double
6. Integer
7. Long
8. LongLong
9. LongPtr
10. Object
11. Single
12. String
13. Variant

5) Where or How you can write VBA program or Macro?

VBA programs are written in the code window of IDE (Integrated Development Environment) which is a part of all Office Applications. You can access the IDE by entering the shortcut ALT + F11 or by clicking on Visual Basic on Developer tab.

6) What are the file extension of excel workbooks with macros in Excel 2003, 2007, 2010, 2013 and 2016 ?
Ans : Excel 2003 = xls, Excel 2007 = xlsm, From Excel 2010 = xlsm

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

8) What is the shortcut to go to VBA screen ?
Ans: ALT + F11

9) Can we record a looping statement ? 
Ans : NO

10) Give some examples of looping statements ?
Ans: For loop, Do While Loop, Do Until Loop

Click Here to View more Excel VBA Macros Interview Questions & Answers

Excel VBA Macros Interview Questions & Answers - Part II

1. What is the difference between ByVal and ByRef and which is default ?

Solution: ByRef: If you pass an argument by reference when calling a procedure the procedure access to the actual variable in memory. As a result the variable's value can be changed by the procedure.
ByVal: If you pass an argument by value when calling a procedure the variable's value can be changed with in the procedure only outside the actual value of the variable is retained.
ByRef is default: Passing by reference is the default in VBA. If you do not explicitly specify to pass an argument by value VBA will pass it by reference.

2. What is the meaning of Option Explicit and Option Base?

Solution:

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.

3. What are various data type and their size?

Solution:

i) 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.
ii) The Byte data type is an 8-bit variable which can store value from 0 to 255.
iii) The Double data type is a 64-bit floating point number used when high accuracy is needed.
iv) 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.
v) The Long data type is a 32-bit number which can range from -2,147,483,648 to 2,147,483,647.
vi) 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.
vii) 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.

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

Solution:

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.

5. Difference between ActiveX and Form Controls.

Solution:

i) Forms controls can be used on worksheets and chart sheets. Forms controls can also be placed within embedded charts in Classic Excel (though not in Excel 2007). ActiveX controls can only be used on worksheets. ActiveX controls do not work in MacExcel.
ii) The Forms controls aren’t very complicated, and they have been part of Excel for longer (they were used in Excel 5/95’s dialog sheets) than the Controls Toolbox (Excel 97), so it stands to reason that they’d be more seamlessly integrated. Being newer, the ActiveX controls have richer formatting possibilities. Both can link to cells and ranges in the worksheet.

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

Solution: 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

 

7. How to debug a VBA code?

Solution:

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

8. Draw basic Excel Object Model.

Solution:

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

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

Solution:

For details click here --> http://msdn.microsoft.com/en-us/library/ms172576%28VS.80%29.aspx
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 cat is an object. Its properties might include long white hair, blue eyes, 3 pounds weight etc. A complete definition of cat must only encompass on its looks, but should also include a complete itemization of its activities. Therefore, a cat'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, MouseMove 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 MouseMove event fires when the mouse is moved over the TextBox

10. Union is used for _____________ ?

Solution:

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)

Click here to see more Excel VBA Macros Interview Questions

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