Home › Blog › Excel VBA Interview Questions (2026): 30 Questions on Ranges, Loops, Errors and Speed

Excel VBA Interview Questions (2026): 30 Questions on Ranges, Loops, Errors and Speed

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.

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

  1. 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.
  2. 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.
  3. What type is a in Dim a, b As Long? a is a Variant; only b is Long. Declare each one: Dim a As Long, b As Long.
  4. ByRef vs ByVal? VBA passes arguments by reference unless you say otherwise, so a procedure can change the caller's variable. Use ByVal when you want a copy.
  5. Range vs Cells? Range("A1:C10") takes address text; Cells(row, column) takes numbers, which suits loops: ws.Cells(i, 2).Value. Combine them with ws.Range(ws.Cells(1, 1), ws.Cells(lastRow, 3)).
  6. Why avoid Select and Activate? They are slow and fragile because they depend on what is active. Reference objects directly: ThisWorkbook.Worksheets("Data").Range("A1").Value = 5.
  7. How do you find the last used row? lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row returns the last non-empty cell in column A. UsedRange can include formatted but empty cells, so it may overshoot.
  8. ThisWorkbook vs ActiveWorkbook? ThisWorkbook is the file that contains the code; ActiveWorkbook is whatever the user has in front. Code that works on its own file should always use ThisWorkbook.
  9. Which file types keep macros? .xlsm, .xlsb (binary) and .xlam (add-in); .xlsx can't store VBA. Macros for every workbook usually live in PERSONAL.XLSB.

Intermediate Excel VBA questions: 10–18

  1. 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 = xlCalculationManual and Application.EnableEvents = False, restoring all three on exit.
  2. Show the array pattern. Dim v As Variant: v = ws.Range("A2:A10001").Value returns a two-dimensional, 1-based array; loop For i = 1 To UBound(v, 1), change v(i, 1), then write back with ws.Range("A2:A10001").Value = v. Ten thousand writes become one.
  3. What is a good error-handling pattern? On Error GoTo ErrHandler at the top, normal code, Exit Sub, then an ErrHandler: block that logs Err.Number and Err.Description and restores settings. Use On Error Resume Next only around one expected failure, then On Error GoTo 0.
  4. WorksheetFunction.VLookup vs Application.VLookup? The first raises a run-time error when the value isn't found; the second returns an error value you can test with IsError(). For many repeated lookups, a Dictionary is faster.
  5. What is Scripting.Dictionary for? Fast key-value lookups and de-duplication: Set d = CreateObject("Scripting.Dictionary"), then If Not d.Exists(k) Then d.Add k, v. It comes from the Windows Scripting Runtime, so it isn't available on a Mac.
  6. Early vs late binding? Early binding adds a reference (Tools, References) and declares specific types, giving IntelliSense and slightly faster calls; late binding uses CreateObject with Object variables and avoids version-specific references when you share files.
  7. How do worksheet events work? Workbook_Open goes in ThisWorkbook and Worksheet_Change(ByVal Target As Range) in the sheet's module. Check Not Intersect(Target, Me.Range("B:B")) Is Nothing, and set Application.EnableEvents = False while the handler writes, or it triggers itself.
  8. 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.
  9. How do you loop through files in a folder? f = Dir(folder & "*.xlsx"), then Do While f <> "" ... f = Dir ... Loop, or the FileSystemObject. Open each with Workbooks.Open, process it and close it, saving only if you mean to.

Experienced Excel VBA questions: 19–27

  1. 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).
  2. 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.
  3. What are class modules for? Custom objects with their own properties (Property Get and Property Let) and methods, and handling events from many controls with WithEvents.
  4. 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.
  5. 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.
  6. 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.
  7. 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.
  8. 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.
  9. 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

  1. 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 Timer to find the slow part.
  2. 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.
  3. 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

RoundWhat they askWhat a strong answer shows
ScreenSub vs Function, Option Explicit, ranges, macro securityCorrect terms and safe habits
Practical testConsolidate sheets, remove duplicates, build a summary reportWorking code without Select, with error handling
Code reviewRead a slow or buggy macro and improve itSpotting cell-by-cell loops and missing restores
Senior or finance automationWhen to use VBA vs Power Query or Python, maintenanceChoosing 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