Excel VBA Interview Questions With Model Answers

Published · by JOBB GROUP FZE

Excel VBA interviews tend to test three things: whether you understand the language, whether you write code that other people can trust, and whether you know when VBA is the wrong tool. The questions below are grouped from fresher to experienced. Each one says what the interviewer is checking and gives a model answer. The model answers and code samples are illustrative, written for this guide; adapt them to your own experience rather than reciting them.

Many interviews also include a live task, such as “write a macro that copies every row marked ‘Overdue’ to a new sheet”. Practise typing code without autocomplete help, because some test environments have it turned off.

Questions for freshers

1. What is VBA, and where does the code live in a workbook?

What they are checking: that you have used the Visual Basic Editor, not just recorded macros.

Model answer: “VBA is the programming language built into desktop Office applications. In Excel, I write code in the Visual Basic Editor. General procedures go in standard modules. Code that responds to workbook events, such as opening the file, goes in the ThisWorkbook module, and code that responds to a particular sheet, such as a cell changing, goes in that sheet’s module. Reusable objects can go in class modules. A workbook containing macros has to be saved in a macro-enabled format such as .xlsm.”

2. What does Option Explicit do, and why use it?

What they are checking: basic discipline.

Model answer: “It forces every variable in the module to be declared with Dim, Private, Public, ReDim or Static. It must appear before any procedures in the module. Without it, a mistyped variable name silently becomes a new Variant variable, and the bug can be hard to find. With it, the mistake becomes a compile error. I set the editor option that adds it to every new module automatically.”

Microsoft’s language reference confirms the key points: Option Explicit applies at module level, must come before any procedures, and without it undeclared variables are of Variant type.

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

Model answer: “Both are procedures. A Function returns a value, so it can be used in an expression and, if it is in a standard module and only calculates, it can be called from a worksheet cell as a user-defined function. A Sub does not return a value; it performs actions, such as formatting a report. I use Functions for calculations I want to reuse and Subs for processes.”

4. What is the difference between ByRef and ByVal?

What they are checking: whether you understand side effects.

Model answer: “By default, VBA passes arguments by reference, so if the called procedure changes the argument, the caller’s variable changes too. ByVal passes a copy, so changes inside the procedure stay inside. I use ByVal when a procedure should not alter the caller’s data, which makes code safer to reuse.”

This matches Microsoft’s documentation, which states that all arguments are passed by reference unless you specify otherwise, and that passing by value copies the original variable so changes are not reflected back.

5. How do you find the last used row in a column?

Model answer: “The usual approach is to start from the bottom of the sheet and go up to the first non-empty cell. I qualify it with the worksheet so it does not depend on which sheet is active.”

Dim ws As Worksheet
Dim lastRow As Long
Set ws = ThisWorkbook.Worksheets("Data")
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

A good follow-up point: declare row counters as Long rather than Integer. Microsoft’s reference gives the Integer range as -32,768 to 32,767, far below the number of rows in a modern worksheet.

6. Why do experienced developers avoid Select and Activate?

Model answer: “Recorded macros select cells before acting on them, which is slow and fragile. If the user clicks somewhere else, or the code runs with a different sheet active, it can act on the wrong range. It is better to reference objects directly, for example ws.Range(“B2″).Value = 0, without selecting anything.”

Microsoft’s Excel team has made the same point: its VBA performance guidance recommends avoiding selection and referencing objects directly.

7. How do you handle errors in VBA?

What they are checking: that you do not just put On Error Resume Next at the top of everything.

Model answer: “I use On Error GoTo with a labelled handler at the end of the procedure, and an Exit Sub just before the label so the handler does not run when there is no error. In the handler I read Err.Number and Err.Description, log or show a useful message, and tidy up. I use On Error Resume Next only around a single line where I expect a possible error, such as checking whether a sheet exists, then check Err.Number straight away and switch normal handling back on with On Error GoTo 0.”

Sub ImportFile()
    On Error GoTo Handler
    ' ... main code ...
    Exit Sub
Handler:
    MsgBox "Import failed: " & Err.Number & " - " & Err.Description
End Sub

Microsoft’s On Error reference describes each form this way, including the advice to put Exit Sub, Exit Function or Exit Property immediately before the handler.

Questions for experienced candidates

8. A macro takes ten minutes on 50,000 rows. How do you speed it up?

What they are checking: whether you know why VBA is slow, not just a list of settings.

Model answer: “First I would find out where the time goes, but the usual cause is the code reading or writing cells one at a time. Each trip between VBA and the worksheet is expensive. I would read the whole range into a Variant array, process the array in memory and write the result back in one operation. Then I would switch off screen updating, set calculation to manual and turn off events while the code runs, and restore them at the end, including in the error handler, so a failure does not leave Excel in manual calculation.”

Sub FastUpdate()
    Dim data As Variant, i As Long
    On Error GoTo CleanUp
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Application.EnableEvents = False

    data = Worksheets("Data").Range("A2:D50001").Value2
    For i = 1 To UBound(data, 1)
        data(i, 4) = data(i, 2) * data(i, 3)
    Next i
    Worksheets("Data").Range("A2:D50001").Value2 = data

CleanUp:
    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
    If Err.Number <> 0 Then MsgBox Err.Description
End Sub

These techniques appear in Microsoft’s own guidance. A Microsoft Excel team post on VBA performance recommends turning off screen updating, calculation and events while code runs, and working on whole ranges through arrays. A later Microsoft Tech Community post (2018), compiled by a Microsoft employee with two Excel MVPs, adds using With blocks, using .Value2 rather than .Text or .Value, and bypassing the clipboard by assigning values directly instead of copying and pasting.

Strong candidates add one caution: setting calculation back to automatic triggers a recalculation, so the restore step itself can take time on a heavy workbook.

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

Model answer: “.Text returns what is displayed in the cell, so it depends on formatting and column width; a too-narrow column can return ####. .Value returns the underlying value, but converts dates and currency-formatted cells into VBA Date and Currency types. .Value2 returns the raw value without those conversions, so a date comes back as its serial number. .Value2 is usually the fastest and the most predictable for bulk work.”

Microsoft’s Range.Value2 reference states that the only difference from .Value is that .Value2 does not use the Currency and Date data types, and the Tech Community performance post describes .Text as slow and .Value2 as faster than .Value.

10. How do you run code when a user changes a cell, without causing an endless loop?

Model answer: “I use the Worksheet_Change event in the sheet’s module, check whether the changed cells intersect the range I care about, and disable events while my code writes to the sheet, because otherwise my own change fires the event again.”

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Me.Range("C2:C500")) Is Nothing Then Exit Sub
    On Error GoTo SafeExit
    Application.EnableEvents = False
    Target.Offset(0, 1).Value = Now
SafeExit:
    Application.EnableEvents = True
End Sub

Interviewers often ask what happens if the code fails between the two EnableEvents lines. The answer is the handler: without it, events stay off for the whole Excel session.

11. Users say your macro workbook “won’t run” after they download it. Why?

What they are checking: awareness of macro security, which affects every VBA deployment.

Model answer: “Office for Windows blocks macros in files that carry the Mark of the Web, which Windows adds to files from the internet, such as downloads and email attachments. Users see a Security Risk banner with no Enable Content button. For a single file, the user can unblock it in the file’s Properties. For a team, the better options are a carefully managed trusted location or signing the code with a certificate the organisation trusts, rather than asking people to unblock files routinely.”

Microsoft’s deployment documentation describes this behaviour and these options, and notes that it applies to Office on Windows, not Office on Mac or the web.

12. How do you debug a macro that gives the wrong result but no error?

Model answer: “I set a breakpoint before the suspect section and step through with F8, watching values in the Locals window. I use Debug.Print to write values to the Immediate window, and a Watch to break when a variable takes an unexpected value. I also test on a small copy of the data where I know the right answer.”

13. When would you not use VBA?

What they are checking: judgement, which separates senior candidates.

Model answer: “VBA is a desktop tool. It does not run in Excel on the web, and it has no Power Automate connector, so it needs a person to start it. If the workbook lives in SharePoint or Teams and the process should run on a schedule or be triggered by another system, Office Scripts with Power Automate may be a better fit. If the job is mainly importing and reshaping data, Power Query may do it without code. VBA still makes sense for rich desktop automation, interaction with other Office applications and older processes that work well.”

Microsoft’s comparison of Office Scripts and VBA sets out these differences, including that VBA macros have the same security clearance as Excel on the desktop, while Office Scripts only have access to the workbook.

14. Spot the bug

Interviewers often show a short snippet. This one is illustrative:

Sub DeleteBlankRows()
    Dim i As Long
    For i = 2 To 100
        If Cells(i, 1).Value = "" Then Rows(i).Delete
    Next i
End Sub

Model answer: “When a row is deleted, the rows below move up, so the loop skips the next row, and two blanks in a row leave one behind. The fix is to loop from the bottom up, For i = 100 To 2 Step -1. I would also qualify Cells and Rows with a worksheet, and find the last row instead of hard-coding 100.”

How to prepare in the week before

  • Rebuild one of your own macros from memory. If you can explain every line, you can handle follow-up questions.
  • Practise three classic tasks: loop through files in a folder and combine them; split one sheet into several by a column value; and produce a formatted report from raw data.
  • Prepare one story of a macro that saved time. Give the before and after, how you tested it and who maintains it now.
  • Know the boundaries: Power Query, PivotTables and Office Scripts, and when each beats VBA.

If your CV lists VBA, make the claim specific, for example “automated the month-end reconciliation pack in VBA, cutting preparation from two days to three hours”, using your own real figures. The financial analyst resume builder and the resume examples show how to set out technical tools alongside results.

FAQ

Is VBA still worth learning?

Many finance, operations and reporting teams still run processes built in VBA, and roles that maintain them ask for it. Learning Power Query and Office Scripts as well widens the jobs you can apply for.

What VBA questions are asked of freshers?

Expect basics: modules, Sub versus Function, variables and Option Explicit, loops, referencing ranges without Select, and simple error handling.

What do experienced candidates get asked?

Performance with arrays, events, error handling that restores application settings, macro security and deployment, debugging, and when another tool would be better.

Will I have to write code in the interview?

Often, yes, either on a laptop or on paper. Ask the recruiter in advance so you can practise in the same format.

Sources

Ready to build your resume?

Start with a sample or a blank page. No sign-up needed. Free to edit; US$1.00 only if you download a PDF.

Build My ATS ResumeExplore Resume Templates