Macros and VBA Automation Interview Questions for Advanced Excel
25 Macros and VBA Automation questions with answers for advanced excel roles — 8 easy, 11 medium, 6 hard.
About Macros and VBA Automation
VBA automates repetitive Excel work: formatting, consolidating files, exporting PDFs and sending emails. Learn the macro recorder and its limits, Subs and Functions, loops, last-row logic, events, error handling, arrays and dictionaries for speed, and macro security. Interviews also ask when Power Query, Office Scripts or Python fit better.
Sheet progressStart practicing
3 QUESTIONS FREESign up to unlock the full question bank and track your progress.Unlock every question and answer, then continue practising company-wise and package-wise.
✓ 3 questions free • Unlock the rest in seconds
01
What is a macro and what file format is needed to save it?
A macro is a recorded or written set of instructions in VBA (Visual Basic for Applications) that automates repeated tasks, such as formatting a report or exporting PDFs. Normal .xlsx files cannot store macros, so the workbook must be saved as .xlsm (macro-enabled) or .xlsb. Macros can be run from the Developer tab, a button, a keyboard shortcut or automatically through events.
02
What are the limitations of the macro recorder, and what is relative recording?
The recorder writes code for every click, including unnecessary Select statements and fixed cell addresses, so the macro breaks when data size changes. It cannot record loops, conditions or input boxes. By default it records absolute references; turning on Use Relative References records moves relative to the active cell. The recorder is great for learning syntax, but recorded code should be cleaned up by hand.
03
What is the difference between a Sub and a Function in VBA?
A Sub performs actions, such as formatting cells or saving files, and is run as a macro. A Function returns a value and can be used in worksheet formulas as a user-defined function or called from other code. Both are written in a standard module, opened through Alt+F11 > Insert > Module. Event code goes in the sheet or ThisWorkbook module instead.
04
How do you find the last used row in a column with VBA?
The most reliable common method starts from the bottom of the sheet and moves up:
Dim lastRow As Long
With ThisWorkbook.Worksheets("Data")
lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With
Using Long, not Integer, avoids overflow beyond 32,767 rows. If the data is an Excel Table, ListObjects("Sales").ListRows.Count is even cleaner.
Recorded macros use lines like Range("A1").Select followed by Selection.Copy, which are slow, flicker the screen and break if the user clicks another sheet while the macro runs. Refer to objects directly instead: Worksheets("Data").Range("A1:D100").Copy Worksheets("Report").Range("A1"). Fully qualifying the workbook and worksheet, often with a With block, makes the code faster and predictable.
06
A macro produces wrong totals because a misspelt variable name was silently treated as a new empty variable. How do you prevent this?
Add Option Explicit at the top of every module, and enable Tools > Options > Require Variable Declaration so new modules get it automatically. VBA then refuses to run code that uses an undeclared variable and highlights the typo at compile time. Also declare proper types such as Long or Double instead of Variant, which catches type mistakes and makes the code faster and easier to read.
Trust Center settings control whether macros run: disabled with notification is typical. Files in a trusted location, or signed with a trusted digital certificate, run without prompts. Since 2022, Office blocks macros by default in files downloaded from the internet or email attachments that carry the Mark of the Web; users must unblock the file in its Properties. Organisations usually manage these settings through policy.
08
Why do you want a role that uses Excel heavily, such as MIS or reporting?
I enjoy turning raw numbers into reports that people actually use to decide things. During my studies and projects I built pivot dashboards and lookup-based trackers, and I liked seeing a three-hour manual task become a ten-minute refresh. An MIS role lets me build those skills daily, understand how the business runs, and grow towards analytics tools like Power BI and SQL, which build directly on Excel.
09
Write VBA to loop through all worksheets and apply the same formatting.
Use For Each over the Worksheets collection and skip any sheet you do not want to touch:
Sub FormatAllSheets()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
If ws.Name <> "Summary" Then
ws.Rows(1).Font.Bold = True
ws.UsedRange.Columns.AutoFit
End If
Next ws
End Sub
Use On Error GoTo to jump to a handler, report the problem and clean up:
Sub ImportData()
On Error GoTo Fail
Application.ScreenUpdating = False
' ... main code ...
Done:
Application.ScreenUpdating = True
Exit Sub
Fail:
MsgBox "Error " & Err.Number & ": " & Err.Description
Resume Done
End Sub
Avoid blanket On Error Resume Next, which hides real failures.
11
What are VBA events? Give an example of Worksheet_Change.
Events run code automatically when something happens, such as opening a workbook (Workbook_Open) or editing a cell (Worksheet_Change). In the sheet module:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Me.Range("F:F")) Is Nothing Then
Application.EnableEvents = False
Target.Offset(0, 1).Value = Now
Application.EnableEvents = True
End If
End Sub
This timestamps column G when status in F changes.
12
Write VBA to save each worksheet as a separate PDF.
Use ExportAsFixedFormat inside a loop:
Sub SheetsToPDF()
Dim ws As Worksheet, p As String
p = ThisWorkbook.Path & "\"
For Each ws In ThisWorkbook.Worksheets
ws.ExportAsFixedFormat Type:=xlTypePDF, _
Filename:=p & ws.Name & ".pdf", _
Quality:=xlQualityStandard, IgnorePrintAreas:=False
Next ws
End Sub
Set print areas and page setup first so each PDF fits the page properly.
13
How do you write a user-defined function (UDF) in VBA? Give an example.
Write a Public Function in a standard module and use it like any worksheet function:
Function GSTAmount(amount As Double, Optional rate As Double = 0.18) As Double
GSTAmount = Round(amount * rate, 2)
End Function
In a cell: =GSTAmount(B2) or =GSTAmount(B2,5%). UDFs cannot change other cells or formats, and they recalculate more slowly than built-in functions, so avoid them in very large ranges.
14
A macro works on your computer but fails on a colleague's. What could be the reasons?
Common causes are hard-coded file paths or user names, missing library references shown as MISSING in Tools > References, macros blocked by Trust Center settings or because the file came from the internet, and 32-bit versus 64-bit Office, which needs PtrSafe in API declarations. Different regional settings for dates and decimals, or a renamed sheet, also break code. Use ThisWorkbook.Path and late binding to make macros portable.
15
What are UserForms in VBA and when would you use one?
A UserForm is a custom dialog box with text boxes, dropdowns, option buttons and command buttons, created in the VBA editor through Insert > UserForm. It is useful for controlled data entry, such as an employee or expense entry form that validates inputs before writing a row to a Table, or for letting users choose options before a report macro runs. It reduces errors from typing directly into sheets.
16
Tell me about a time you built a macro that others in your team relied on. How did you keep it maintainable?
Situation: I wrote a macro that split a master file into branch-wise reports for 30 branches. Task: others had to run it while I was on leave. Action: I added Option Explicit, comments, a settings sheet for paths and branch lists, error handling with clear messages and a short user guide. Result: colleagues ran it for months without my help, and adding new branches needed only a row in the settings sheet.
17
Your macro accidentally deleted data, and Undo does not work. How do you prevent this in future?
Macro actions clear Excel's undo history, so prevention matters. Add a confirmation prompt before destructive steps, test on a copy of the file, and have the macro save a backup copy with a timestamp before it changes data. Restrict deletion to clearly identified ranges such as a named Table, not ActiveSheet. For files on OneDrive or SharePoint, version history can usually restore the earlier copy.
Loop from the bottom up so deleting a row does not skip the next one:
Sub DeleteBlankRows()
Dim r As Long, ws As Worksheet
Set ws = ActiveSheet
For r = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row To 2 Step -1
If Trim(ws.Cells(r, "A").Value) = "" Then ws.Rows(r).Delete
Next r
End Sub
For big data, filtering blanks and deleting visible rows in one action is faster.
19
Tell me about a time your automation failed after you handed it over. What did you do?
Situation: a report macro I wrote stopped working for the accounts team. Task: fix it quickly and stop it happening again. Action: I found that a sheet had been renamed and the macro used a fixed sheet name and file path. I fixed it with ThisWorkbook.Path and code names, added an error handler with a clear message, and wrote a short usage note. Result: it ran reliably for the rest of the year and I now test macros on another user's machine before handover.
Switch off screen updates, automatic calculation and events while the macro runs, and always restore them:
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
' ... main code ...
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.EnableEvents = True
Also avoid Select, process data in arrays instead of cell by cell, and use an error handler so settings are restored on failure.
21
Write VBA to consolidate the first sheet of every Excel file in a folder into one sheet.
Loop with Dir, open each file, copy its data below the last row and close it:
Sub Consolidate()
Dim f As String, wb As Workbook, dest As Worksheet, r As Long
Const P As String = "C:\Reports\"
Set dest = ThisWorkbook.Worksheets("All")
f = Dir(P & "*.xlsx")
Do While f <> ""
Set wb = Workbooks.Open(P & f, ReadOnly:=True)
r = dest.Cells(dest.Rows.Count, 1).End(xlUp).Row + 1
wb.Worksheets(1).UsedRange.Offset(1).Copy dest.Cells(r, 1)
wb.Close False
f = Dir
Loop
End Sub
22
Why are arrays faster than looping through cells in VBA?
Each read or write to a cell crosses between VBA and Excel, which is slow when repeated thousands of times. Reading a whole range into a Variant array, processing it in memory and writing it back once is many times faster:
Dim v As Variant, i As Long
v = Range("A2:C50001").Value
For i = 1 To UBound(v, 1)
v(i, 3) = v(i, 1) * v(i, 2)
Next i
Range("A2:C50001").Value = v
23
How do you send personalised emails from Excel through Outlook using VBA?
Use late binding so no library reference is needed, and loop through the rows:
Sub SendMails()
Dim ol As Object, m As Object, r As Long
Set ol = CreateObject("Outlook.Application")
For r = 2 To Cells(Rows.Count, 1).End(xlUp).Row
Set m = ol.CreateItem(0)
m.To = Cells(r, 2).Value
m.Subject = "Payment reminder"
m.Body = "Dear " & Cells(r, 1).Value & ", your due amount is Rs " & Cells(r, 3).Value
m.Display
Next r
End Sub
Use .Display to review before switching to .Send.
24
How is the Scripting.Dictionary object used in VBA?
A Dictionary stores key-value pairs with fast lookups, ideal for unique lists and grouping:
Dim d As Object, i As Long, v As Variant
Set d = CreateObject("Scripting.Dictionary")
v = Range("A2:B10001").Value
For i = 1 To UBound(v)
d(v(i, 1)) = d(v(i, 1)) + v(i, 2)
Next i
Range("D2").Resize(d.Count, 1).Value = Application.Transpose(d.Keys)
This totals amounts per customer far faster than repeated SUMIF calls.
25
When would you choose VBA, Power Query, Office Scripts or Python in Excel?
Power Query is best for importing, cleaning and combining data, because it is refreshable and needs no code security. VBA suits desktop automation such as formatting, emailing, file handling and interacting with other Office apps. Office Scripts, written in TypeScript, automate Excel on the web and work with Power Automate flows. Python in Excel suits statistics and advanced analysis inside a cell. Pick the simplest tool your users can run and maintain.
Frequently asked questions
How important is Macros and VBA Automation in Advanced Excel interviews?
VBA automates repetitive Excel work: formatting, consolidating files, exporting PDFs and sending emails. Learn the macro recorder and its limits, Subs and Functions, loops, last-row logic, events, error handling, arrays and dictionaries for speed, and macro security. Interviews also ask when Power Query, Office Scripts or Python fit better.
How should I use the Macros and VBA Automation sheet?
Go top to bottom — easy to hard. Answer before opening each answer, tick what you solved and save misses to Last-Minute Revision.