VBA interview questions

Data analysis is one of the renowned job posts these days. VBA is a great tool that helps automate common repetitive tasks daily, weekly, or monthly by running the macro. Therefore, to apply for the Business data analysis or VBA developer post, you may require to confront the face-to-face interview round.

In this tutorial, we will cover the most commonly asked Fundamental and Advanced VBA Interview Questions that can be useful for Freshers and Experienced VBA Developers.

1) Define macros. What is the advantage of using Macros?

A VBA macro is a set of instructions (or code) stored in the Visual Basic module in a VBA Editor. The VBA Macros are written in a module. To insert a module go the VBA editor and click on Insert ->Module

By running VBA macro, the excel users can automate common repetitive tasks daily weekly. Therefore using macros can save your time, improve productivity, and deliver on time to clients.

2) Define the basic object model of Excel?

Following is the basic Object model of Excel.

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

3) What is the quickest way to open the VBA editor screen?

The easy and fastest way to go to the Visual Basic editor window is by using the keyboard shortcut i.e., ‘Alt + F11’

4) What do you understand by the term ‘Option Explicit’?

Option Explicit is used in VBA to force the variables to be declared before using the variable. It helps the VBA developers to dodge the type errors and create a bug free code.

5) What are the types of core Modules present in Visual Basic Editor?

There are three different modules available in VBE:

Class Module: These are default modules commonly used to write functions.
UserForms: They help to design and develop GUI applications.
Class Modules: They helps the user to create new objects, methos, procedures and events.

6) Is it possible to run the macros automatically while opening a worksheet in Excel VBA? IS yes, how is it?

Yes, by using the VBA macros we can automatically open a worksheet in Excel. For execute the same we will use the Workbook_Open() Event to run macros.

Following are the steps to run Workbook_Open() Event in VBA Excel:

Click Alt+F11 to open the VBA Editor.
Go to the Project Explorer and select the ‘ThisWorkbook’.
You will see two drop down lists in the right panel of the pane.
From the first drop down select the ‘Workbook’ option and from the second drop down choose the ‘Open’ option.
In the editor window, you will see the below given code.

Write the VBA macros in-between the above lines to run the macros automatically while opening a worksheet in Excel VBA.
Save and close the workbook
Reopen the same workbook to test the created macro.

Example

  Private Sub Workbook_Open_Example()      MsgBox “The workbook has Opened automatically.”, vbInformation  End Sub  

7) In VBA, how many scope variables are there?

In VBA editor, there exists different levels of scope variables. Though we can define the variable in three levels that are as follows:

Local Level: The Local Level variables are defined with a DIM statement in a procedure or function.
Module Level: The Module level scope variables are defined with the DIM keywords on top of a VBA code. The special thing about these variables is that they can be accessed in the entire module.
Global Level: The Global Level variables are defined using a Public keyword at the top of your VBA programming module. They can be accessed anywhere in the entire VBA project.

8) Mentions the steps to create object Variable for workbook, worksheet, etc.?

We can create object variable and late it can be used in the entire macro.

Following is the VBA macro to create object for workbook, worksheet, etc.

  Sub Create_Obj_Wrkbok()     ‘creating a workbook object  Dim Wrbk As Workbook  ‘Assigning  the Active Workbook to ‘Wrbk’ object  Set Wrbk = ActiveWorkbook     ‘Using MsgBox to display the first worksheet name  MsgBox Wrbk.Sheets(1).Name  End Sub  

9) What is an Array? How will you create or define array in VBA?

An array is a set of variables that contains data of similar data type. They are used to refer to a specific array value by using array name and the index number.

We can create and define size of an array variable in the following way.

Syntax

Example:

In the above procedure ‘arrVal’ is an array name and ‘5’ represents the array size.

10) How do I stop recording macro?

Below given are the steps to stop recording macro in your active Excel workbook:

Step 1: Open your Excel workbook. Click on the ‘Developer tab’ located in the main ribbon menu of Excel window.

Step 2: Click on ‘Stop Recording’ button to stop from the recording your active macro.

11) How do I delete macros from the workbook?

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

Step 1: Open your Excel workbook. Click on the ‘Developer tab’ located in the main ribbon menu of Excel window.

Step 2: Next, click on the Macros button to check all the existing macros present in your Excel workbook.

Step 3: It will open the Macro dialog box on the screen.

Step 4: Select the macro name which you want to delete, and then click on ‘Delete’ command button.

Step 5: The confirmation dialog box will appear. Click on Ok to confirm the delete of the selected macro.

12) What are the different ways to speed up the VBA macro?

This is one of the most common VBA questions frequently asked in the interview round. By asking it, the interviewer wants to understand your real experience in VBA coding and how to take care of coding techniques.

While writing VBA code, we have several best practices to follow. We can speed up the execution of VBA macros by following the given techniques.

Declare your variables without using the ‘Variant’ Data Type. As it takes more space.
Always remember to turn Off the ‘Screen Updating’
Turn Off the automatic calculations
Always remember to disable the Events
Use With Statement
Use the vbNullString instead of “”.
Release all the memory objects at the end of the procedure.

13) What are the different built-in Class modules available in VBA:

The different built-in classes are as follows:

Workbook modules
Worksheet modules

Microsoft Access VBA Interview Questions

1) Write a VBA code to create a table in the required database.

Following is the VBA macro to create a table in the required database:

  ‘Create table in the required database using VBA  Sub Create_MSAcsess_Table()         Dim DataBse As Database      ‘Open your required Database      Set DataBse= OpenDatabase(“C:UsersReema DocumentsAccessDatabase.accdb”)       ‘ Create a table with three employee fields.      DataBse.Execute “CREATE TABLE MyTable ” _          & “(EmpName CHAR, EmpNumber INT, EmpAddress CHAR);”      DataBse.Close    End Sub  

2) Write VBA macro to quickly insert records in the MS Access database table.

Following is the VBA macro to quickly insert records in the MS Access database table:

  ‘Add or Insert records to the access database table using VBA  Sub Insert_Records_Table()         Dim DataBse As Database      Dim SQLQry As String      ‘Open the required Access Database         Set DataBse= OpenDatabase(“C:UsersReema DocumentsAccessDatabase.accdb”)       ‘Write the SQL query to insert 3 fields in table      SQLQry = “INSERT INTO MyTable values(‘Reema’,5678,’India’);”      ‘ Adding records in the database table      DoCmd.RunSQL SQLQry      DataBse.Close     End Sub  

3) Write a VBA code to update an existing record in the database table.

Following is the VBA macro to update an existing record in the Ms Access database table:

  ‘Updating the existing records in the Ms Access database table   Sub Update_Existing_Records_Tble()         Dim DataBse As Database      Dim sSQL As String      ‘Open your Access Database   Set DataBse= OpenDatabase(“C:UsersReema DocumentsAccessDatabase.accdb”)        ‘SQL query to update the existing field in the table      sSQL = “Update MyTable set EmpAddress=’U.K’ where EmpNumber =5678;”      ‘ Updating the records in the existing table      DoCmd.RunSQL strSQL      DataBse.Close     End Sub  

4) Write a VBA macro to delete records present in the Ms Access database.

Following is the VBA macro to delete records present in the Ms Access database:

  ‘Delete a record from the Ms Access database table  Sub Delete_Records_DatabaseTable()         Dim DataBse As Database      Dim sSQL As String      ‘Open the Access Database      Set DataBse= OpenDatabase(“C:UsersReema DocumentsAccessDatabase.accdb”)        ‘SQL query to update the existing field in the table      stQL = “Delete from MyTable where EmpNumber=5678;”      ‘ Deleting the record from the table      DoCmd.RunSQL sSQL      DataBse.Close     End Sub  

5) Write a VBA code to export the Ms Access Table to Excel.

Following is the VBA macro to export the Ms Access Table to Excel.

  ‘Export the access table to MS Excel  Sub Export_Access_Table_Excel()            ‘Declaring the Variables      Dim sTble As String      Dim sWorkbook As String            ‘Change the target file path      strWorkbook = “E:/MyTable.xlsx”            ‘Assigning the source table name      sTble = “MyTable”            ‘Exporting the table to Excel      DoCmd.TransferSpreadsheet transfertype:=acExport, spreadsheettype:=acSpreadsheetTypeExcel12, _      tablename:=sTble, FileName:=strWorkbook, hasfieldnames:=True           End Sub  

6) Write a VBA code to change the existing table name in the Ms Access database.

Following is the VBA macro code to change the existing table name in the Ms Access Database:

  ‘Changing the name of existing table in access database  Sub Change_Existing_Table_Name()            ‘Declaring the variables      Dim stOTble As String      Dim stNTble As String      ‘Specifying the Old Table name      stOTble = “MyTable”      ‘Specifying the New Table Name      stNTble = “EmpTable”      ‘Export table to Excel      DoCmd.Rename stNTble, acTable, stOTble              End Sub  

Microsoft Word VBA Interview Questions

1) Can you open a new document in word using VBA procedure?

Below given is the VBA macro code to open a new document in word:

  ‘Creating a new word document   Sub Create_Word_Document()     Documents.Add  End Sub  

2) Write a VBA macro to quickly open a new work document from the existing word document.

Below given is the VBA macro to quickly open a new work document from the existing word document:

  ‘Creating a new word document from the existing word document  Sub Create_New_Word_Doc()      Documents.Add Template:=”E:/Word.doc”, NewTemplate:=True  End Sub  

3) Write a VBA macro to create an existing word document as a read only document.

Below given is the VBA macro to create an existing word document as a read only document:

  ‘Creating an Existing Word Document as Read Only Document  Sub Create_Work_Doc_As_ReadOnly()      Documents.Open FileName:=”E:/Word.doc”, ReadOnly:=True  End Sub  

4) Write a VBA macro to SaveAs a word document if already not saved.

Below given is the VBA macro to SaveAs a word document if already not saved:

  ‘VBA macro to SaveAs Word Document  Sub SaveAs_Word_Doc()      ActiveDocument.SaveAs (“E:/Word.doc”)  End Sub  

5) Write a VBA macro to quickly select the current line in a MS word document.

Below given is the VBA snippet to quickly select the current line in a Ms word document:

  ‘Selecting the current line in a Ms word document  Sub Select_CurrentLine_Word()      Selection.HomeKey Unit:=wrdLine      Selection.EndKey Unit:=wrdLine, Extend:=wrdExtend  End Sub  

6) Write a VBA macro to quickly select the entire Ms word document.

Below given is the VBA snippet to quickly select the entire Ms word document:

  ‘Selecting the current line in a Ms word document  Sub Select_Entire _Word_Doc()  Selection.WholeStory  End Sub  

7) Write a VBA Macro to quickly change (increase or decrease) the font size of third paragraph in a Ms word file.

Following is the VBA macro to quickly change the font size of the third paragraph of your word document file.

  ‘Changing the font size of third paragraph in word document  Sub Paragraph_Font_Size_Word ()       ActiveDocument.Paragraphs(3).Range.Font.Size = 13  End Sub  

Microsoft Outlook VBA Interview Questions

1) Write the VBA code to sent an email using Outlook?

Below given is the VBA code to send an email using Outlook:

  ‘Sending an email using Microsoft Outlook  Sub VBA_Send_Email()            ‘Declaring the objects      Dim OutApp As New Outlook.Application      Dim outMsg As MailItem          ‘Create the application objects      Set OutApp= New Outlook.Application      Set outMsg= OLApp.CreateItem(olMailItem)          ‘Fill the email fields      With outMsg          .To = “reemapanda18@gmail.com”          .Subject = “VBA Test Email”          .Body = “Hello VBA. Test Verified.”             .Send      End With           Set objMail = Nothing      Set objOL = Nothing  End Sub  

2) Can we also add an attachment to an email in Microsoft Outlook using VBA? If yes, write the VBA code.

Following is the VBA macro to quickly add an attachment to an email using Microsoft Outlook.

  ‘Inserting an attachment to an email using Microsoft Outlook  Sub Insert_Attachment_Email ()                ‘Variable Declaration      Dim oMsg As Outlook.MailItem      Dim oAttach As Outlook.Attachments            ‘Create the Objects      Set oMsg = Application.CreateItem(olMailItem)      Set oAttach = oMsg.Attachments            ‘Add the attachment from the file      oAttach.Add “E:/Data.doc”, olByValue, 1, “Data”      oMsg.Display       End Sub  

3) Write a VBA macro to add attachment and send an email simultaneously using Microsoft Outlook.

Following is the VBA snippet to add attachment and send an email simultaneously using Ms Outlook.

  ‘Add attachment and send an email using Microsoft Outlook  Sub Send_Outlook_Email_With_Attachment()                ‘Declaring the variables      Dim oMsg As Outlook.MailItem      Dim oAttach As Outlook.Attachments      ‘Create the Objects      Set oMsg = Application.CreateItem(olMailItem)      Set oAttach = oMsg.Attachments      ‘Add the attachment      oAttach.Add “E:/data.doc”, olByValue, 1, “data”            ‘Add the message fields      With outMsg          .To = “reemasharma@tutoraspire.com”          .Subject = “Test Attachment Email”          .Body = “Type your Body here.”          .Send      End With        End Sub  

4) Can we also create a Task in Ms Outlook using VBA macro?

Yes, we can create a Task in Ms Outlook using VBA macros. Following is the VBA snippet to do so.

  ‘Create a Task in Outlook  Sub Create_Task()            ‘Variable Declaration      Dim Task As TaskItem            ‘Create object      Set myTask = Application.CreateItem(ItemType:=olTaskItem)        End Sub  

Microsoft PowerPoint VBA Interview Questions

1) Can you automate a new PowerPoint Presentation using VBA macros? If yes, write the VBA code.

Yes, VBA can also automate the various PowerPoint tasks. Following is the VBA snippet to create a new PowerPoint Presentation:

  ‘Creating a new PowerPoint Presentation  Sub New_PPT_Presentation()      Presentations.Add  End Sub  

2) Write a VBA macro to add new slide in a PowerPoint Presentation.

Following is the VBA snippet to add a new slide in a PowerPoint Presentation:

  Sub Add_New_PPT_Slide_Example1()      With Presentations.Add      .Slides.Add Index:=1, Layout:=ppLayoutTitle      End With  End Sub  

‘OR

  Sub Add_New_PPT_Slide_Example2()      With Presentations.Add      .Slides.Add 1, 1      End With  End Sub  

3) Write a VBA macro create a new PowerPoint Presentation and later save it in your files.

Following is the VBA macro to create and save a PowerPoint Presentation:

  Sub Create_Save_PPT_Presentation()          ‘Variable Declaration      Dim NewPres As Presentation            ‘Creating a new Presentation      Set NewPPT = Presentations.Add                ‘Save the above Presentation in a file      NewPPT.SaveAs FileName:=”e:/VBApptPresentation.pptx”        End Sub  

4) Can you move a PPT slide from one position 1 to 4 in a PowerPoint Presentation?

Yes, using VBA macro we can move a PPT slide from one position 1 to 4 in a PowerPoint Presentation. Refer to the following macro:

  ‘Moving ppt slide from position one to four  Sub Move_PPT_Slide()      ActivePresentation.Slides(4).MoveTo topos:=2  End Sub  

5) Write a VBA code, to start the PowerPoint slide show.

Following is the VBA snippet to start the PowerPoint slide show:

  ‘Starting a PowerPoint slide show  Sub Start_PPT_SlideShow()      ActivePresentation.SlideShowSettings.Run  End Sub  

VBA Interview Questions and Answers