100+ VBA Interview Questions and Answers with Examples

• Basics
• Advanced
• Arrays
• Excel VBA
• UserForms
• Scenario
• Coding round
• Find the bug
• Access
• PowerPoint
• Word
• Outlook
• MCQs
• Practice
• VBA vs Python
• Preparation
• HR round
• Cheat sheet
• Free PDF
• FAQ

VBA interview questions cover five areas: the language fundamentals, the Excel object model, error handling, performance tuning, and the host application you will automate — Excel, Access, PowerPoint, Word or Outlook. This page collects 138 real VBA interview questions and answers across 20 sections, numbered straight through from 1 to 138, each with a working macro you can paste into the VBA editor and run.

Every answer is short enough to say out loud in an interview. Use the contents list to jump to your section, or work through the page from the top if you are preparing from scratch.

Basic VBA interview questions and answers

These are the fundamentals every VBA interview opens with, whether you are a fresher or moving across from another language.

1. What is VBA and what is the full form of VBA?

VBA stands for Visual Basic for Applications. It is an event-driven programming language built into Microsoft Office applications. Unlike Visual Basic 6, VBA cannot produce a standalone .exe — it always runs inside a host application and automates that host through its object model.

2. What is a data type and why does it matter?

A data type tells VBA what kind of value a variable holds and how much memory to allocate. Declaring types correctly catches errors at compile time, uses less memory and runs faster than leaving everything as Variant.

Dim iCount As Integer   ' 2 bytes
Dim lRow As Long        ' 4 bytes — use for row numbers
Dim sName As String
Dim bFlag As Boolean

3. Name the data types available in VBA

Byte, Boolean, Integer, Long, LongLong, LongPtr, Single, Double, Currency, Decimal, Date, String, Object, Variant, plus user-defined types created with Type ... End Type.

4. What is the Variant data type?

Variant is the default data type when no type is declared. It holds any kind of value — numbers, text, dates, arrays, objects — and decides its subtype at run time. That flexibility costs memory (16 bytes minimum) and speed, so use it deliberately: for values whose type is genuinely unknown, for arrays returned by Range.Value, or when a function must accept mixed input.

5. What is the scope of a variable in VBA?

Scope defines where a variable can be seen and how long it lives. There are three practical levels:

• Procedure level — declared with Dim inside a Sub or Function; visible only there, destroyed when it ends.
• Module level — declared with Dim or Private at the top of a module; visible throughout that module.
• Project level — declared with Public at the top of a standard module; visible across the whole project.

Static is the follow-up question: a Static variable keeps its value between calls to the same procedure.

Full guide to variable scope in VBA

6. What is the difference between a Sub and a Function?

A Sub performs actions and returns nothing. A Function returns a value to the caller and can be used directly in a worksheet cell as a user-defined function.

Sub ShowMessage()
    MsgBox "This returns nothing."
End Sub

Function AddNumbers(a As Double, b As Double) As Double
    AddNumbers = a + b     ' assign to the function name to return
End Function

7. What is a macro?

A macro is a stored set of VBA instructions saved in a module inside a workbook or document. Running it repeats a task exactly, every time, which is why macros are used for daily, weekly and monthly reporting work. Macros can be recorded or written directly in the VBA editor; recorded code is a useful starting point but is usually rewritten for speed and reliability.

8. What is the shortcut to open the VBA editor?

Alt + F11 opens the Visual Basic Editor from any Office application. Alt + F8 opens the Macro dialog to run an existing macro, and F5 runs the current procedure from inside the editor.

9. What are the different looping statements in VBA?

For...Next, For Each...Next, Do While...Loop, Do Until...Loop, Do...Loop While, Do...Loop Until and the legacy While...Wend.

The distinction interviewers listen for: Do While tests the condition before the first iteration, so the body may never run; Do...Loop While tests after, so the body always runs at least once. For Each is the right choice for iterating a collection of objects.

10. What is an array and how do you declare one?

An array is a single variable holding multiple values of the same type, accessed by index. VBA arrays are zero-based by default, so Dim aValue(2) creates three elements: 0, 1 and 2.

' Fixed-size array
Dim aValue(2) As String
aValue(0) = "First"
aValue(1) = "Second"
aValue(2) = "Third"

' Quick way using the Array function (needs a Variant)
Dim vList As Variant
vList = Array("First", "Second", "Third")

' Dynamic array — size decided at run time
Dim aData() As Long
ReDim aData(1 To 100)
ReDim Preserve aData(1 To 200)   ' Preserve keeps existing values

11. What is Option Explicit and why should you always use it?

Option Explicit at the top of a module forces every variable to be declared before use. Without it, a typo such as totl instead of total silently creates a new empty Variant, producing wrong results with no error. Turn it on permanently in the VBA editor via Tools > Options > Require Variable Declaration.

12. How do you add a comment in VBA?

Prefix the line with an apostrophe (') or the Rem keyword. Comments are ignored at run time. Use the Comment Block button on the Edit toolbar to comment several lines at once.

13. How do you run a macro in Excel?

Five ways, and interviewers like you to know more than one: press Alt + F8 and pick it from the Macro dialog; press F5 inside the VBA editor with the cursor in the procedure; assign it to a button or shape; assign a keyboard shortcut in the Macro Options dialog; or let an event such as Workbook_Open run it automatically.

14. What are the debugging tools in the VBA editor?

• Breakpoints (F9) — pause execution on a chosen line.
• Step Into (F8) — run one line at a time and watch what happens.
• Immediate window (Ctrl + G) — print values with Debug.Print or run a statement on the spot.
• Locals window — every variable in scope with its current value.
• Watch window — track one expression, or break when it changes.
• Debug > Compile VBAProject — catches syntax and type errors before you run anything.

Debug.Print "lLastRow = " & lLastRow    ' prints to the Immediate window
Debug.Assert lLastRow > 1               ' breaks if the condition is False

15. What is the difference between MsgBox and InputBox?

MsgBox displays a message and returns which button the user clicked. InputBox displays a prompt with a text field and returns what the user typed, or an empty string if they cancel.

Dim vAnswer As VbMsgBoxResult
vAnswer = MsgBox("Delete all rows?", vbYesNo + vbQuestion, "Confirm")
If vAnswer = vbNo Then Exit Sub

Dim sName As String
sName = InputBox("Enter the report name:", "Report")
If Len(sName) = 0 Then Exit Sub          ' user cancelled

Excel also has Application.InputBox, which adds a Type argument and can accept a range the user selects with the mouse.

Read more basic VBA interview questions

More basic VBA interview questions and answers

Advanced VBA interview questions and answers

These come up for experienced VBA developer roles, where the interviewer wants to know how you structure code, not just whether you can write it.

16. What types of modules are available in the VBA editor?

• Standard module — holds ordinary Subs and Functions.
• UserForm — a form plus the code behind its controls, used to build a GUI.
• Class module — defines a new object with its own properties, methods and events.
• Document modules — ThisWorkbook and each Sheet in Excel; built-in class modules where workbook and worksheet events live.

17. What is the difference between ByVal and ByRef?

The single most frequently asked advanced VBA question.

• ByRef passes the memory address of the argument. Changes made inside the procedure are visible to the caller. ByRef is the default in VBA.
• ByVal passes a copy of the value. Changes inside the procedure are discarded when it ends.

Sub TestPassing()
    Dim x As Long
    x = 10
    ChangeByVal x
    Debug.Print x        ' 10 — unchanged
    ChangeByRef x
    Debug.Print x        ' 99 — modified by the callee
End Sub

Sub ChangeByVal(ByVal n As Long)
    n = 99
End Sub

Sub ChangeByRef(ByRef n As Long)
    n = 99
End Sub

18. What is a class module and when would you use one?

A class module defines a custom object — a blueprint with Property Get/Let/Set procedures, methods and events. Use one when you are modelling a real thing with both data and behaviour (an Employee, an Invoice, a Logger), when you need many instances of the same structure, or when you want to wrap application events with WithEvents.

' In a class module named clsEmployee
Private mName As String

Public Property Get Name() As String
    Name = mName
End Property

Public Property Let Name(ByVal sValue As String)
    mName = sValue
End Property

Public Function Greeting() As String
    Greeting = "Hello, " & mName
End Function

' In a standard module
Sub UseClass()
    Dim oEmp As clsEmployee
    Set oEmp = New clsEmployee
    oEmp.Name = "Priya"
    MsgBox oEmp.Greeting
End Sub

19. What is the difference between early binding and late binding?

Early binding sets a reference in Tools > References and declares the object by its specific type (Dim oOL As Outlook.Application). You get IntelliSense, compile-time checking and faster execution, but the file breaks if the target machine has a different library version.

Late binding declares the variable as Object and creates it with CreateObject. No reference is needed, so the code is portable across Office versions, at the cost of IntelliSense and a small speed penalty.

' Late binding — no reference required
Dim oOutlook As Object
Set oOutlook = CreateObject("Outlook.Application")

Ship production code with late binding when it will run on machines you do not control.

20. What are the error handling techniques in VBA?

• On Error GoTo Label — jump to a handler when an error occurs. Use this in production code.
• On Error Resume Next — ignore the error and continue on the next line. Use only for a specific expected failure, then restore normal handling immediately.
• On Error GoTo 0 — turn off the active handler and reset the Err object.

Sub ProcessData()
    On Error GoTo ErrHandler

    Application.ScreenUpdating = False
    ' ... your code ...

CleanExit:
    Application.ScreenUpdating = True
    Exit Sub

ErrHandler:
    MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical
    Resume CleanExit
End Sub

Mention Err.Number, Err.Description, Err.Raise and Resume / Resume Next / Resume Label. Interviewers look for the cleanup path that always restores ScreenUpdating and Calculation.

21. What is the difference between a collection, a dictionary and an array?

Dim oDict As Object
Set oDict = CreateObject("Scripting.Dictionary")
oDict("Mumbai") = 400001
If oDict.Exists("Mumbai") Then MsgBox oDict("Mumbai")

22. What are the built-in class modules in Excel VBA?

ThisWorkbook and each worksheet module are built-in class modules. They expose event procedures — Workbook_Open, Workbook_BeforeSave, Worksheet_Change, Worksheet_SelectionChange — that fire automatically when the user acts on the file.

23. How do you call a Windows API function from VBA?

Declare it at the top of a standard module. Use PtrSafe and LongPtr so the code works in both 32-bit and 64-bit Office.

#If VBA7 Then
    Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As LongPtr)
#Else
    Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
#End If

The conditional compilation block is the detail that separates a prepared candidate from a rehearsed one.

24. What is the difference between a UDF and a Sub in Excel?

A user-defined function is a Function in a standard module that can be typed into a cell as =AddNumbers(A1,B1). UDFs are restricted: they cannot change other cells’ values, formatting or workbook structure, only return a value to the calling cell. A Sub has no such restriction but cannot be used in a formula.

25. How do you protect VBA code in a workbook?

In the VBA editor go to Tools > VBAProject Properties > Protection, tick “Lock project for viewing” and set a password.

26. What are Optional arguments and ParamArray?

Optional arguments may be omitted by the caller; ParamArray accepts any number of arguments as an array. Both must come last in the parameter list, and they cannot be combined.

Function BuildPath(sFolder As String, Optional sFile As String = "") As String
    BuildPath = sFolder
    If Len(sFile) > 0 Then BuildPath = sFolder & "\" & sFile
End Function

Function SumAll(ParamArray Numbers() As Variant) As Double
    Dim i As Long
    For i = LBound(Numbers) To UBound(Numbers)
        SumAll = SumAll + Numbers(i)
    Next i
End Function

Use IsMissing to test an omitted Optional Variant; typed optionals cannot be missing, they take their default value instead.

27. What is a user-defined type in VBA?

A Type ... End Type block groups related fields into one structure, declared at the top of a module. It is lighter than a class module when you need data but no behaviour.

Public Type EmployeeRecord
    ID       As Long
    Name     As String
    JoinDate As Date
End Type

Sub UseType()
    Dim uEmp As EmployeeRecord

    uEmp.ID = 101
    uEmp.Name = "Priya"
    uEmp.JoinDate = DateSerial(2024, 4, 1)

    Debug.Print uEmp.Name & " joined " & Format(uEmp.JoinDate, "dd-mmm-yyyy")
End Sub

28. What is WithEvents used for?

WithEvents declares an object variable that can respond to that object’s events from inside a class module. It is how you trap application-level events, such as detecting a change on any open workbook rather than one specific sheet.

' In a class module named clsAppEvents
Private WithEvents mApp As Application

Private Sub Class_Initialize()
    Set mApp = Application
End Sub

Private Sub mApp_WorkbookOpen(ByVal Wb As Workbook)
    Debug.Print Wb.Name & " opened at " & Now
End Sub

Note that WithEvents is only valid in class modules, including ThisWorkbook and sheet modules, never in a standard module.

29. What is recursion, and when would you use it in VBA?

A recursive procedure calls itself, with a condition that eventually stops it. In VBA the classic use is walking a folder tree, since you cannot know the depth in advance.

Sub ListFiles(sPath As String)
    Dim oFSO As Object, oFolder As Object
    Dim oFile As Object, oSub As Object

    Set oFSO = CreateObject("Scripting.FileSystemObject")
    Set oFolder = oFSO.GetFolder(sPath)

    For Each oFile In oFolder.Files
        Debug.Print oFile.Path
    Next oFile

    For Each oSub In oFolder.SubFolders
        ListFiles oSub.Path              ' the recursive call
    Next oSub
End Sub

Read more advanced VBA interview questions

More advanced VBA interview questions and answers

Arrays, collections and dictionaries: VBA interview questions

Anything beyond a fresher role tests whether you can hold data in memory instead of reading the sheet cell by cell. This is also where most performance questions end up.

30. How do you read a range into an array and write it back?

The single most valuable technique in Excel VBA. A range assigned to a Variant becomes a two-dimensional, one-based array.

Sub RangeToArray()
    Dim vData As Variant
    Dim i As Long

    vData = ThisWorkbook.Worksheets("Data").Range("A1:C5000").Value

    For i = 1 To UBound(vData, 1)
        vData(i, 3) = vData(i, 1) * vData(i, 2)
    Next i

    ThisWorkbook.Worksheets("Data").Range("A1:C5000").Value = vData
End Sub

31. What do LBound and UBound do?

They return the lowest and highest index of an array dimension. Use them instead of hard-coded sizes so the code survives a change in the data.

Debug.Print LBound(vData, 1), UBound(vData, 1)   ' rows
Debug.Print LBound(vData, 2), UBound(vData, 2)   ' columns

Dim lRows As Long
lRows = UBound(vData, 1) - LBound(vData, 1) + 1

A range-sourced array is always 1-based, while Array() and Split() return 0-based arrays. Mixing the two up is a common source of off-by-one errors.

32. How do you check whether an array has been initialised?

Calling UBound on an empty dynamic array raises error 9, so test first.

Function IsArrayAllocated(vArr As Variant) As Boolean
    On Error Resume Next
    IsArrayAllocated = IsArray(vArr) And Not IsError(LBound(vArr, 1)) _
                       And LBound(vArr, 1) <= UBound(vArr, 1)
    On Error GoTo 0
End Function

33. How do you use Split and Join?

Dim vParts As Variant
vParts = Split("Mumbai,Delhi,Chennai", ",")

Debug.Print vParts(0)                 ' Mumbai — zero-based
Debug.Print UBound(vParts)            ' 2
Debug.Print Join(vParts, " | ")       ' Mumbai | Delhi | Chennai

Split is the usual answer to “how would you parse a CSV line” or “how do you get the file name from a full path”.

34. How do you sort an array in VBA?

VBA has no built-in sort, which is exactly why it is asked. A bubble sort is acceptable for small arrays and shows you can write an algorithm.

Sub BubbleSort(vArr As Variant)
    Dim i As Long, j As Long, vTemp As Variant

    For i = LBound(vArr) To UBound(vArr) - 1
        For j = i + 1 To UBound(vArr)
            If vArr(i) > vArr(j) Then
                vTemp = vArr(i)
                vArr(i) = vArr(j)
                vArr(j) = vTemp
            End If
        Next j
    Next i
End Sub

Add that for large data you would sort the range with Range.Sort and let Excel do it, or load into a System.Collections.ArrayList where .NET is available.

35. How do you pass an array to a procedure and return one?

Function DoubleValues(vIn As Variant) As Variant
    Dim i As Long
    For i = LBound(vIn) To UBound(vIn)
        vIn(i) = vIn(i) * 2
    Next i
    DoubleValues = vIn
End Function

Sub UseIt()
    Dim vNums As Variant
    vNums = Array(1, 2, 3)
    vNums = DoubleValues(vNums)
    Debug.Print Join(vNums, ", ")      ' 2, 4, 6
End Sub

Arrays are always passed ByRef in VBA; you cannot pass a fixed-size array ByVal. A function’s return type must be Variant to return an array.

36. When would you use a Dictionary instead of an array?

Whenever the question is “have I seen this value before” or “what is the total for this key”. A Dictionary lookup is effectively instant, where scanning an array is a loop every time. It is the standard answer for de-duplicating, grouping and building a unique list.

Sub TotalByRegion()
    Dim oDict As Object, vData As Variant, i As Long

    Set oDict = CreateObject("Scripting.Dictionary")
    vData = Range("A2:B1000").Value

    For i = 1 To UBound(vData, 1)
        If Len(vData(i, 1)) > 0 Then
            oDict(vData(i, 1)) = oDict(vData(i, 1)) + vData(i, 2)
        End If
    Next i

    Dim vKey As Variant
    For Each vKey In oDict.Keys
        Debug.Print vKey, oDict(vKey)
    Next vKey
End Sub

Excel VBA interview questions and answers

Excel is where most VBA roles live, so expect the bulk of the interview here: the object model, finding the last row, speed, file handling and events.

37. What is the Excel object model?

Excel exposes a hierarchy of objects, each containing the next:

Application --> Workbooks --> Worksheets --> Range / Chart / ListObject / PivotTable

You navigate down the hierarchy to reach what you want, for example Application.Workbooks("Sales.xlsx").Worksheets("Jan").Range("A1"). Understanding this is what lets you write code without the macro recorder.

38. What is the difference between ActiveWorkbook and ThisWorkbook?

ThisWorkbook always refers to the workbook containing the running code. ActiveWorkbook refers to whichever workbook currently has focus, which may change while the macro runs. Confusing the two is one of the most common sources of bugs in recorded code. The same distinction applies to ActiveSheet versus an explicit ThisWorkbook.Worksheets("Data") reference.

39. How do you find the last used row in a worksheet?

The reliable method walks up from the bottom of the column.

Dim lLastRow As Long
lLastRow = ThisWorkbook.Worksheets("Data").Cells(Rows.Count, 1).End(xlUp).Row

Alternatives, and their catches:

• Cells.SpecialCells(xlLastCell).Row — includes formatted but empty cells, so it often overstates the real last row.
• UsedRange — same problem, and it does not always reset after rows are deleted.
• Range("A" & Rows.Count).End(xlUp).Row — fine, but fails if column A has gaps and data extends further in another column.

Find the last used row with data

40. How do you find the last used column in a worksheet?

Dim lLastCol As Long
lLastCol = ThisWorkbook.Worksheets("Data").Cells(1, Columns.Count).End(xlToLeft).Column

Find the last column with data

41. How do you run a macro automatically when a workbook opens?

Use the Workbook_Open event in the ThisWorkbook module. In the VBA editor double-click ThisWorkbook, choose Workbook in the left drop-down and Open in the right one.

Private Sub Workbook_Open()
    MsgBox "Workbook opened successfully.", vbInformation
End Sub

A procedure named Auto_Open in a standard module does the same job and exists for backward compatibility. The difference worth mentioning: Workbook_Open fires when the file is opened programmatically too, Auto_Open does not.

42. How do you show a UserForm every time the workbook opens?

Private Sub Workbook_Open()
    frmMain.Show          ' frmMain is the UserForm name
End Sub

.Show defaults to modal, which blocks the worksheet until the form closes. Use frmMain.Show vbModeless if the user must still interact with the sheet.

43. What are the main workbook and worksheet events?

• Workbook — Open, BeforeClose, BeforeSave, BeforePrint, NewSheet, SheetChange.
• Worksheet — Change, SelectionChange, BeforeDoubleClick, BeforeRightClick, Calculate, Activate.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Me.Range("A1:A100")) Is Nothing Then Exit Sub
    Application.EnableEvents = False
    Target.Offset(0, 1).Value = Now
    Application.EnableEvents = True
End Sub

The EnableEvents = False line matters: without it, writing to a cell from inside Worksheet_Change re-triggers the event and loops.

44. How do you speed up a slow VBA macro?

A favourite question, because the answer reveals real project experience.

Sub FastMacro()
    Dim lCalc As XlCalculation
    lCalc = Application.Calculation

    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Application.EnableEvents = False
    Application.DisplayStatusBar = False

    ' ... your code ...

    Application.DisplayStatusBar = True
    Application.EnableEvents = True
    Application.Calculation = lCalc
    Application.ScreenUpdating = True
End Sub

Beyond those switches:

1. Read a range into a Variant array, process it in memory, write it back in one go. Usually the single biggest win.
2. Avoid Select and Activate; work with object references directly.
3. Declare specific data types instead of Variant.
4. Use With ... End With to avoid repeated object resolution.
5. Set object variables to Nothing when finished.

' Array round-trip — orders of magnitude faster than looping cells
Dim vData As Variant, i As Long

vData = Range("A1:A10000").Value

For i = 1 To UBound(vData, 1)
    vData(i, 1) = vData(i, 1) * 1.1
Next i

Range("A1:A10000").Value = vData

45. How do you turn off screen updating and display alerts?

Application.ScreenUpdating = False   ' stops screen flicker
Application.DisplayAlerts = False    ' suppresses confirmation prompts

46. What is the difference between Range and Cells?

Range takes an address string or named range: Range("A1:C10"). Cells takes numeric row and column arguments: Cells(1, 3). Because Cells is numeric it works naturally inside loops, and the two combine well:

Range(Cells(1, 1), Cells(lLastRow, 5)).Copy

47. How do you create an object variable for a workbook or worksheet?

Sub UseObjectVariables()
    Dim wb As Workbook
    Dim ws As Worksheet

    Set wb = ThisWorkbook
    Set ws = wb.Worksheets("Data")

    ws.Range("A1").Value = "Assigned via object variable"

    Set ws = Nothing
    Set wb = Nothing
End Sub

Set is required for object variables; plain assignment is only for values.

48. How do you check whether a file exists?

' Method 1 — Dir function, no reference needed
Sub CheckFileWithDir()
    Dim sFile As String
    sFile = "C:\Test\Workbook.xlsx"

    If Len(Dir(sFile)) > 0 Then
        MsgBox "File exists."
    Else
        MsgBox "File does not exist."
    End If
End Sub

' Method 2 — FileSystemObject, also works for folders and UNC paths
Sub CheckFileWithFSO()
    Dim oFSO As Object
    Set oFSO = CreateObject("Scripting.FileSystemObject")

    If oFSO.FileExists("C:\Test\Workbook.xlsx") Then
        MsgBox "File exists."
    Else
        MsgBox "File does not exist."
    End If

    Set oFSO = Nothing
End Sub

Check whether a file exists in a folder

49. How do you check whether a workbook is already open?

Function IsWorkbookOpen(sName As String) As Boolean
    Dim wb As Workbook

    On Error Resume Next
    Set wb = Workbooks(sName)
    On Error GoTo 0

    IsWorkbookOpen = Not wb Is Nothing
End Function

Note the tight scope of On Error Resume Next: two lines, then normal handling is restored.

50. How do you save a workbook using VBA?

Sub SaveWorkbook()
    ThisWorkbook.Save
End Sub

Sub SaveWorkbookAs()
    Application.DisplayAlerts = False
    ThisWorkbook.SaveAs Filename:="C:\Test\Report.xlsm", _
                        FileFormat:=xlOpenXMLWorkbookMacroEnabled
    Application.DisplayAlerts = True
End Sub

The FileFormat argument matters: saving a macro-enabled workbook without it strips the VBA project.

Save a workbook to a specific folder

51. How do you copy, move and delete a file?

Sub FileOperations()
    Dim oFSO As Object
    Dim sSource As String, sDest As String

    sSource = "D:\Test.xlsx"
    sDest = "E:\Test.xlsx"

    Set oFSO = CreateObject("Scripting.FileSystemObject")

    ' Copy — the final True overwrites an existing destination file
    oFSO.CopyFile sSource, sDest, True

    ' Move
    oFSO.MoveFile sSource, "E:\Archive\Test.xlsx"

    ' Delete, but only after checking
    If oFSO.FileExists(sDest) Then oFSO.DeleteFile sDest, True

    Set oFSO = Nothing
End Sub

The native equivalents are FileCopy source, destination, Name source As destination for moving or renaming, and Kill path for deleting.

Copy files from one folder to another

52. How do you loop through all worksheets in a workbook?

Sub LoopSheets()
    Dim ws As Worksheet

    For Each ws In ThisWorkbook.Worksheets
        Debug.Print ws.Name & " — " & ws.UsedRange.Address
    Next ws
End Sub

If you plan to delete sheets inside the loop, iterate backwards with For i = Sheets.Count To 1 Step -1, because deleting during a For Each skips items.

53. How do you loop through all files in a folder?

Sub LoopFilesInFolder()
    Dim sPath As String, sFile As String

    sPath = "C:\Reports\"
    sFile = Dir(sPath & "*.xlsx")

    Do While Len(sFile) > 0
        Debug.Print sFile
        sFile = Dir                 ' Dir with no argument returns the next match
    Loop
End Sub

54. What are the different UserForm and ActiveX controls?

Form controls (Developer tab, worksheet only, lightweight): Button, Combo Box, Check Box, Spin Button, List Box, Option Button, Group Box, Label, Scroll Bar.

ActiveX controls (worksheet or UserForm, full property and event support): Command Button, Text Box, Combo Box, List Box, Check Box, Option Button, Toggle Button, Spin Button, Scroll Bar, Label, Image.

Form controls are assigned a macro; ActiveX controls have their own event procedures such as CommandButton1_Click.

55. How do you assign a macro to a button?

1. Enable the Developer tab File > Options > Customize Ribbon, then tick Developer.
2. Insert a button Developer > Insert > Form Controls > Button.
3. Draw it on the sheet The Assign Macro dialog opens automatically as soon as you release the mouse.
4. Select the macro Pick the procedure name from the list and click OK.
5. Change it later Right-click the button and choose Assign Macro.

56. How do you delete a macro from a workbook?

Press Alt + F8, select the macro name, click Delete and confirm. To remove a whole module, right-click it in the Project Explorer and choose Remove Module. To strip all VBA from a file, save it as .xlsx — that format cannot store a VBA project.

57. How do you stop recording a macro?

Click Stop Recording on the Developer tab, or the small square button on the status bar at the bottom left of the Excel window.

58. What is the difference between .Value, .Value2, .Text and .Formula?

• .Value — the cell value, with Date and Currency returned as those types.
• .Value2 — the underlying value with no Date or Currency conversion; dates come back as serial numbers. Fastest of the four.
• .Text — what is displayed on screen, formatting included. Returns #### if the column is too narrow, so never use it for calculations.
• .Formula — the formula string, for example =SUM(A1:A10). Use .FormulaR1C1 for relative-style formulas.

59. How do you remove duplicates from a range?

Sub RemoveDupes()
    ThisWorkbook.Worksheets("Data").Range("A1:D1000") _
        .RemoveDuplicates Columns:=Array(1, 2), Header:=xlYes
End Sub

For control over which row survives, load the data into a Dictionary keyed on the duplicate columns instead.

60. How do you refresh all pivot tables and connections?

Sub RefreshEverything()
    ThisWorkbook.RefreshAll                 ' connections + pivots

    Dim ws As Worksheet, pt As PivotTable

    For Each ws In ThisWorkbook.Worksheets
        For Each pt In ws.PivotTables
            pt.RefreshTable
        Next pt
    Next ws
End Sub

For queries where you must wait for the refresh to finish, set BackgroundQuery = False on the connection first.

61. What is the difference between ClearContents, Clear and Delete?

• Range.ClearContents — removes values and formulas, keeps formatting.
• Range.ClearFormats — removes formatting, keeps values.
• Range.Clear — removes everything: values, formats, comments.
• Range.Delete — removes the cells themselves and shifts surrounding cells up or left.

62. How do you use AutoFilter in VBA?

Sub FilterAndCopy()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Data")

    If ws.AutoFilterMode Then ws.AutoFilterMode = False
    ws.Range("A1").CurrentRegion.AutoFilter Field:=3, Criteria1:="India"

    ws.Range("A1").CurrentRegion.SpecialCells(xlCellTypeVisible).Copy
End Sub

SpecialCells(xlCellTypeVisible) is the part interviewers probe. Without it you copy the hidden rows too.

63. What is the With statement and why use it?

With ThisWorkbook.Worksheets("Report").Range("A1:D1")
    .Font.Bold = True
    .Interior.Color = RGB(217, 225, 242)
    .HorizontalAlignment = xlCenter
    .Borders(xlEdgeBottom).LineStyle = xlContinuous
End With

VBA resolves the object reference once instead of five times, which is both faster and more readable.

64. How do you write to and read from a text or CSV file?

Sub WriteTextFile()
    Dim iFile As Integer
    iFile = FreeFile

    Open "C:\Test\log.txt" For Output As #iFile
    Print #iFile, "Run completed at " & Now
    Close #iFile
End Sub

Sub ReadTextFile()
    Dim iFile As Integer, sLine As String
    iFile = FreeFile

    Open "C:\Test\log.txt" For Input As #iFile
    Do Until EOF(iFile)
        Line Input #iFile, sLine
        Debug.Print sLine
    Loop
    Close #iFile
End Sub

For Output overwrites, For Append adds to the end. Always use FreeFile rather than hard-coding #1.

65. How do you send an email from Excel VBA?

Sub SendReportByEmail()
    Dim oOutlook As Object, oMail As Object

    Set oOutlook = CreateObject("Outlook.Application")
    Set oMail = oOutlook.CreateItem(0)          ' 0 = olMailItem

    With oMail
        .To = "manager@example.com"
        .Subject = "Daily sales report"
        .Body = "Please find the report attached."
        .Attachments.Add ThisWorkbook.FullName
        .Display                                 ' use .Send to send silently
    End With

    Set oMail = Nothing
    Set oOutlook = Nothing
End Sub

66. What is the difference between xlUp, xlDown, xlToLeft and xlToRight?

They are direction constants for the Range.End property, equivalent to pressing Ctrl plus an arrow key. End(xlUp) from the bottom of a column finds the last row with data; End(xlToRight) from column A finds the last column in a contiguous block.

67. How do you use Excel worksheet functions in VBA?

Call them through Application.WorksheetFunction. The difference between the two available forms matters:

Dim vResult As Variant

' Raises a run-time error if the value is not found
vResult = Application.WorksheetFunction.VLookup("Delhi", Range("A:B"), 2, False)

' Returns an error value instead, so you can test it
vResult = Application.VLookup("Delhi", Range("A:B"), 2, False)

If IsError(vResult) Then
    MsgBox "Not found"
Else
    MsgBox vResult
End If

Functions that exist natively in VBA, such as Left, Mid and Trim, should be used directly rather than through WorksheetFunction.

68. How do you protect and unprotect a worksheet in VBA?

Sub ProtectSheet()
    ThisWorkbook.Worksheets("Report").Protect _
        Password:="secret", UserInterfaceOnly:=True
End Sub

Sub UnprotectSheet()
    ThisWorkbook.Worksheets("Report").Unprotect Password:="secret"
End Sub

UserInterfaceOnly:=True is the useful detail: the sheet stays locked for the user but your macros can still write to it, so you do not have to unprotect and re-protect around every change. It resets when the workbook is closed, so re-apply it on Workbook_Open.

69. How do you work with named ranges in VBA?

Sub UseNames()
    ' Create
    ThisWorkbook.Names.Add Name:="SalesData", _
                           RefersTo:="=Data!$A$1:$D$100"

    ' Use
    Range("SalesData").Interior.ColorIndex = 36

    ' Delete
    ThisWorkbook.Names("SalesData").Delete
End Sub

Named ranges make code readable and survive rows being inserted above them, which hard-coded addresses do not.

70. How do you work with Excel tables (ListObjects) in VBA?

Sub UseListObject()
    Dim lo As ListObject
    Set lo = ThisWorkbook.Worksheets("Data").ListObjects("tblSales")

    Debug.Print lo.ListRows.Count             ' rows excluding the header

    lo.ListColumns("Region").DataBodyRange.Interior.ColorIndex = 35
    lo.ListRows.Add                            ' append a row
End Sub

Tables grow automatically as data is added, so DataBodyRange removes the need to find the last row at all.

71. How do you copy a worksheet to a new workbook?

Sub CopySheetToNewBook()
    Dim wbNew As Workbook

    ThisWorkbook.Worksheets("Report").Copy    ' no argument = new workbook
    Set wbNew = ActiveWorkbook

    Application.DisplayAlerts = False
    wbNew.SaveAs "C:\Test\Report.xlsx", xlOpenXMLWorkbook
    Application.DisplayAlerts = True

    wbNew.Close SaveChanges:=False
End Sub

To keep values only and drop the formulas, copy the sheet then paste-special its used range over itself with xlPasteValues.

72. How do you create a chart with VBA?

Sub AddChart()
    Dim ws As Worksheet, ch As ChartObject

    Set ws = ThisWorkbook.Worksheets("Data")
    Set ch = ws.ChartObjects.Add(Left:=300, Top:=20, Width:=400, Height:=250)

    With ch.Chart
        .SetSourceData Source:=ws.Range("A1:B12")
        .ChartType = xlColumnClustered
        .HasTitle = True
        .ChartTitle.Text = "Monthly sales"
    End With
End Sub

73. How do you schedule a macro to run at a specific time?

Sub ScheduleRefresh()
    Application.OnTime EarliestTime:=TimeValue("09:30:00"), _
                       Procedure:="RefreshEverything"
End Sub

Sub CancelSchedule()
    Application.OnTime EarliestTime:=TimeValue("09:30:00"), _
                       Procedure:="RefreshEverything", Schedule:=False
End Sub

74. How do you handle dates in VBA?

Build dates with DateSerial rather than strings, which removes any dependence on regional settings.

Dim dStart As Date, dEnd As Date

dStart = DateSerial(2026, 4, 1)
dEnd = DateAdd("m", 3, dStart)                ' add three months

Debug.Print Format(dStart, "dd-mmm-yyyy")
Debug.Print DateDiff("d", dStart, dEnd) & " days"
Debug.Print Year(Date), Month(Date), Day(Date)

Remember that Excel stores a date as a serial number, so Range("A1").Value2 returns 46114 where .Value returns a Date.

Read more Excel VBA interview questions

55+ Excel VBA interview questions and answers with examples

UserForm interview questions and answers

If the role involves building tools for other people rather than reports for yourself, expect UserForm questions. They test event handling more than layout.

75. How do you create a UserForm and show it?

In the VBA editor choose Insert > UserForm, drag controls from the Toolbox, set each control’s Name in the Properties window, then show it from a procedure.

Sub ShowForm()
    frmEntry.Show               ' modal by default
End Sub

Rename controls before writing any code. txtEmployeeName is maintainable; TextBox1 is not, and interviewers notice.

76. What is the difference between modal and modeless forms?

A modal form blocks everything else until it closes, so the user cannot touch the worksheet. A modeless form (frmEntry.Show vbModeless) lets them keep working, which is what you want for a progress window or a search panel that updates the sheet as they type.

77. What is the difference between the Initialize and Activate events?

UserForm_Initialize fires once, when the form object is created — the place to fill combo boxes and set defaults. UserForm_Activate fires every time the form becomes active, which can be several times in one session if it is hidden and shown again.

Private Sub UserForm_Initialize()
    Me.cboRegion.List = Array("North", "South", "East", "West")
    Me.txtDate.Value = Format(Date, "dd-mmm-yyyy")
End Sub

78. How do you populate a ComboBox or ListBox from a worksheet range?

Private Sub UserForm_Initialize()
    Dim ws As Worksheet
    Dim lLast As Long

    Set ws = ThisWorkbook.Worksheets("Lists")
    lLast = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row

    Me.cboRegion.List = ws.Range("A2:A" & lLast).Value
End Sub

Assigning .List from a range in one statement is faster than AddItem in a loop, and it is the answer interviewers are listening for.

79. How do you validate user input before accepting a form?

Private Sub cmdSave_Click()
    If Len(Trim(Me.txtName.Value)) = 0 Then
        MsgBox "Name is required.", vbExclamation
        Me.txtName.SetFocus
        Exit Sub
    End If

    If Not IsNumeric(Me.txtAmount.Value) Then
        MsgBox "Amount must be a number.", vbExclamation
        Me.txtAmount.SetFocus
        Exit Sub
    End If

    ' ... write the record, then close
    Unload Me
End Sub

Validate in the button’s click event rather than in each control’s Exit event, so a user can tab through the form freely and only gets stopped when they try to save.

80. What is the difference between Unload Me and Me.Hide?

Unload Me destroys the form and releases its memory, so every control value is lost. Me.Hide keeps the form in memory, which is how you read a value back after the form closes.

' In the form's OK button
Private Sub cmdOK_Click()
    Me.Hide
End Sub

' In a standard module
Sub GetInput()
    frmEntry.Show
    MsgBox "You entered: " & frmEntry.txtName.Value
    Unload frmEntry
End Sub

Note that referring to frmEntry after unloading it silently re-creates a blank form, which is a classic interview trap.

Technical and scenario-based VBA interview questions

Above two years’ experience, interviewers move from definitions to judgement: how you would approach a real problem, and what you do when the code fails in production.

81. A macro takes 20 minutes to run. How would you diagnose and fix it?

1. Measure first. Wrap sections in Timer calls and print elapsed seconds to the Immediate window, so you optimise the real bottleneck rather than the suspected one.
2. Look for cell-by-cell loops. Replace them with a single array read, in-memory processing and a single array write.
3. Check for volatile recalculation caused by writing to cells inside a loop while calculation is automatic.
4. Remove Select and Activate left behind by the macro recorder.
5. Turn off ScreenUpdating, Calculation and EnableEvents for the duration, restoring them in the error handler.
6. Question the design. If the macro pulls 500,000 rows from a database, Power Query or SQL may be the right answer instead of VBA.

82. Your macro works on your machine but fails on a colleague’s. What do you check?

• Missing references — early-bound libraries such as Outlook or Scripting.Runtime may be absent or a different version. Switch to late binding with CreateObject.
• 32-bit versus 64-bit Office — API declarations need PtrSafe and LongPtr.
• Hard-coded paths and mapped drive letters that do not exist on their machine.
• Regional settings — date and decimal separators differ.
• Macro security and Trusted Locations, plus “Trust access to the VBA project object model”.
• Excel version differences — properties added in newer versions fail to compile in older ones.

83. How would you structure a VBA project that several people maintain?

Separate modules by responsibility: data access, business logic, UI, utilities. Put Option Explicit everywhere. Use a consistent naming convention (ws for worksheet, o for object, s for string). Centralise configuration such as file paths and sheet names in one constants module or a hidden settings sheet, never scattered through the code. Add a single reusable error handler that logs the procedure name and error number to a text file. Export modules as .bas and .cls files so the code can go into version control, since .xlsm is a binary blob to Git.

84. How do you handle an error that only occurs sometimes?

Log it rather than suppress it. Write Err.Number, Err.Description, the procedure name and the relevant variable values to a text file or hidden log sheet, then let the handler exit cleanly. Reproduce from the log rather than from the user’s description.

85. When would you say VBA is the wrong tool?

A senior-level question, and “never” is the wrong answer. VBA is a poor fit when the work must run on a schedule without a user session, when data volumes exceed what Excel holds comfortably, when several people need concurrent access, when the destination is a web or mobile interface, or when the organisation is standardising on Power Query, Power Automate or Python. Say what you would recommend instead and why. That is what the question tests.

86. How do you prevent a Worksheet_Change event from triggering itself?

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo CleanExit
    Application.EnableEvents = False

    If Not Intersect(Target, Me.Range("B:B")) Is Nothing Then
        Target.Offset(0, 1).Value = Environ("Username")
    End If

CleanExit:
    Application.EnableEvents = True
End Sub

The error handler is essential. If the procedure exits on an error with EnableEvents still False, every event in the application stops firing until Excel restarts.

VBA coding round questions with solutions

The practical round usually asks for a short working macro rather than a definition. These are the tasks that come up most often. Write the logic first and say it out loud as you go — interviewers score the approach more heavily than the syntax.

87. Write a macro to consolidate all sheets into one master sheet

Sub ConsolidateSheets()
    Dim ws As Worksheet, wsMaster As Worksheet
    Dim lLastRow As Long, lNextRow As Long

    Set wsMaster = ThisWorkbook.Worksheets("Master")
    wsMaster.Cells.ClearContents
    lNextRow = 1

    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> wsMaster.Name Then
            lLastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row

            If lLastRow > 1 Then
                ws.Range("A2:E" & lLastRow).Copy _
                    wsMaster.Cells(lNextRow + 1, 1)
                lNextRow = lNextRow + lLastRow - 1
            End If
        End If
    Next ws

    ThisWorkbook.Worksheets(2).Range("A1:E1").Copy wsMaster.Range("A1")
End Sub

88. Write a macro to delete all blank rows in a range

Sub DeleteBlankRows()
    Dim ws As Worksheet
    Dim i As Long, lLast As Long

    Set ws = ThisWorkbook.Worksheets("Data")
    lLast = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row

    For i = lLast To 1 Step -1               ' backwards, always
        If Application.CountA(ws.Rows(i)) = 0 Then
            ws.Rows(i).Delete
        End If
    Next i
End Sub

The one-liner alternative is worth mentioning too: ws.Range("A1:A" & lLast).SpecialCells(xlCellTypeBlanks).EntireRow.Delete, wrapped in error handling because SpecialCells raises an error when nothing matches.

89. Write a macro to highlight duplicate values in a column

Sub HighlightDuplicates()
    Dim oDict As Object, ws As Worksheet
    Dim i As Long, lLast As Long, sKey As String

    Set oDict = CreateObject("Scripting.Dictionary")
    Set ws = ThisWorkbook.Worksheets("Data")
    lLast = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row

    For i = 2 To lLast
        sKey = CStr(ws.Cells(i, 1).Value)

        If oDict.Exists(sKey) Then
            ws.Cells(i, 1).Interior.ColorIndex = 6      ' yellow
        Else
            oDict.Add sKey, i
        End If
    Next i
End Sub

90. Write a function to reverse a string without using StrReverse

Function ReverseText(sInput As String) As String
    Dim i As Long, sOut As String

    For i = Len(sInput) To 1 Step -1
        sOut = sOut & Mid(sInput, i, 1)
    Next i

    ReverseText = sOut
End Function

Expect a follow-up on palindromes: ReverseText(s) = s, usually after stripping spaces and matching case with LCase(Replace(s, " ", "")).

91. Write a macro to print the Fibonacci series and check for a prime

Sub Fibonacci(ByVal nTerms As Long)
    Dim a As Long, b As Long, i As Long, t As Long
    a = 0: b = 1

    For i = 1 To nTerms
        Debug.Print a
        t = a + b
        a = b
        b = t
    Next i
End Sub

Function IsPrime(ByVal n As Long) As Boolean
    Dim i As Long

    If n < 2 Then Exit Function
    For i = 2 To Int(Sqr(n))
        If n Mod i = 0 Then Exit Function
    Next i

    IsPrime = True
End Function

Stopping the loop at the square root rather than at n is the detail an interviewer is looking for.

92. Write a macro to split a sheet into separate workbooks by a column value

Sub SplitByRegion()
    Dim ws As Worksheet, wbNew As Workbook
    Dim oDict As Object, vKey As Variant
    Dim i As Long, lLast As Long

    Set ws = ThisWorkbook.Worksheets("Data")
    Set oDict = CreateObject("Scripting.Dictionary")
    lLast = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row

    For i = 2 To lLast
        oDict(CStr(ws.Cells(i, 3).Value)) = 1        ' column C = Region
    Next i

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False

    For Each vKey In oDict.Keys
        If ws.AutoFilterMode Then ws.AutoFilterMode = False
        ws.Range("A1:E" & lLast).AutoFilter Field:=3, Criteria1:=vKey

        Set wbNew = Workbooks.Add
        ws.Range("A1:E" & lLast).SpecialCells(xlCellTypeVisible).Copy _
            wbNew.Worksheets(1).Range("A1")

        wbNew.SaveAs "C:\Output\" & vKey & ".xlsx", xlOpenXMLWorkbook
        wbNew.Close SaveChanges:=False
    Next vKey

    ws.AutoFilterMode = False
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
End Sub

93. Write a macro to import every CSV file in a folder

Sub ImportAllCSVs()
    Dim sPath As String, sFile As String
    Dim wbCSV As Workbook, wsTarget As Worksheet
    Dim lNext As Long

    sPath = "C:\Imports\"
    Set wsTarget = ThisWorkbook.Worksheets("Import")
    sFile = Dir(sPath & "*.csv")

    Application.ScreenUpdating = False

    Do While Len(sFile) > 0
        Set wbCSV = Workbooks.Open(sPath & sFile)

        lNext = wsTarget.Cells(wsTarget.Rows.Count, 1).End(xlUp).Row + 1
        wbCSV.Worksheets(1).UsedRange.Copy wsTarget.Cells(lNext, 1)

        wbCSV.Close SaveChanges:=False
        sFile = Dir
    Loop

    Application.ScreenUpdating = True
End Sub

94. Write a function to count the words in a cell

Function WordCount(rng As Range) As Long
    Dim sText As String

    sText = Application.Trim(rng.Value)       ' collapses repeated spaces
    If Len(sText) = 0 Then Exit Function

    WordCount = UBound(Split(sText, " ")) + 1
End Function

Use it on the sheet as =WordCount(A1). Application.Trim is deliberate: the VBA Trim only removes leading and trailing spaces, not the doubles in between.

Debug the code: find the bug questions

A quiet favourite in technical rounds. You are shown a short procedure and asked what is wrong with it. Each of these appears regularly, and each has a one-line fix.

95. Why does this loop skip rows?

For i = 1 To lLastRow
    If Cells(i, 1).Value = "" Then Rows(i).Delete
Next i

The bug: deleting row 5 moves row 6 up into position 5, but the counter has already advanced to 6, so the row that moved up is never tested. Consecutive blanks survive.

The fix: loop backwards with For i = lLastRow To 1 Step -1. The same applies to deleting sheets, slides and collection items.

96. What is wrong with this declaration?

Dim i, j, k As Long

The bug: only k is a Long. i and j are Variants, because VBA does not carry a type across a comma the way most languages do.

The fix: Dim i As Long, j As Long, k As Long. This is probably the most common real bug in production VBA, and it is invisible until performance or a type mismatch gives it away.

97. Why does this fail at row 32,768?

Dim lastRow As Integer
lastRow = Cells(Rows.Count, 1).End(xlUp).Row

The bug: Integer holds a maximum of 32,767, so any sheet with more rows than that raises run-time error 6, Overflow.

The fix: declare row counters as Long. There is no memory reason to prefer Integer in modern VBA; it is converted to Long internally anyway.

98. Why does this raise “Object variable or With block variable not set”?

Dim ws As Worksheet
ws = ThisWorkbook.Worksheets("Data")
ws.Range("A1").Value = "Hello"

The bug: the missing Set. Object variables need Set; without it VBA tries to assign the worksheet’s default property and leaves ws as Nothing, producing error 91 on the next line.

The fix: Set ws = ThisWorkbook.Worksheets("Data").

99. What is dangerous about this error handler?

Sub ProcessAll()
    On Error Resume Next
    Application.ScreenUpdating = False

    ' ... 200 lines of processing ...

    Application.ScreenUpdating = True
End Sub

The bug: two of them. On Error Resume Next across the whole procedure hides every failure, so the macro finishes “successfully” with wrong output. And if an error does stop execution, ScreenUpdating is never restored and Excel appears frozen.

The fix: use On Error GoTo ErrHandler with a CleanExit label that always restores the application settings, and reserve Resume Next for a single line where you expect a specific error.

MS Access VBA interview questions and answers

Access VBA questions centre on DAO, recordsets and running SQL from code.

100. What is the difference between DAO and ADO?

DAO (Data Access Objects) is native to the Jet/ACE engine that Access uses, and is faster for local Access databases. ADO (ActiveX Data Objects) is a generic provider-based model that reaches SQL Server, Oracle, Excel and more through OLE DB. Use DAO for Access-only work, ADO when the back end is or may become an external database.

101. How do you create a table in an Access database using VBA?

Sub CreateTable()
    Dim dbs As DAO.Database
    Set dbs = OpenDatabase("C:\Users\Public\Documents\MyDatabase.accdb")

    dbs.Execute "CREATE TABLE Employees " & _
                "(EName TEXT(50), ENumber LONG, ELocation TEXT(50));"

    dbs.Close
    Set dbs = Nothing
End Sub

Requires a reference to the Microsoft Office Access Database Engine Object Library.

102. How do you insert, update and delete records with VBA?

Sub ModifyRecords()
    Dim dbs As DAO.Database
    Set dbs = CurrentDb

    dbs.Execute "INSERT INTO Employees VALUES ('John', 12345, 'U.S');", dbFailOnError
    dbs.Execute "UPDATE Employees SET ELocation = 'U.K' WHERE ENumber = 12345;", dbFailOnError
    dbs.Execute "DELETE FROM Employees WHERE ENumber = 12345;", dbFailOnError

    Debug.Print dbs.RecordsAffected & " record(s) affected"
    Set dbs = Nothing
End Sub

dbFailOnError is the detail worth mentioning: without it a failed statement is silently ignored. DoCmd.RunSQL does the same job but shows confirmation prompts unless you set DoCmd.SetWarnings False.

103. How do you open and loop through a recordset?

Sub LoopRecordset()
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset

    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("SELECT * FROM Employees WHERE ELocation = 'U.S';")

    Do While Not rst.EOF
        Debug.Print rst!EName, rst!ENumber
        rst.MoveNext
    Loop

    rst.Close
    Set rst = Nothing
    Set dbs = Nothing
End Sub

Check If rst.EOF And rst.BOF Then first to handle an empty recordset, and always close and release both objects.

104. How do you export an Access table to Excel using VBA?

Sub ExportToExcel()
    DoCmd.TransferSpreadsheet TransferType:=acExport, _
                              SpreadsheetType:=acSpreadsheetTypeExcel12Xml, _
                              TableName:="Employees", _
                              FileName:="C:\Test\Employees.xlsx", _
                              HasFieldNames:=True
End Sub

105. How do you connect to SQL Server from VBA using ADO?

Sub QuerySQLServer()
    Dim oConn As Object, oRS As Object

    Set oConn = CreateObject("ADODB.Connection")
    Set oRS = CreateObject("ADODB.Recordset")

    oConn.Open "Provider=SQLOLEDB;Data Source=SERVER01;" & _
               "Initial Catalog=SalesDB;Integrated Security=SSPI;"

    oRS.Open "SELECT TOP 100 * FROM dbo.Orders", oConn

    Do While Not oRS.EOF
        Debug.Print oRS.Fields("OrderID").Value
        oRS.MoveNext
    Loop

    oRS.Close
    oConn.Close
End Sub

In Excel you can drop a whole recordset onto a sheet in one line with Range("A2").CopyFromRecordset oRS.

106. How do you suppress Access confirmation prompts in VBA?

Sub RunActionQuery()
    On Error GoTo CleanExit
    DoCmd.SetWarnings False

    DoCmd.RunSQL "UPDATE Employees SET ELocation = 'U.K' WHERE ENumber = 12345;"

CleanExit:
    DoCmd.SetWarnings True                    ' always restore
End Sub

The better answer is to avoid the setting altogether: CurrentDb.Execute sql, dbFailOnError never prompts and does report failures, whereas SetWarnings False hides real errors as well as prompts.

Read more MS Access VBA interview questions

10+ MS Access VBA interview questions and answers with examples

MS PowerPoint VBA interview questions and answers

107. How do you create a new presentation and add a slide?

Sub CreatePresentationWithSlide()
    Dim oPres As Presentation
    Dim oSlide As Slide

    Set oPres = Presentations.Add
    Set oSlide = oPres.Slides.Add(Index:=1, Layout:=ppLayoutTitle)

    oSlide.Shapes(1).TextFrame.TextRange.Text = "Quarterly Review"
    oSlide.Shapes(2).TextFrame.TextRange.Text = "Prepared with VBA"
End Sub

108. How do you save a PowerPoint presentation from VBA?

Sub SavePresentation()
    Dim oPres As Presentation
    Set oPres = Presentations.Add
    oPres.SaveAs FileName:="D:\TestPresentation.pptx"
End Sub

109. How do you delete a slide?

Sub DeleteSlides()
    ActivePresentation.Slides(1).Delete

    ' Delete several — always loop backwards
    Dim i As Long
    For i = ActivePresentation.Slides.Count To 1 Step -1
        If ActivePresentation.Slides(i).Shapes.Count = 0 Then
            ActivePresentation.Slides(i).Delete
        End If
    Next i
End Sub

Looping backwards is the point of this question: deleting forwards renumbers the remaining slides and skips items.

110. How do you duplicate a slide and move it to the end?

Sub DuplicateSlideToEnd()
    Dim oSlide As Slide
    Set oSlide = ActivePresentation.Slides(1).Duplicate(1)
    oSlide.MoveTo ActivePresentation.Slides.Count
End Sub

111. How do you loop through every shape on every slide?

Sub LoopShapes()
    Dim oSlide As Slide, oShape As Shape

    For Each oSlide In ActivePresentation.Slides
        For Each oShape In oSlide.Shapes
            If oShape.HasTextFrame Then
                If oShape.TextFrame.HasText Then
                    Debug.Print oSlide.SlideIndex, oShape.TextFrame.TextRange.Text
                End If
            End If
        Next oShape
    Next oSlide
End Sub

The nested HasTextFrame and HasText check prevents a run-time error on pictures and charts.

112. How do you control PowerPoint from Excel VBA?

Sub ExportChartToPowerPoint()
    Dim oPPT As Object, oPres As Object, oSlide As Object

    Set oPPT = CreateObject("PowerPoint.Application")
    oPPT.Visible = True

    Set oPres = oPPT.Presentations.Add
    Set oSlide = oPres.Slides.Add(1, 12)      ' 12 = ppLayoutBlank

    ThisWorkbook.Worksheets("Data").ChartObjects(1).Chart.CopyPicture
    oSlide.Shapes.Paste

    Set oSlide = Nothing
    Set oPres = Nothing
    Set oPPT = Nothing
End Sub

Late binding is used here deliberately, so the workbook runs on machines with a different PowerPoint version. With early binding you would set a reference to the Microsoft PowerPoint Object Library and could use ppLayoutBlank by name.

Read more MS PowerPoint VBA interview questions

10+ MS PowerPoint VBA interview questions and answers

MS Word VBA interview questions and answers

113. How do you create a new Word document from VBA?

Sub CreateDocument()
    Dim oDoc As Document
    Set oDoc = Documents.Add
    oDoc.Content.Text = "Created with VBA."
End Sub

' Based on an existing template
Sub CreateFromTemplate()
    Documents.Add Template:="D:\Templates\Report.dotx", NewTemplate:=False
End Sub

114. How do you open a document as read-only?

Sub OpenReadOnly()
    Documents.Open FileName:="D:\Test.docx", ReadOnly:=True
End Sub

115. How do you save and SaveAs a document?

Sub SaveDocument()
    If Not ActiveDocument.Saved Then ActiveDocument.Save
End Sub

Sub SaveDocumentAs()
    ActiveDocument.SaveAs2 FileName:="D:\Report.docx", _
                           FileFormat:=wdFormatXMLDocument
End Sub

SaveAs2 replaced SaveAs from Word 2010 onward.

116. How do you find and replace text in a Word document?

Sub FindAndReplace()
    With ActiveDocument.Content.Find
        .ClearFormatting
        .Replacement.ClearFormatting
        .Text = "[CLIENT]"
        .Replacement.Text = "AnalysisTabs"
        .Execute Replace:=wdReplaceAll
    End With
End Sub

117. How do you close a document without saving?

Sub CloseWithoutSaving()
    ActiveDocument.Close SaveChanges:=wdDoNotSaveChanges
End Sub

The three constants are wdDoNotSaveChanges, wdSaveChanges and wdPromptToSaveChanges.

118. How do you loop through paragraphs and tables in a Word document?

Sub LoopDocument()
    Dim oPara As Paragraph, oTable As Table, oCell As Cell

    For Each oPara In ActiveDocument.Paragraphs
        If oPara.Style = "Heading 1" Then
            Debug.Print oPara.Range.Text
        End If
    Next oPara

    For Each oTable In ActiveDocument.Tables
        For Each oCell In oTable.Range.Cells
            Debug.Print oCell.RowIndex, oCell.ColumnIndex, oCell.Range.Text
        Next oCell
    Next oTable
End Sub

Cell text carries two trailing control characters, so use Left(oCell.Range.Text, Len(oCell.Range.Text) - 2) when you need the clean value.

Read more MS Word VBA interview questions

10+ MS Word VBA interview questions and answers

MS Outlook VBA interview questions and answers

119. How do you create and send a message using Outlook VBA?

Sub SendMessage()
    Dim oOutlook As Object, oMail As Object

    Set oOutlook = CreateObject("Outlook.Application")
    Set oMail = oOutlook.CreateItem(0)      ' 0 = olMailItem

    With oMail
        .To = "recipient@example.com"
        .CC = "manager@example.com"
        .Subject = "Test email"
        .Body = "This is a test message."
        .Send                                ' use .Display to review first
    End With

    Set oMail = Nothing
    Set oOutlook = Nothing
End Sub

Use .HTMLBody instead of .Body for formatted email.

120. How do you add an attachment to a message?

Sub SendWithAttachment()
    Dim oOutlook As Object, oMail As Object

    Set oOutlook = CreateObject("Outlook.Application")
    Set oMail = oOutlook.CreateItem(0)

    With oMail
        .To = "recipient@example.com"
        .Subject = "Report attached"
        .Body = "Please find the report attached."
        .Attachments.Add "D:\Report.xlsx"
        .Display
    End With

    Set oMail = Nothing
    Set oOutlook = Nothing
End Sub

121. How do you read messages from the Inbox?

Sub ReadInbox()
    Dim oOutlook As Object, oNamespace As Object
    Dim oInbox As Object, oItem As Object

    Set oOutlook = CreateObject("Outlook.Application")
    Set oNamespace = oOutlook.GetNamespace("MAPI")
    Set oInbox = oNamespace.GetDefaultFolder(6)   ' 6 = olFolderInbox

    For Each oItem In oInbox.Items
        If oItem.Class = 43 Then                  ' 43 = olMail
            Debug.Print oItem.ReceivedTime, oItem.SenderName, oItem.Subject
        End If
    Next oItem
End Sub

The oItem.Class check matters: an Inbox can contain meeting requests and reports, which do not have all mail properties.

122. How do you create a task or appointment from VBA?

Sub CreateTask()
    Dim oOutlook As Object, oTask As Object

    Set oOutlook = CreateObject("Outlook.Application")
    Set oTask = oOutlook.CreateItem(3)        ' 3 = olTaskItem

    With oTask
        .Subject = "Send the monthly report"
        .DueDate = Date + 7
        .ReminderSet = True
        .Save
    End With

    Set oTask = Nothing
    Set oOutlook = Nothing
End Sub

Use CreateItem(1) for an appointment.

123. How do you save attachments from incoming email automatically?

' Place in ThisOutlookSession, wired to a rule that runs a script
Sub SaveAttachments(oItem As Outlook.MailItem)
    Dim oAtt As Outlook.Attachment
    Const SAVE_PATH As String = "C:\Incoming\"

    For Each oAtt In oItem.Attachments
        oAtt.SaveAsFile SAVE_PATH & oAtt.FileName
    Next oAtt
End Sub

124. What is the ItemSend event and how do you use it?

Application_ItemSend fires in ThisOutlookSession every time a message is sent, which makes it the standard place for outbound checks such as warning about a missing attachment.

' In ThisOutlookSession
Private Sub Application_ItemSend(ByVal Item As Object, Cancel As Boolean)
    If Item.Class <> 43 Then Exit Sub         ' 43 = olMail

    If InStr(1, Item.Body, "attach", vbTextCompare) > 0 Then
        If Item.Attachments.Count = 0 Then
            If MsgBox("No attachment. Send anyway?", vbYesNo) = vbNo Then
                Cancel = True
            End If
        End If
    End If
End Sub

Setting Cancel = True stops the send and returns the user to the open message.

Read more MS Outlook VBA interview questions

10+ MS Outlook VBA interview questions and answers

VBA MCQs and assessment practice questions

Many companies now screen with an online VBA test or a LinkedIn-style skill assessment before the interview. These multiple-choice questions cover what those tests target most often. The answer is at the end of each one.

125. Which of the following best describes VBA?

A. A standalone programming language that compiles to .exe
B. An event-driven language hosted inside Microsoft Office applications
C. A spreadsheet formula language
D. A database query language

126. Which of the following is NOT true about VBA?

A. It supports loops and conditional statements
B. It can create user forms
C. It can run without any Microsoft Office application installed
D. It uses an object model to control the host application

127. What is the default way arguments are passed in VBA?

A. ByVal    B. ByRef    C. ByPointer    D. Optional

128. After Dim aValue(3) As Long, how many elements does the array hold?

A. 3    B. 4    C. 2    D. Depends on Option Base

129. What does lastRow = Cells(Rows.Count, 1).End(xlUp).Row return?

A. The total number of rows in the sheet
B. The row number of the last cell in column A that contains data
C. The first empty row in column A
D. Always 1048576

130. Which statement forces variables to be declared?

A. Option Base 1    B. Option Compare Text    C. Option Explicit    D. Option Private Module

131. Which is the fastest way to write 10,000 values to a worksheet?

A. A loop writing one cell at a time
B. A loop using Select then ActiveCell.Value
C. Building a Variant array and assigning it to a range in one statement
D. Copy and paste in a loop

132. What does the Project Explorer in the VBA editor show?

A. Only the code of the active module
B. A tree of all open projects with their modules, forms, class modules and Office objects
C. The values of variables at run time
D. Compilation errors

VBA practice exercises

Reading answers is not the same as writing code under pressure. Work through these before an interview. Each maps to a task that comes up in real VBA jobs, and every technique needed is explained somewhere on this page.

• Basic VBA examples for beginners — start here if you are a fresher.
• 100+ advanced VBA examples with solutions — for experienced roles.

VBA versus Power Query, Office Scripts and Python

Increasingly asked, and not a trick question. Interviewers want to know you choose tools on merit rather than defaulting to the one you know. Being able to say where VBA loses is treated as a sign of experience, not disloyalty.

How to answer it well

Give a concrete boundary rather than a generality. For example: “I would keep the monthly pack in VBA because the users open it in Excel and click a button, but the data load underneath it should be Power Query, so nobody has to maintain a hundred lines of import code.” That answer shows judgement about a specific situation, which is what the question is testing.

How to prepare, by experience level

Freshers and 0 to 1 year

Expect definitions and short code: data types, scope, Sub versus Function, loops, the Excel object model, recording and editing a macro, and finding the last row. Have one small automation you have actually built ready to describe end to end — what was manual before, what the macro does, how long it saves. That story matters more than the syntax.

2 to 5 years

Expect error handling, performance tuning, arrays, dictionaries, UserForms, working across applications, and the ByVal versus ByRef question. You will likely be asked to debug a snippet or explain why a given macro is slow. Be ready to talk about a bug you found in production and how you traced it.

Senior and lead

Expect design questions: how you structure a shared codebase, how you version-control VBA, how you decide between VBA, Power Query, Office Scripts and Python, how you handle 64-bit and version compatibility, and how you hand a tool over to users who cannot maintain it. Knowing when not to use VBA is treated as a signal of seniority.

HR and behavioural questions for VBA roles

The technical round is rarely the one candidates lose. These come up in almost every VBA interview, and the answers that work are specific rather than polished.

133. Tell me about a macro or tool you built end to end

Structure it as: what was manual before, what you built, what it saves now. “The team spent two hours every morning reconciling 14 broker files by hand. I built a workbook that reads the folder, matches on trade ID, flags the breaks and mails the summary. It runs in about three minutes and two people moved onto other work.” Numbers make the story credible; have one project you can describe at that level of detail.

134. How do you test a macro before releasing it to users?

Talk about a copy of real data rather than made-up rows, edge cases (empty sheet, one row, duplicate keys, a missing file), running it twice to check it is repeatable, and having one user try it before the whole team gets it. Mention that you never test destructive code on the only copy of a file. Nobody expects unit tests in VBA, but they do expect a method.

135. What happens to your automation when you are on leave?

This question is about handover, and the honest answer is documentation. Comments at the top of each module explaining what it does and what it assumes, a ReadMe sheet inside the workbook, configuration in one place rather than scattered through the code, and at least one colleague who has run it with you. If you have exported modules into a shared folder or Git, say so.

136. How do you handle a user who keeps changing the requirements?

Describe how you keep the change cheap rather than how you push back. Configuration in a settings sheet instead of hard-coded values, small modules that can be swapped, and agreeing what the tool will and will not do in writing before you build. Then give a real example of a request you accommodated and one you talked the user out of, and why.

137. Have you ever broken something in production? What happened?

Answer it straight, briefly, and finish on the fix. A macro that wrote to the wrong sheet, a delete that ran on unfiltered data, a file overwritten with DisplayAlerts off. What matters is what changed afterwards: a confirmation prompt, a backup copy written before the destructive step, or a test on a copy first. Candidates who claim they have never broken anything are usually the ones who have not shipped much.

138. Where do you want to take your skills from here?

Given the direction of the Microsoft stack, an answer that mentions SQL, Power Query or Python alongside VBA reads as awareness rather than disloyalty. Be specific about what you are actually learning and where you have used it, even in a small way. Vague ambition is less convincing than one concrete thing you built last month.

VBA quick revision cheat sheet

The syntax worth having in your head on the morning of the interview. If you can write these from memory, you can rebuild most of what gets asked.

VBA interview questions PDF, free download

Frequently asked questions

Related Excel VBA guides

• 100+ most useful Excel VBA codes — the full macro library.
• VBA Code Explorer — browse by object, method and property.
• Variables and data types in VBA — the basis of half these questions.
• Scope of variables in VBA — procedure, module and project level.
• Excel VBA tutorial — from basics to advanced programming.

If a question came up in your interview that is not covered here, tell us in the comments and we will add it with a worked example.