Excel VBA Error Messages: Causes, Codes, and Fixes

VBA & macros · Updated November 19, 2026

An Excel VBA error message means the macro could not compile, run an instruction, or complete an operation as written. Read the message and note the highlighted line first; then use the error number, the code around that line, and the VBA editor’s debugging tools to find the cause.

What Excel VBA error messages mean

VBA errors generally fall into three groups, and identifying the group helps narrow down what to check. A compile error is detected before the affected procedure can run. Examples include a missing End If, an undeclared name when Option Explicit is in use, or a misspelled keyword. The editor usually identifies the relevant line, although the underlying mistake can be just before it.

A run-time error happens after a macro starts. VBA displays a message and may offer Debug, which pauses execution and highlights the line that failed. The instruction may be valid VBA but fail with the current workbook, data, or Excel settings. For example, a macro can refer to a worksheet named “Data” even though the active workbook does not contain that sheet.

A logic error is different: the macro runs without showing an error, but produces the wrong result. Debugging still helps, but the code must be checked against the intended result rather than relying on an error message to point to the problem. A macro that uses the wrong worksheet or processes one row too few can be logically wrong and error-free.

How to read the message and locate the cause

When a run-time message appears, record its number and exact wording. Choose Debug to pause on the failing line. The highlighted line is where VBA detected the problem, but the cause may be an earlier value or object assignment. If the dialog offers only End, the code may not be running in a state where it can enter the debugger, or an error handler may have intercepted the error.

In Excel for Microsoft 365 on Windows, press Alt+F11 to open the Visual Basic Editor; on Mac, Option+F11 usually opens it, though some keyboards require the Fn key. In the editor, useful tools include:

  • Debug > Compile VBAProject to identify compile problems in the project.
  • F8 to execute code one statement at a time. On some Mac keyboards, use Fn+F8.
  • View > Immediate Window to inspect or print values while debugging; in Windows, Ctrl+G opens this window.
  • Breakpoints, set by clicking beside a code line, to pause execution at a chosen point.

Use the Locals or Watch windows to inspect variable values when stepping through complicated code. Ask what the highlighted statement expects: a worksheet, a range, a number, or a particular kind of value? Then check whether the actual object or data meets that expectation. A blank cell, unexpected text, or a differently named workbook can explain an error that looks mysterious at first.

Common VBA error numbers and their usual causes

The message text matters as much as the number. Several errors have multiple possible causes, so treat these descriptions as starting points rather than guaranteed diagnoses.

  • Error 9, “Subscript out of range”: VBA cannot find an item by the name or index used. Check worksheet, workbook, and array names, including spelling and whether the code refers to ThisWorkbook or ActiveWorkbook.
  • Error 13, “Type mismatch”: A value does not match the type the code expects. A cell containing text may be used as a number, or a variable may receive an incompatible value. Inspect the actual cell contents and variable declarations.
  • Error 91, “Object variable or With block variable not set”: An object variable has not been assigned a usable object. Check that a Set statement ran and that the object lookup succeeded before using it.
  • Error 1004, “Application-defined or object-defined error”: This broad error often involves an invalid range, worksheet operation, or Excel object method. Check the full expression, the selected workbook and sheet, and whether the requested operation is permitted in the current state.
  • Error 424, “Object required”: Code is using an object property or method where VBA expected an object but did not receive one. Review the variable type and the statement that supplies the object.
  • Error 438, “Object doesn’t support this property or method”: The referenced object does not provide the named member. Confirm the object type and check for a misspelled property or method.
  • Error 6, “Overflow”: A numeric value is outside the range that the variable type can hold. Check the size of the value and whether a different numeric type is appropriate.

For example, error 9 on a line referring to Worksheets("Data") points toward a missing or misnamed sheet in the workbook being referenced. Error 13 on a line converting a cell to a number suggests checking that cell for text, an error value, or an unexpected blank. The line and its inputs are the key evidence.

A practical debugging process

Work from the first failure instead of changing several lines at once. These steps help preserve useful evidence and make it easier to confirm a fix:

  1. Save a separate copy of the workbook before changing or running a macro that edits data. VBA actions cannot be reversed with Ctrl+Z.
  2. Write down the complete error text and number, then choose Debug if available.
  3. Check the highlighted line and the values used by that line. Confirm the intended workbook, sheet, range, and variable contents.
  4. Step through preceding statements with F8 to see where an unexpected value first appears. On Mac, the equivalent function-key behavior can depend on keyboard settings.
  5. Make one targeted change, run the macro on the copy, and verify the output—not just the absence of an error.

A useful habit is to qualify worksheet and range references. For instance, ThisWorkbook.Worksheets("Data").Range("A1") identifies the workbook containing the macro, rather than relying on whichever workbook happens to be active. That is helpful when several workbooks are open. It is not always the right choice: if a macro is intentionally meant to work on the user’s active workbook, use that reference deliberately and check that the active workbook is the expected one.

If the error occurs only for certain records, inspect those rows and test boundary cases: an empty sheet, a blank cell, a single-row range, and values at the beginning or end of the expected range. Many failures come from assumptions about data that are true for typical rows but not every row.

Using VBA error handling responsibly

An error handler can show a useful explanation or direct the procedure to a controlled cleanup step. It should not hide a problem that needs fixing. A handler such as On Error Resume Next tells VBA to continue after an error; used broadly, it can let a macro produce incomplete or misleading results. Prefer a handler that applies to a defined procedure and reports the actual error.

This example looks for a worksheet named Data and reports the last used row in column A. If that worksheet is missing, its error handler displays the number and description. Before running any macro, save a copy of the workbook: macros cannot be undone with Ctrl+Z.

Sub CheckDataSheet()
    Dim ws As Worksheet
    Dim lastRow As Long

    On Error GoTo ErrorHandler

    Set ws = ThisWorkbook.Worksheets("Data")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    MsgBox "Last used row in column A: " & lastRow, vbInformation
    Exit Sub

ErrorHandler:
    MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation
End Sub

Err.Number and Err.Description identify the error that was raised. The example’s Exit Sub prevents successful execution from falling through into the error-handler section. For more complex procedures, use clear cleanup and exit paths, and avoid replacing the original error details with a generic message. Test the handler by checking both the normal case and a predictable failure, such as renaming the sheet in a disposable copy.

Workbook, platform, and macro-security considerations

VBA macros run in the desktop versions of Excel, not Excel for the web. A workbook containing macros should normally be saved as an Excel Macro-Enabled Workbook (.xlsm); saving it as .xlsx removes its VBA project. If a macro works on Windows but fails on Mac, check for Windows-only components, operating-system calls, or ActiveX controls, which are not supported in the same way on Mac. The Excel object model and available capabilities can also differ, so platform-specific code may need an alternative.

Security settings can prevent a macro from running, which is different from a VBA run-time error. On Windows, macro controls are available under File > Options > Trust Center > Trust Center Settings > Macro Settings. Follow your organization’s policy and only enable code from a source you trust; lowering macro security globally is not a good debugging shortcut. A blocked or disabled macro may need to be handled through approved file and security procedures rather than by changing the VBA code.

Finally, distinguish a macro that will not start from one that starts and then fails. If the macro is absent from the Macros list, check the file type, macro location, and whether the correct workbook is open. If it starts and displays an error, use the message and highlighted line to investigate the procedure itself.

Frequently Asked Questions

What should I do when Excel VBA shows a Run-time error?

Note the error number and wording, choose Debug, and inspect the highlighted statement and the values it uses. Step through earlier code if necessary to find where an unexpected value or missing object first appears.

Why does VBA say “Subscript out of range”?

Usually, the code requests a workbook item that cannot be found, such as a worksheet with a different name or an array element outside its bounds. Check the exact name, workbook reference, and index used on the highlighted line.

How do I stop a VBA error message from appearing?

Fix the condition causing the error or handle a specific expected error with a deliberate error handler. Avoid suppressing every error with On Error Resume Next; that can conceal failures and allow bad results to pass unnoticed.

Can I undo a VBA macro with Ctrl+Z?

No. Excel’s normal Undo history does not reverse changes made by a VBA macro. Save a separate copy before running code that changes data, and verify the macro’s effect on that copy first.

Can Excel for the web run VBA macros?

No. Excel for the web does not run VBA macros. Open the macro-enabled workbook in the desktop Excel app to run VBA, subject to the workbook’s security settings and your organization’s policies.

Related

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.