VBA Interview Questions and Answers with Examples, macro codes – Download Free PDF File. Top Most 100+ commonly asked Basic and Advanced VBA Interview Questions and Answers Covered for Freshers and Experienced VBA Developers.Important ,Latest, Logical, Technical, Programming, Simple and Tough questions and answers from MS Excel VBA, Access VBA, PowerPoint VBA, MS Outlook and Word VBA Questions and Answers with tests and Quizzes.
- Basic VBA Interview Questions and Answers (10+)
- Advanced VBA Interview Questions and Answers (10+)
- Top Most MS Excel VBA Interview Questions and Answers (55+)
- Top Most MS Access VBA Interview Questions and Answers (10+)
- Top Most MS PowerPoint VBA Interview Questions and Answers (10+)
- Top Most MS Word VBA Interview Questions and Answers (10+)
- Top Most MS Outlook VBA Interview Questions and Answers (10+)
- VBA Interview Questions and Answers PDF – Free Download pdf file
- Top Most Example Macro Codes – Simple & Basic VBA Interview Questions and Answers for Beginners
- Top Most Example Macro Codes – Advanced VBA Interview Questions and Answers for Experienced
100+ VBA Interview Questions and Answers Explained with proper examples by topic. FAQs are useful for all Excel VBA users to refer Excel VBA quickly. Please find the below are more than 100 VBA FAQs:Frequently Asked Interview Questions and Answers explained with examples which we have experienced.
Basic VBA Interview Questions and Answers
Here are the most commonly asked Basic VBA Questions and Answers covered from Basics of VBA Programming.
- VBA stands for Visual Basic for Applications.
- VBA is Programming language available in MS Office Tools.
- Boolean
- Byte
- Currency
- Date
- Double
- Integer
- Long
- LongLong
- LongPtr
- Object
- Single
- String
- Variant
- 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.
Read More Basic VBA Interview Questions and Answers
More Basic VBA Interview Questions
Advanced VBA Interview Questions and Answers
Here are the top most Advanced VBA Questions and Answers covered from Advanced concepts of VBA Programming.
UserForms: UserForms helps to develop GUI (Graphical User Interface) applications.
Class Modules: Class module allows to create new objecs and define methods, properties and events. Also, it will allow to enhance the existing objects.
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.
Read More Advanced VBA Interview Questions and Answers
More Advanced VBA Interview Questions
Top Most Excel VBA Interview Questions and Answers
We have covered most frequently asked Excel VBA Interview Questions and Answers, divided into different sections. Take your own time to understand the questions and answers. Please ask us if you have any further questions.
Please let us know your interview experience and share your experience. Please let us know if you want to add any questions, which we have missed here.
Application –> Workbooks –> Worksheets –> Range / Chart
Step 1: Go To Developer tab from the main ribbon of Excel window.
Step 2: Click on ‘Stop Recording’ command button to stop from the recording macro.
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 macro name which you want to delete macro and then click on ‘Delete’ command button.
Step 5: Now, It will show the confirmation dialog box. Click on Ok to delete the macro.
To get Workbook_Open() Event in Excel, please find the following steps.
1. Go To VBA Editor.
2. Click on ‘ThisWorkbook’ from the Project Explorer.
3. Now, you can see two drop down lists in the right side.
4. Select ‘Workbook’ from the first drop down list and then choose ‘Open’ from the second drop down list.
5. Now, you can see the following code.
Private Sub Workbook_Open()
‘Your Statements…..
End Sub
6. You can add the code in-between the above lines to run a macro.
7. Save and close the workbook
8. 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 workbook. Now, it will display 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.
To get Workbook_Open() Event in Excel, please find the following steps.
1. Go To VBA Editior.
2. Click on ‘ThisWorkbook’ from the Project Explorer.
3. Now, you can see two drop down lists in the right side of the VBA Editor window.
4. Select ‘Workbook’ from the first drop down list and then choose ‘Open’ from the second drop down list.
5. Now, you can see the following code.
Private Sub Workbook_Open()
‘Your Statements…..
End Sub
6. You can add the code in-between the above lines to run a macro.
7. Save and close the workbook
8. 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’.
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.
For…. Next loop, Do While…. Loop, Do until Loop, Do….Loop Until..,Do While Not…Loop, While…. Wend loop
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 with 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 added Class Module in the Project Explorer. Default Class module name will be ‘Class1’. You can change the class module name with using properties.
Create object for Workbook:
Sub Create_Object_Workbook()
Dim Wb As Workbook
Set Wb = ActiveWorkbook
MsgBox Wb.Sheets(1).Name
End Sub
Explanation: In the above example, I have created and assigned to Active Workbook to ‘Wb’ objet. And then I have used it in the next statement(MsgBox Wb.Sheets(1).Name) to display first worksheet name.
We can create and define size of an array variable in the following way.
Dim ArrayName (IndexNumber) As Datatype
Example: Dim aValue(2) As Integer
In the above statement ‘aValue’ is an array name and ‘2’ indicates an array size.
‘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”}
Before using variable, we need to know how much space the variable will occupy in memory, because different variables occupy different amount of space in memory.
We can declare the variable in the following way.
Dim VariableName as Datatype
Example:
Dim iCnt as Integer
Where iCnt represents VariableName and Integer represents Datatype.
Byte, Boolean, Integer, Long, Single, Double, Currency, Decimal, Date, Object, String, Variant and User defined data types.
For more information please find the following link.
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.
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 button to test. Now, your macro should run.
When we are working with variables, it is important to understand the Scope of a Variable. The Scope describes the accessibility or life time or visibility of a variable.
There are four levels of Scope:
• Procedure-Level Scope
• Module-Level Scope
• Project-Level Scope
• Global-Level Scope
For more information please find the following link.
Find last row in the worksheet:
Dim lastRow As Long
lastRow = ActiveSheet.Cells.SpecialCells(xlLastCell).Row
For more information please find the following link.
Find last column in the worksheet:
Dim lastColumn As Long
lastColumn = ActiveSheet.Cells.SpecialCells(xlLastCell).Column
For more information please find the following link.
We can fasten the execution of macros or VBA Procedures by following the below tips.
1. Declare the variables and avoid using ‘Variant’ Data Type.
2. Turn Off Screen Updating
3. Turn Off Automatic Calculations
4. Disable Events
5. Use With Statement
6. Use vbNullString instead of “”.
7. Release memory objects at the end of the procedure.
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
In order to stop triggers, disable the display alerts at Staring of the procedure:
Application.DisplayAlerts = False
You have to set enable display alerts before ending of the procedure:
Application.DisplayAlerts = True
1. On Error Resume Next
2. On Error Goto Err_Lbl
3. On Error Goto 0
You can find a specific file exist or not in the following two ways.
1. Using FileSystemObject:
Here is the example to check file exist 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 file exist or not using ‘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
For more information please find the following link.
Sub Save_Workbook()
Dim Wkb As Workbook
Set Wkb = Workbooks.Add
Wkb.Save
End Sub
Please find the following link for more information on saving workbook using Excel VBA.
Sub SaveAs_Workbook()
Dim Wkb As Workbook
Set Wkb = Workbooks.Add
ActiveWorkbook.SaveAs Filename:="C:Test.xlsm"
End Sub
Please find the following link for more information on changeing the existing file name.
Sub sbDelete_File()
Dim FSO
Dim sFile As String
sFile = "C:Test.xlsm"
'Set Object
Set FSO = CreateObject("Scripting.FileSystemObject")
'Check File Exists or Not
If FSO.FileExists(sFile) Then
FSO.DeleteFile sFile, True
MsgBox "Deleted The File Successfully", vbInformation, "Done!"
Else
MsgBox "Specified File Not Found", vbInformation, "Not Found!"
End If
End Sub
Note: Before deleting file from the specified location, we have to check whether file is exists or not in the specified location. In the above example we are using statement:’ FSO.FileExists(sFile)’ to check for the file.
Please find the following link for more information on deleting a file in a specified location.
Sub Copy_File()
Dim FSO
Dim sFileName As String
Dim dFileName As String
sFileName = "D:Test.xlsx" 'Source File Location Name
dFileName = "E:Test.xlsx" 'Destination File Location Name
Set FSO = CreateObject("Scripting.FileSystemObject")
FSO.CopyFile sFileName, sFileName, True
End Sub
Please find the following link for more information on copying a file from one location to another location.
Sub Move_File()
Dim sFileName As String
Dim dFileName As String
sFileName = "D:Test.xlsx" 'Source File Location Name
dFileName = "E:Test.xlsx" 'Destination File Location Name
Name sFileName As sFileName
End Sub
Read More MS Excel VBA Interview Questions and Answers
55+ MS Excel VBA Interview Questions and Answers with Examples
MS Access VBA Interview Questions and Answers with Examples
Here are the most commonly asked MS Access VBA Questions and Answers covered from Basics of VBA Programming.
'Create table in the required database using VBA
Sub sbCreate_Table()
Dim DBase As Database
'
'Open Databse
Set DBase = OpenDatabase("C:UsersPNRaoDocumentsMyDatabase.accdb")
' Create a table with three fields.
DBase.Execute "CREATE TABLE MyTable " _
& "(EName CHAR, ENumber INT, ELocation CHAR);"
DBase.Close
End Sub
'Delete table from a specific database using VBA
Sub sbDelete_Table()
Dim DBase As Database
'
'Open Databse
Set DBase = OpenDatabase("C:UsersPNRaoDocumentsMyDatabase.accdb")
' Create a table with three fields.
DBase.Execute "DROP TABLE MyTable;"
DBase.Close
End Sub
'Add or Insert records to the access database table using VBA
Sub sbAdd_Records_Table()
Dim DBase As Database
Dim strSQL As String
'
'Open Databse
Set DBase = OpenDatabase("C:UsersPNRaoDocumentsMyDatabase.accdb")
strSQL = "INSERT INTO MyTable values('John2',12345,'U.S');"
' Adding records the table
DoCmd.RunSQL strSQL
DBase.Close
End Sub
'Update records in the access database table using VBA
Sub sbUpdate_Records_Table()
Dim DBase As Database
Dim strSQL As String
'
'Open Databse
Set DBase = OpenDatabase("C:UsersPNRaoDocumentsMyDatabase.accdb")
strSQL = "Update MyTable set ELocation='U.K' where ENumber=12345;"
' Updating records the table
DoCmd.RunSQL strSQL
DBase.Close
End Sub
'Delete records in the access database table using VBA
Sub sbDelete_Records_Table()
Dim DBase As Database
Dim strSQL As String
'
'Open Databse
Set DBase = OpenDatabase("C:UsersPNRaoDocumentsMyDatabase.accdb")
strSQL = "Delete from MyTable where ENumber=12345;"
' Deleting records the table
DoCmd.RunSQL strSQL
DBase.Close
End Sub
10+ MS Access VBA Interview Questions and Answers
More MS Access VBA Interview Questions and Answers with Examples
MS PowerPoint VBA Interview Questions and Answers with Examples
Here are the most commonly asked MS PowerPoint VBA Questions and Answers covered from Basics of VBA Programming.
Sub Create_Presentation()
Presentations.Add
End Sub
Sub Create_Presentation_WithSlide_Ex1()
With Presentations.Add
.Slides.Add Index:=1, Layout:=ppLayoutTitle
End With
End Sub
'Or
Sub Create_Presentation_WithSlide_Ex2()
With Presentations.Add
.Slides.Add 1, 1
End With
End Sub
'Create new PowerPoint Presentation and add slide
Sub Create_Presentation_WithSlide_Ex2()
With Presentations.Add
.Slides.Add 1, 1
End With
End Sub
Sub Create_Save_Presentation()
'Variable Declaration
Dim NewPres As Presentation
'Create new Presentation
Set NewPres = Presentations.Add
'Your statements do something
'Save Presentation in a folder
NewPres.SaveAs FileName:="d:/TestPresentation.pptx"
End Sub
Example 1:
Sub DeleteSlide_Presentation_Ex1()
ActivePresentation.Slides(1).Delete
End Sub
Example 2:
'Delete 3rd slide in a presentation
Sub DeleteSlide_Presentation_Ex2()
ActivePresentation.Slides(3).Delete
End Sub
Read More MS Powerpoint VBA Interview Questions and Answers
10+ MS PowerPoint VBA Interview Questions and Answers with Examples
MS Word VBA Interview Questions and Answers with Examples
Here are the most commonly asked MS Word VBA Questions and Answers covered from Basics of VBA Programming.
‘Create New Word Document
Sub Create_Documnent()
Documents.Add
End Sub
'Create new word document based on existing word document
Sub Create_Doc_ReadOnly()
Documents.Add Template:="D:/Test.doc", NewTemplate:=True
End Sub
'Open a Existing Word Document (Read Only Document)
Sub Open_Doc_ReadOnly()
Documents.Open FileName:="D:/Test.doc", ReadOnly:=True
End Sub
'Save Word Document if already not saved
Sub Save_Documnent()
If ActiveDocument.Saved = False Then
ActiveDocument.Save
End If
End Sub
'SaveAs Word Document if already not saved
Sub SaveAs_Documnent()
ActiveDocument.SaveAs ("d:/Test.doc")
End Sub
Read More MS Word VBA Interview Questions and Answers
10+ MS Word VBA Interview Questions and Answers with Examples
MS Outlook VBA Interview Questions and Answers with Examples
Here are the most commonly asked MS Outlook VBA Questions and Answers covered from Basics of VBA Programming.
'Create a new message using Outlook
Sub Create_Message()
'Variable Declaration
Dim OLApp As New Outlook.Application
Dim oMessage As MailItem
'Create objects
Set OLApp = New Outlook.Application
Set oMessage = OLApp.CreateItem(olMailItem)
With oMessage
.To = "Person_Name@domain.com"
.Subject = "Test Email"
.Body = "This is a test message."
.Display
End With
Set objMail = Nothing
Set objOL = Nothing
End Sub
'Send a new message using Outlook
Sub Send_Message()
'Variable Declaration
Dim OLApp As New Outlook.Application
Dim oMessage As MailItem
'Create objects
Set OLApp = New Outlook.Application
Set oMessage = OLApp.CreateItem(olMailItem)
With oMessage
.To = "emailID@domainname.com"
.Subject = "Test Email"
.Body = "This is a test message."
.Send
End With
Set objMail = Nothing
Set objOL = Nothing
End Sub
'Add attachment to a message using Outlook
Sub Add_Attachment_Message()
'Variable Declaration
Dim oMessage As Outlook.MailItem
Dim oAttachment As Outlook.Attachments
'Create Objects
Set oMessage = Application.CreateItem(olMailItem)
Set oAttachment = oMessage.Attachments
'Add attachment
oAttachment.Add "D:/Test.doc", olByValue, 1, "Test"
oMessage.Display
End Sub
'Add attachment and send a message using Outlook
Sub Send_Message_With_Attachment()
'Variable Declaration
Dim oMessage As Outlook.MailItem
Dim oAttachment As Outlook.Attachments
'Create Objects
Set oMessage = Application.CreateItem(olMailItem)
Set oAttachment = oMessage.Attachments
'Add attachment
oAttachment.Add "D:/Test.doc", olByValue, 1, "Test"
'Add message details
With oMessage
.To = "emailID@domainname.com"
.Subject = "Test Email"
.Body = "This is a test message."
.Send
End With
End Sub
Create a Task
Sub Create_Task()
'Variable Declaration
Dim Task As TaskItem
'Create object
Set myTask = Application.CreateItem(ItemType:=olTaskItem)
End Sub
Read More MS Outlook VBA Interview Questions and Answers
10+ MS Outlook VBA Interview Questions and Answers with Examples
VBA Interview Questions and Answers PDF – Free Download pdf file
You can download the free VBA Interview Questions and Answers PDF with Examples Explained.
VBA Interview Questions and Answers PDF – Free Download
Simple and Basic VBA Interview Questions and Answers for Beginners with Examples
Below are the 15+ Basic VBA Questions with examples for VBA beginners. Please refer these example VBA Basic Examples if you are a fresher or just beginner. This will be helpful to answer the basic VBA Questions.
Basic VBA Interview Questions and Answers with Examples
Advanced VBA Interview Questions and Answers for Experienced
Below are the 100+ examples on Advanced VBA Problems with solutions. Please refer these Advanced VBA Examples if you are attending for a experienced job/position profile requirement.
Hi Team,
Awesome! Thanks for the most useful faqs.
Regards,
Raji
Wow!!!!… Useful Questions and Answers..
Thanks for giving this..
Thanks Suresh!-PNRao!
Very useful. Thanks for helping us!!
Good Work
Good work …looking some tips for class module also
Thanks for providing such a valuable information from you!!!
!!!!!………This will truly helpfull for all of us… !!!!!
Very helpful thanks
there are several spelling mistakes in your content please update that part and make your website easier to read and understand
rest all the content was good and helpful;
Hi all,
I have a userform on a worksheet. Now, I open anothe workbook, the userform still stays on top of all the workbooks. However, if I open a new workbook(new instance of excel), then it does not show. But that would help me for nothing.
Can anybody help me in this. I need the userform to be a part of the excel which I open. not for all workbooks.
Few comments before I complete: This was working as intended. All of a sudden, I messed up with the code and it burnt out.
Secondly, the ShowModel of userform is set to False as I need to hide the workbook when userform opens. Hence, I cannot set that to true.
Any suggestions are welcome. Please help
yes its interview saver questions. Thanks for the questions…………
Hi,
I want to ask a answer. When I open VBE editor then show me a message.
Is that possible or not.
If it is possible. Then please tell me, how can I do.
Thanks
Ram
Hello,
I have a problem creating a macro, while making my steps into the excel I need to enter VBA code to insert this little thing that will help me recognize my columns and rows :
‘Determine last row original export
Dim LastRow As Long
LastRow = Cells(Rows.Count, “A”).End(xlUp).Row
Usually I start my macro using record macro, but now I want to add manually the above statement and then to go back recording my macro in excel.
When I due that It does not let me continue it just finishes the macro with end sub and that it.
Does anybody know how to manually add lines after recording the macro in VBA the continue with the recording?
Thanks
Very crisp material !
Really useful article. One thing though: byVal is the default mode of passing arguments into functions, not byRef.
My one question.
I have to create a macro.
I have to select A1 and automatically copy A2 to F2.
And a1 highlight yellow colour. And a2 to f2 hightlight yello colour.
Please suggest this moment.
I have two sheets, sheet1 A2, A3, A4, A5 cells data copy find in sheet2 if matching copy next cell data past into the sheet1 B2,B3,B4,B5 through VAB.
Thank you a lot Sir !
It’s really very very usefull.
Sir please upload all other VBA related tips and On data challenges
So we can improve our VBA skill more.
Sure, thanks for the feedback and comments. We will continue posting useful VBA automation macros
this is very helpful us thanks for sharing it
Thanks a lot for your support!
Nice vba information provided
Very nice and useful VBA FAQs
Thanks for sharing it
This pdf file didn’t open..Can you help how to download this pdf file ?