Top 25+ VBA Interview Questions & Answers [ EXCEL TRICKS ] |ACTE
VBA Interview Questions and Answers

Top 25+ VBA Interview Questions & Answers [ EXCEL TRICKS ]

Last updated on 29th Jun 2020, Blog, Interview Questions

About author

Sandeep (Sr Technical Project Manager )

Highly Expertise in Respective Industry Domain with 7+ Years of Experience Also, He is a Technical Blog Writer for Past 4 Years to Renders A Kind Of Informative Knowledge for JOB Seeker

(5.0) | 16547 Ratings 6109

Visual Basic for Applications is an implementation of Microsoft’s event-driven programming language Visual Basic 6, which was declared legacy in 2008, and its associated integrated development environment.Excel VBA is not a programming language. VBA is a programming language and runtime environment that can be hosted by the Excel application, or any other Microsoft Office application, or a number of other applications such as Photoshop and CorelDRAW.

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

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

    Course Curriculum

    Enroll in VBA Training Cover In-Depth Concepts By Top-Rated Instructors

    • Instructor-led Sessions
    • Real-life Case Studies
    • Assignments
    Explore Curriculum

    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.

    Course Curriculum

    Best VBA Course Taught By Microsoft Certified Trainers

    Weekday / Weekend BatchesSee Batch Details

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

    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 Visual Basic module in a VBA Editor. It helps in automating common repetitive tasks on daily, weekly or monthly basis by running macro. Using macros, you can save lot of time, increase productivity and on time delivery to customers.

    61. What is the shortcut to go to 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 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 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:

     lease 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. How to find the last row in the worksheet?

    Ans:

      We need to find Last used Row with data if we want to perform a certain task on each row of the worksheet. Please find the following statements to find the last row in the worksheet.

    Find last row in the worksheet:

    Dim lastRow As Long

    • lastRow = ActiveSheet.Cells.SpecialCells(xlLastCell).Row

    74. How to find the last column in the worksheet?

    Ans:

     We need to find Last used Column with data if we want to perform certain task on each column in the worksheet. Please find the following statements to find last column in the worksheet.

    Find last column in the worksheet:

    Dim lastColumn As Long

    • lastColumn = ActiveSheet.Cells.SpecialCells(xlLastCell).Column

    75. How to fasten the VBA macro?

    •  We have several best practices to follow while coding VBA. This is also one of the most frequently asked Excel VBA Interview Questions and Answers. This helps the interviewer to understand your real time experience in VBA.
    • We can fasten the execution of macros or VBA Procedures by following the below tips.

    • Declare the variables and avoid using ‘Variant’ Data Type.

    • Turn Off Screen Updating

        • Turn Off Automatic Calculations

        • Disable Events

        • Use With Statement

        • Use vbNullString instead of “”.

        • Release memory objects at the end of the procedure. 

        76. How to enable/disable screen updating? Or how do I hide the process of running macro?

        Ans:

        •   Enabling and Disabling the Screen updating will be used in almost all projects. Questions on understanding the screen updating are also one of the most frequently asked Excel VBA Interview Questions and Answers.
        • Here is the approach to enable or disable screen updating or screen flickering.

        In order to stop the screen flickering, stop the screen updating at Staring of the procedure:

        • Application.ScreenUpdating = False
        • You have to set back screen updating as True Before ending of the procedure:
        • Application.ScreenUpdating = True

        77. How do I stop triggers or display alerts or error warnings while running macros?

        Ans:

          Here is the approach to stop trigger or display alerts or error warnings.

        In order to stop triggers, disable the display alerts at Starting of the procedure:

        • Application.DisplayAlerts = False
        • You have to set enable display alerts before ending of the procedure:
        • Application.DisplayAlerts = True

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

        Ans:

        Please find the below different types of error handling techniques.

        • On Error Resume Next
        • On Error Goto Err_Lbl
        • On Error Goto 0

        79. How to check whether a file exists or not in a specified location?

        Ans:

          You can find if a specific file exists or not in the following two ways.

        1. Using FileSystemObject:

        • Here is the example to check if a file exists or not using ‘FileSystemObject’.
        • Sub Check_File2()
        • Dim FSO
        • Dim sFileName As String
        •     sFileName = “C:/Test/Workbook.xls”
        • Set FSO = CreateObject(“Scripting.FileSystemObject”)
        • If Not FSO.FileExists(sFileName) Then
        •         MsgBox “File Does Not Exists.”
        • Else
        •         MsgBox “File Exists.”
        • End If
        • End Sub

        2. Using Dir Function:

        • Here is the example to check if a file exists or not using the ‘Dir’ function.
        • Sub Check_File1()
        • Dim sFileName As String
        • sFileName = “C:/Test/Workbook.xls”
        • If Dir(sFileName) <> “” Then
        • MsgBox “File Exists.”
        • Else
        • MsgBox “File Does Not Exists.”
        • End If
        • End Sub

        80. How to save a workbook using Excel VBA?

        Ans:

        •   You can save the workbook using the following example. In the below example we are adding new workbook and then assigned it to an object named Wkb. Finally we are saving workbook with using

        Save method of workbook object.

        • Sub Save_Workbook()
        • Dim Wkb As Workbook
        • Set Wkb = Workbooks.Add
        •     Wkb.Save   
        • End Sub

        81. How to change the existing file name?

        Ans:

        • You can change theexisting file name of workbook using the following example. In the below
        • For example, we are adding a new workbook and then assigned it to an object named Wkb.
        • Finally we are changing workbook name with using SaveAs method of workbook object.
        • Sub SaveAs_Workbook()
        • Dim Wkb As Workbook
        • Set Wkb = Workbooks.Add
        •     ActiveWorkbook.SaveAs
        • Filename:=”C:\Test.xlsm”   
        • End Sub

    Are you looking training with Right Jobs?

    Contact Us
    Get Training Quote for Free