Excel VBA interview questions test whether you write macros that are correct, fast and safe to share: declaring variables, working with Range and Cells without selecting, finding the last row, looping with arrays, handling errors and events, and knowing how macro security works. Analyst and finance roles often add a short practical test.
Below are 30 questions with answers and short code, plus three debugging scenarios. Language facts follow Microsoft's VBA reference, and the macro-blocking behaviour is as Microsoft Learn described it on November 17, 2026. Formula-level questions are on the Excel interview questions page.
Excel VBA fundamentals: questions 1–9
- Sub vs Function? A Sub performs actions and returns nothing; a Function returns a value and can be used as a worksheet formula (a user-defined function) if it doesn't change other cells. Example:
Function NetPrice(p As Double) As Double: NetPrice = p * 0.8: End Function. - Why use
Option Explicit? It forces every variable to be declared, so a typo raises a compile error instead of silently creating an empty Variant. Turn on Require Variable Declaration so new modules include it. - What type is
ainDim a, b As Long?ais a Variant; onlybis Long. Declare each one:Dim a As Long, b As Long. - ByRef vs ByVal? VBA passes arguments by reference unless you say otherwise, so a procedure can change the caller's variable. Use
ByValwhen you want a copy. - Range vs Cells?
Range("A1:C10")takes address text;Cells(row, column)takes numbers, which suits loops:ws.Cells(i, 2).Value. Combine them withws.Range(ws.Cells(1, 1), ws.Cells(lastRow, 3)). - Why avoid
SelectandActivate? They are slow and fragile because they depend on what is active. Reference objects directly:ThisWorkbook.Worksheets("Data").Range("A1").Value = 5. - How do you find the last used row?
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Rowreturns the last non-empty cell in column A.UsedRangecan include formatted but empty cells, so it may overshoot. - ThisWorkbook vs ActiveWorkbook?
ThisWorkbookis the file that contains the code;ActiveWorkbookis whatever the user has in front. Code that works on its own file should always use ThisWorkbook. - Which file types keep macros?
.xlsm,.xlsb(binary) and.xlam(add-in);.xlsxcan't store VBA. Macros for every workbook usually live inPERSONAL.XLSB.
Intermediate Excel VBA questions: 10–18
- How do you speed up a slow macro? Read the range into an array, work in memory and write it back once, and during the run set
Application.ScreenUpdating = False,Application.Calculation = xlCalculationManualandApplication.EnableEvents = False, restoring all three on exit. - Show the array pattern.
Dim v As Variant: v = ws.Range("A2:A10001").Valuereturns a two-dimensional, 1-based array; loopFor i = 1 To UBound(v, 1), changev(i, 1), then write back withws.Range("A2:A10001").Value = v. Ten thousand writes become one. - What is a good error-handling pattern?
On Error GoTo ErrHandlerat the top, normal code,Exit Sub, then anErrHandler:block that logsErr.NumberandErr.Descriptionand restores settings. UseOn Error Resume Nextonly around one expected failure, thenOn Error GoTo 0. WorksheetFunction.VLookupvsApplication.VLookup? The first raises a run-time error when the value isn't found; the second returns an error value you can test withIsError(). For many repeated lookups, a Dictionary is faster.- What is
Scripting.Dictionaryfor? Fast key-value lookups and de-duplication:Set d = CreateObject("Scripting.Dictionary"), thenIf Not d.Exists(k) Then d.Add k, v. It comes from the Windows Scripting Runtime, so it isn't available on a Mac. - Early vs late binding? Early binding adds a reference (Tools, References) and declares specific types, giving IntelliSense and slightly faster calls; late binding uses
CreateObjectwithObjectvariables and avoids version-specific references when you share files. - How do worksheet events work?
Workbook_Opengoes in ThisWorkbook andWorksheet_Change(ByVal Target As Range)in the sheet's module. CheckNot Intersect(Target, Me.Range("B:B")) Is Nothing, and setApplication.EnableEvents = Falsewhile the handler writes, or it triggers itself. - When do you need
Set? For object assignments:Set ws = ThisWorkbook.Worksheets("Data"). Leaving it out gives error 91 (object variable not set) or assigns the object's default property instead. - How do you loop through files in a folder?
f = Dir(folder & "*.xlsx"), thenDo While f <> ""...f = Dir...Loop, or the FileSystemObject. Open each withWorkbooks.Open, process it and close it, saving only if you mean to.
Experienced Excel VBA questions: 19–27
- Which run-time errors should you recognise? 9 (subscript out of range: a wrong sheet name or array index), 13 (type mismatch), 91 (object variable not set), 424 (object required) and 1004 (application-defined, often a bad range reference or a protected sheet).
- How does macro security affect sharing files? Office now blocks macros by default in files from the internet that carry the Mark of the Web, a change that reached Current Channel in version 2206 in July 2022 according to Microsoft. Users can unblock a trusted file in its Properties; organisations use Trusted Locations or macros signed by a trusted publisher.
- What are class modules for? Custom objects with their own properties (
Property GetandProperty Let) and methods, and handling events from many controls withWithEvents. - How should UserForms be used? As custom dialogs: show with
UserForm1.Show, validate input before writing to sheets, and prefer hiding the form and reading its values from the calling code over putting all the logic inside the form. - How do you automate other Office apps or databases? Through their object models, such as
CreateObject("Outlook.Application")to create an email, and ADO for database queries. Security prompts and company policies can block some Outlook automation. - VBA vs Office Scripts vs Power Query vs Python in Excel? VBA runs in desktop Excel with the full object model; Office Scripts use TypeScript and are built for Excel on the web and Power Automate; Power Query handles repeatable import and shaping without code; Python in Excel runs Python in Microsoft's cloud for analysis. Pick by where the file lives and who maintains it.
- How do you debug VBA? Breakpoints with F9, stepping with F8, the Immediate window (
Debug.Print,?variable), Locals and Watch windows, and Debug, Compile VBAProject to catch errors before running. - When is VBA the wrong tool? For hundreds of thousands of rows or repeatable data imports, Power Query, a database or Python usually beats VBA. VBA shines at automating Excel itself: formatting, workflows, reports and user interaction.
- How do you write maintainable VBA? Option Explicit, meaningful names, short procedures, constants instead of magic numbers, Excel tables (
ListObjects) or named ranges instead of hard-coded addresses, error handling, and comments that explain why.
Excel VBA scenario and troubleshooting questions: 28–30
- A macro that took 5 seconds now takes 5 minutes. More rows plus cell-by-cell writes, formulas recalculating on every write, or event handlers firing. Switch to arrays, turn off screen updating, calculation and events during the run, and time sections with
Timerto find the slow part. - Run-time error 9 appears only on a colleague's computer. A sheet or workbook name differs (a renamed tab, a different file name, or ActiveWorkbook pointing at another file). Use ThisWorkbook and sheet code names such as
Sheet1, or check the sheet exists first. - After an error, Excel is left in manual calculation with screen updating off. The macro stopped before restoring settings. Put the restore lines in an exit block that both the normal path and the error handler reach, so they always run.
How interviews test Excel VBA
| Round | What they ask | What a strong answer shows |
|---|---|---|
| Screen | Sub vs Function, Option Explicit, ranges, macro security | Correct terms and safe habits |
| Practical test | Consolidate sheets, remove duplicates, build a summary report | Working code without Select, with error handling |
| Code review | Read a slow or buggy macro and improve it | Spotting cell-by-cell loops and missing restores |
| Senior or finance automation | When to use VBA vs Power Query or Python, maintenance | Choosing tools and handing code over cleanly |
Candidates for finance and analyst roles commonly report a timed practical test in a workbook. Pair VBA practice with formula skills from Excel interview questions, and if the role involves pulling data, SQL interview questions. Finance-specific context is in financial analyst interview questions, and reporting roles in data analyst interview questions.
Where CoPilot Interview fits
CoPilot Interview is an AI interview assistant whose free AI mock interview builds Excel VBA and spreadsheet automation questions from your résumé and the job description, reads them aloud and scores each spoken answer out of 10, which is a quick way to find the answers above that you can't yet explain without notes. The free plan gives 15 minutes a day of live help in sessions of up to 15 minutes, with AI mock interviews included. The browser demo at /try has 145 practice questions in 10 tracks with typed answers scored out of 10, no signup needed. Paid plans are Standard at $24.99 a month and Pro at $39.99 a month, and the desktop app runs on Windows 10 and 11 and macOS 12 or later. Technical screens, take-home tasks, online assessments and proctored tests usually forbid outside help, so prepare for those rather than use any tool during them; in any live interview, follow your employer's rules.
Disclosure: CoPilot Interview is our product, so weigh what we say about it accordingly.
Frequently asked questions
What are the most common Excel VBA interview questions?
Sub vs Function, Option Explicit, ByRef vs ByVal, Range vs Cells, finding the last row, arrays for speed, error handling, events and macro security.
Is VBA still worth learning in 2026?
For desktop Excel automation in finance, operations and reporting, yes; many workbooks still depend on it. Knowing when Office Scripts, Power Query or Python is the better choice also helps in interviews.
Why are macros blocked when I open a file from email?
Office blocks macros by default in files from the internet that carry the Mark of the Web. If you trust the file, unblock it in its Properties or save it to a Trusted Location.
How do I prepare for an Excel VBA test?
Practise three tasks against the clock: combine sheets into one table, clean and de-duplicate data, and produce a formatted summary. Then refactor each to use arrays and error handling.
Practice this interview before the real one
The AI mock interview builds questions from your resume and the job description, reads them aloud and scores each spoken answer out of 10. Included on the free plan.
Start a mock interview