VBA Interview Questions and Answers

VBA Frequently Asked Questions in various VBA (Visual Basic for Applications) Interviews asked by the interviewer. So learn VBA with the help of this VBA (Visual Basic for Applications) Interview questions and answers guide and feel free to comment as your suggestions, questions and answers on any VBA (Visual Basic for Applications) Interview Question or answer by the comment feature available on the page.

1. What is the use of excel?

Ans:

 It is an application to use for calculation, visualization and analytics.

2. What is the way to count a string in excel?

Ans:

 CountA

3.  What’s the Shortcut for sum?

Ans:

   ALT and +

4. What’s the Shortcut for Filter?

Ans:

  ALT, A, and T.

5. What’s the difference between sum if and count if?

Ans:

  Sum if, sums the values based upon criteria and count if counts the values based on criteria.

6. What is conditional formatting?

Ans:

   it allows to colour the cells, based upon conditions.

7. What’s the shortcut of Paste Special Values?

Ans:

  ALT E S V for values.

8. What’s the shortcut to split the excel sheet?

Ans:

  ALT W S.

9. What is the shortcut to reference the cell?

Ans:

   Fn + F4.

10. What does index function in excel?

Ans:

   It returns a value from a range at a given position.

11. What is a match function?

Ans:

  It is a lookup function in excel which searches for a value in an array and returns a relative position.

12. What is the lookup function in excel?

Ans:

  It returns a value from a range or array.

13. What is the VLookup function in excel?

Ans:

It is a function which vertically lookups and gets the relative data from a table or range.

14. What is the HLookup function in excel?

Ans:

   It is a function which horizontally lookups and gets the relative data from a table or range.

15. What is nested if function?

Ans:

   It is a function which allows to put multiple IF functions inside and allows to evaluate multiple criteria and returns the outcomes.

16. What is sumifs function?

Ans:

It is a function which allows you to sum on multiple criteria.

17. What is averageif function?

Ans:

  It calculates the average of given numbers based on criteria.

18. What is the ROUND function?

Ans:

  It is a function which returns a rounded number to the given number of digits

19. What is a pivot table?

Ans:

   Pivot table is a tool which allows us to summarize the data by sum, count, etc.

20. What is a pivot chart?

Ans:

It is an integrated feature of pivot table which allows to visualize the pivot table.

Subscribe For Free Demo

SUBSCRIBE

21. Define the chart in excel?

Ans:

   It is a visualization feature available in excel which allows users to create interactive bar charts, pie charts etc.

22. What is a scenario manager in excel?

Ans:

   It is a set of values that saves and could substitute cells on a worksheet and create different scenarios to view the results.

23. What does goal seek feature in excel?

Ans:

  It allows to adjust a value used in a formula to achieve a specific goal.

24. What is solver?

Ans:

  Solver is an ad in use for what if analysis and it can find optimal value for a formula in one cell.

25. What is an offset function in excel?

Ans:

  It is a function which returns a cell or ranges and moves right or left by a specified number of columns and rows.

26. What is the Choose function in excel?

Ans:

 It is a lookup function which returns a value from a list of values based on position.

27. What does filter in excel?

Ans:

  It is a feature which allows you to retrieve the set of data based on criteria.

28. What is sort function in excel?

Ans:

 It allows you to sort the range or table by ascending or descending.

29. What is the number of rows and columns in excel 2019?

Ans:

  1,048,576 rows and 16,384 columns.

30. What is a name manager in excel?

Ans:

   It is designed to manage names.

31. What is the IFERROR function in excel?

Ans:

  It returns a custom result when a function generates error.

32. What is slicer in excel?

Ans:

  It is a new way to filter the pivot table.

33. What are sparklines in excel?

Ans:

 It is a tiny chart in a cell which provides visual representation of data.

34. What is freeze pane in excel?

Ans:

 it is an excel feature which allows to keep visible rows and columns while scrolling to other areas of the sheet.

35. What does hyperlink in excel?

Ans:

  It returns a hyperlink from a given destination.

36. What is ribbon?

Ans:

  It is set of toolbars at the top of the window.

37. What is Macro?

Ans:

  It allows me to record the repeated tasks.

38.  What is function?

Ans:

  It is routine or procedure which performs a specific task.

39.  What sub procedure?

Ans:

   It is a procedure that doesn’t return values.

40. What is a User defined function?

Ans:

   It is a custom function which could be created using VBA.

41. Can be UDF be called in a macro

Ans:

 Yes

42. What is option explicit?

Ans:

  If a module contains Option Explicit, then variables must be declared all the variables explicitly.

43. What is array?

Ans:

   It is a group of variables and it is capable of storing more than one variable.

44. What is ReDim?

Ans:

  It is used to size or resize a dynamic array that has been already declared.

45. What is the scope of variables in excel vba?

Ans:

  it has defined scope and it could be local variable, module level, project level and global level.

46. How to comment in excel vba?

Ans:

  using apostrophe

47. What is the variant in excel vba?

Ans:

It is the default data type and it can hold any type of data.

48. What is CurrentRegion Properties?

Ans:

   It is a range bound any combination of columns and rows.

49. What is UsedRange?

Ans:

  It is used to select the range of used cells.

50. Does vba have a dictionary structure?

Ans:

   Yes, it has a Dictionary Structure.

51.What is VBA?

Ans:

• VBA stands for Visual Basic for Applications.
• VBA is Programming language available in MS Office Tools.

52. What are Data-types?

Ans:

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

53. Name some data types?

Ans:

• Boolean
• Byte
• Currency
• Date
• Double
• Integer
• Long
• LongLong
• LongPtr
• Object
• Single
• String
• Variant

54. What is Variant Data Type and Explain it?

Ans:

 Variant data type is default data type and it can hold any type of data. This will allocate maximum memory to hold the Varian Type. We use Variant data-type when we don’t know the type of the data or to accept the multiple data types in a single variable.

55. 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 enhance the existing objects.

56. What is the difference between Procedure and Functions?

Ans:

   Procedures or Subroutines will not return a value; functions will return values.

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

Ans:

   Arguments can be passed in two ways in VBA Subroutines and Functions:

• 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 ended.
• 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.

58. What are the built-in Class modules?

Ans:

Workbook, Worksheet modules are Class modules.

59. What is the basic object model of Excel?

Ans:

•   Please find the below basic Object model of Excel.
• Application –> Workbooks –> Worksheets –> Range / Chart

60. Why do we need to use macros?

Ans:

  A macro is nothing but a set of instructions which are stored in the Visual Basic module in a VBA Editor. It helps in automating common repetitive tasks on a daily, weekly or monthly basis by running macro. Using macros, you can save a lot of time, increase productivity and on time delivery to customers.

61. What is the shortcut to go to the VBA editor screen?

Ans:

   You can use the ‘Alt+F11’ key to go to VBA editor screen

62. How do I stop recording macro?

Ans:

  Please find the following steps to stop recording macro in the workbook.

• Step 1: Go To Developer tab from the main ribbon of Excel window.
• Step 2: Click on the ‘Stop Recording’ command button to stop from the recording macro.

63. How do I delete macros from the workbook?

Ans:

  Please find the following steps to delete macros from the workbook.

• Step 1: Go To Developer tab from the main ribbon of Excel window.
• Step 2: Click on the Macros command button to see the available macros in the active workbook.
• Step 3: Once you click on the Macros command button, Macro dialog box will appear on the screen.
• Step 4: Select the macro name which you want to delete macro and then click on the ‘Delete’ command button.
• Step 5: Now, It will show the confirmation dialog box. Click on Ok to delete the macro.

64. How to run macros automatically while opening Workbook in Excel VBA?

Ans:

This is one of the most commonly asked Excel VBA Interview Questions and Answers. You can use Workbook_Open() Event to run macros automatically in Excel VBA while opening Workbook.

To get a Workbook_Open() Event in Excel, please find the following steps.

• Go To VBA Editor.
• Click on ‘ThisWorkbook’ from the Project Explorer.
• Now, you can see two drop down lists on the right side.
• Select ‘Workbook’ from the first drop down list and then choose ‘Open’ from the second drop down list.
• Now, you can see the following code.

Private Sub Workbook_Open()

  ‘Your Statements…..

End Sub

• You can add the code in-between the above lines to run a macro.
• Save and close the workbook
• Now, reopen the workbook to test the macro.

Example:

• Private Sub Workbook_Open()
• MsgBox “Workbook has Opened Successfully.”, vbInformation
• End Sub

In the above example, the macro will run automatically when we are opening a workbook. Now, it will display a message like “Workbook has Opened Successfully.”.

Or we can also define a procedure named Auto_Open() in any code module, this will execute while opening the macro file.

65. How do I show UserForm each time when I open a workbook?

Ans:

 You can use Workbook_Open() Event to show UserForm automatically in Excel VBA when we open Workbook.

To get a Workbook_Open() Event in Excel, please find the following steps.

• Go To VBA Editor.
• Click on ‘ThisWorkbook’ from the Project Explorer.
• Now, you can see two drop down lists in the right side of the VBA Editor window.
• Select ‘Workbook’ from the first drop down list and then choose ‘Open’ from the second drop down list.
• Now, you can see the following code.

Private Sub Workbook_Open()

     ‘Your Statements…..

End Sub

• You can add the code in-between the above lines to run a macro.
• Save and close the workbook
• Now, reopen the workbook to test the macro.

Example:

• Private Sub Workbook_Open()
• ‘ Here “MyForm” is the UserForm name.
•  MyForm.Show
• End Sub
• In the above example, the macro will show the UserForm(Named ‘MyForm’) automatically when we open Workbook.

Note: Before running above macro add UserForm and then assign the name of the UserForm to ‘MyForm’

66. What is the difference between ByVal and ByRef?

Ans:

 ByVal vs ByRef in VBA is also one of the most frequently asked Excel VBA Interview Questions and Answers.

ByVal:

Specifies that an argument is passed in such a way that the called procedure or property cannot change the value of a variable underlying the argument in the calling code.

ByRef:

Specifies that an argument is passed in such a way that the called procedure can change the value of a variable underlying the argument in the calling code.

Note: Default value is ByRef. It is good practice to include the ByRef declaration if you are going to change the value of the parameter.

67. What are the available looping statements?

Ans:

•   Please find the different looping statements which are available in Excel VBA.
• For…. Next loop, Do While…. Loop, Do until Loop, Do….Loop Until..,Do While Not…Loop, While…. Wend loop

68. How to add a UserForm or module or class to a VBA Project?

Ans:

•   Please find the following steps to add UserForm or Module or Class Module to the VBA Project.

Add UserForm:

• Step 1: Go To Insert menu in the VBA Editor window.
• Step 2: Click on ‘UserForm to add to the Project. Now you can see added UserForm in the Project Explorer. Default UserForm name will be ‘UserForm1’. You can change the UserForm name with using properties

Add Module:

• Step 1: Go To Insert menu in the VBA Editor window.
• Step 2: Click on ‘Module’ to add to the Project. Now you can see the added Module in the Project Explorer. Default module name will be ‘Module1’. You can change the module name using properties.

Add Class Module:

• Step 1: Go To Insert menu in the VBA Editor window.
• Step 2: Click on ‘Class Module’ to add to the Project. Now you can see the added Class Module in the Project Explorer. Default Class module name will be ‘Class1’. You can change the class module name using properties.

69. How to assign values to an array?

Ans:

 We can assign values to an array in the following way.

• ‘Declare an array variable
• Dim aValue (2) As Integer
• aValue(0)=”first”
• aValue(1)= “Second”
• aValue(2)= “Third”

‘Or

Dim aValue () As Integer={” first “,”Second”,”Third”}

70. What are the various data types available in the VBA?

Ans:

•  The following are different data types which are available in Excel VBA.
• Byte, Boolean, Integer, Long, Single, Double, Currency, Decimal, Date, Object, String, Variant and User defined data types.

VBA Sample Resumes! Download & Edit, Get Noticed by Top Employers!DOWNLOAD

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

72. How to assign macros to a button?

Ans:

 please find the following steps to assign macro to a button.

• Step 1: Go to the Developer tab from the excel ribbon menu, go to Forms Control group.
• Step 2: Click on Button from the Form Controls.
• Step 3: Click the worksheet location where you want the button to appear.
• Step 4: Drag the button in the sheet.
• Step 5: Right click on the button, click on Assign Macro.
• Step 6: Assign Macro Dialog box will appear now, click the name of the macro that you want to assign to the button. Click on OK.
• Step 7: You can format the control by specifying control properties.
• Step 8: Click on the button to test. Now, your macro should run

73.  What is Visual Basic for Applications(VBA)?

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.

74. What is the Scope of Variables?

Ans:

We can define variable in different levels:

• 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 statements at the top of any module, can be accessed in the entire project.

75. What is  the difference between Msgbox Statement and Msgbox function?

Ans:

MsgBox is a built in VB function which displays a Message Box and MsgBoxQ is a function defined by the user.

76. Where can you write your VBA program for Macro?

Ans:

vModule is the place where you can write VBA program for Macro, to insert a Module navigate to Insert -> Module

77. What are the  four different cursor and locking types in ADO and describe them briefly?

Ans:

Cursor types:

• Forwardonly
• Static
• Keyset
• Dynamic

Lock types :

•  lock pessimistic
• lock optimistic
• lock batch optimistic
• Adlockreadonly

78.  How “reference counting” in VBA is done?

Ans:

In VBA, soon a variable goes out of scope, the reference counter on the reference object is decremented. When you assign the object reference to another variable, the reference counter is incremented. While when your reference count reaches to zero it terminates the event.

79. Which controls have refresh method, clear method?

Ans:

Datagrid,listbox,combobox,label,button have refresh method and textbox has clear method almost all controls have refresh method

80.  Which controls can not be placed in MDI?

Ans:

The Controls which do not have Align property can’t be placed on MDI Form.

E.g., Picturebox only has Align property in Standard Components of VB other controls don’t have align property and hence cannot be drawn on MDI Form.

Timer control can also be placed on MDI form.

81. How can we declare variables and constants in VBA?

Ans:

In VBA, a variable can be declared with the keyword “DIM” while a constant is declared with the keyword “Const.”

82. What is the insert module and Goal Seek functions present in VBA?

Ans:

The chief use of VBA is to make use of its special function which helps in repeated actions. Goal seek function helps to reduce manual entry of the code each and every time. This solves the problem of repeated function entry by automating functions and actions. Subroutines are inserted into the VBA editor and command insert module.

83. How to format expressions by using VBA?

Ans:

Format functions can be used to format many of the expressions such as currency, time, date, percentages and numbers. These functions are much simpler to use in VBA. User defined date, numeric and string formats are present in many of the applications.

84. Explain what ADO, ODBC and OLEDB are?

Ans:

• ADO: ActiveX Data Objects or ADO is a universal data access framework that encompasses the functionality of DAO
• ODBC: Open Database Connectivity or ODBC is a technology that enables a database client application connect to an external database
• OLEDB: It is a low-level programming interface designed to access a wide variety of data access object linking and embedding (OLE)

85. Mention what are the comments style 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 (‘)

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

87. What does Option Explicit refer to?

Ans:

Option Explicit makes the declaration of Variables Mandatory

88.  What is the meaning of “Option Explicit”? Where should it 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 applications in which type errors are common. Before starting any sub-procedures it can be used inside a module.

89 . How can you decrease the reference counter explicitly?

Ans:

To decrease the reference counter explicitly, you need to set a variable to “Nothing”.

90. What is the explicit line option?

Ans:

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.

91. Explain what COM (Component Object Model) objects are in VBA?

Ans:

COM objects are usually .dll files, and are compiled executable programs.

92. Explain the difference between visual basic, VB script and visual basic applications?

Ans:

Visual basic is useful if you are planning to develop your programs from scratch. This language helps you in developing Active x controls, exe files, etc.

Visual script is a powerful tool, through which you can create small scale applications on web pages, automation applications, etc. Integrated development environment is not present for VB script.

Visual basic applications are very useful in automating your existing application. VB application is useful for developing already existing applications.

93. Which Property is used to compress an image in image control?

Ans:

Stretch

94. Benefit of wrapping database calls into MTS transactions?

Ans:

Benefit of wrapping database calls into MTS transa…

If database calls are made within the context of a transaction, aborting the transaction will undo and changes that occur within that transaction. This removes the possibility of stranded or partial data.

95. What do ByVal and ByRef mean and which is the default?

Ans:

If you pass an argument by reference when calling a procedure, the procedure accesses the actual variable in memory. As a result, the variable’s value can be changed by the procedure.

If you pass an argument by value when calling a procedure, the variable’s value can be changed within the procedure only outside the actual value of the variable is retained.

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.

Argument Passing ByVal

Describes passing arguments by value, which means the procedure cannot modify the variable itself.

Argument Passing ByRef

Describes passing arguments by reference, which means the procedure can modify the variable itself.

96. Mention the method that is called from the ObjectContext object to notify MTS that the transaction was unsuccessful or successful?

Ans:

Setabort and setcomplete method are called from the ObjectContext object to notify MTS that the transaction was unsuccessful or unsuccessful

97. Explain Which property of the textbox cannot be changed at runtime and what?s the maximum size of a textbox?

Ans:

MultiLine Property

No limits

98.  What is the code to find a last used Row in a column or last used Column of a row?

Ans:

To find the last row in a column, the command used is End(xlUp) and to find the last column in a row, the command used is End(xlToLeft).

99. How to use a data validation function in VBA?

Ans:

Data validation is an important concept in VBA. Application procedure and a custom dialog box can be used to correct input errors. You can assign an application procedure to the frame of a dialog box. Error messages or custom dialog boxes can be highlighted with the first field with errors.