Top Excel Macros Interview Questions and Answers

Q1. How do you run an Excel macro step by step?

Ans 

Q2. What is the difference between VBA and macros?

Ans

Q3. 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 predominantly used with Microsoft office applications like MS-word, MS-Access, and MS-Excel.

Q4. What is macro script? 

Ans

A macro is a sequence of key or mouse actions and host commands that you can perform with a single action, such as a keystroke. You can edit an existing macro or create a new macro by selecting Preferences → Macro/Script from the Edit menu

Q5. What is break mode in macro?

Ans

Break mode in VBA is used for debugging and fixing errors. If we need to continue with execution, we should either click on the play button or press F5 or F8. Or we should just end the program's execution and start a fresh run of the code.

Q6. How do I write a script in Excel?

Ans

Getting started. First, select the Automate tab on the ribbon. This will expose your two primary options in the Scripting Tools group: Record Actions and New Script. To create a new Office Script, press the Record Actions button, then start taking the steps you want to automate.

Q7. What is debug print VBA Excel?

Ans

Print is telling VBA to print that information in the Immediate Window. This can be useful when you want to see the value of a variable in a certain line of your code, without having to store the variable somewhere in the workbook or show it in a message box.

Q8. What is type mismatch in VBA?

Ans 

Q9. Explain about function pointers in VBA?

Ans

The VBA (Visual Basic Information) have flexible applications, but there is a limitation to a function pointer in VBA. Windows API has inadequate support for function pointers because it has the ability to use the function but not the functional support to call back the function. It has inbuilt support for the call but not for a callback.

Q10. Explain what is the meaning of “Option Explicit”? Where it should be used?

Ans

“Option Explicit” makes the declaration of variables mandatory. Line explicit function makes the compiler to determine all the variables that are not declared by the dim statement. This command diminishes the problem of type errors significantly. It is used in VBA because it deals with information rich application in which type errors are common. Before starting any, sub-procedures it can be used inside a module.

Q11. Mention the difference between the Subroutines and Functions in VBA?

Ans

• 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

Q12. What is the VBA Data Types?

Ans

While declaring a variable (or even a constant) you need to specify the data type. Declaring the data type helps VBA to understand that which type of value you want to store in the variable. Let us say if you want to store a date in the variable as we have done in the previous example, you need to specify the data type as the date.

Q13. What are Scope of Variables?

Ans

• Local Level: Variables which are defined with DIM statement in a procedure or functions
• Module Level: Which are defined with DIM statement on top of a module, can be accessed in entire module
• Global Level: Which are defined Public statement at top of any module, can be accessed in entire project.

Q14. Explain how can you pass arguments to VBA functions?

Ans

ByVal: When an argument is passed By Value, the value assigned to the argument is passed to the procedure. And any changes that are made to the argument inside a procedure, it will be lost when the procedure is ends.

ByRef: When an argument is passed By Ref, the actual address assigned to the argument is passed to the procedure. And any changes that are made to the argument inside the procedure will be passed when the procedure ends.
And By Ref is default in VBA.

Q15. What are different Types of core Modules available in VBE?

Ans

Code Module: Default Modules which we use to write all procedures and functions
UserForms: UserForms helps to develop GUI (Graphical User Interface) applications.
Class Modules: Class module allows to create new objects and define methods, properties and events. Also, it will allow to enhance the existing objects.

Q16. What are the available looping statements?

Ans

For…. Next loop, Do While…. Loop, Do until Loop, Do….Loop Until..,Do While Not…Loop, While…. Wend loop

Q17. What are the different UserForm Controls and ActiveX Controls?

Ans

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

Q18. What are the different types of error handling techniques?

Ans

1. On Error Resume Next
2. On Error Goto Err_Lbl
3. On Error Goto 0