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.

Private Sub Workbok_Open_Example()
     'Your Statements.....
End Sub

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:\Users\Reema \Documents\AccessDatabase.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:\Users\Reema \Documents\AccessDatabase.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:\Users\Reema \Documents\AccessDatabase.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:\Users\Reema \Documents\AccessDatabase.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@javatpoint.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

We provides tutorials and interview questions of all technology like java tutorial, android, java frameworks

Contact info

G-13, 2nd Floor, Sec-3, Noida, UP, 201301, India

[email protected].

Python

AI, ML and Data Science

Java

B.Tech and MCA

Web Technology

Software Testing

Technical Interview

Java Interview

Web Interview

Database Interview

Company Interviews

All Interview

Company Interview