Excel VBA error codes identify problems that stop a macro or signal that an operation failed. The most useful starting point is the VBA Err.Number property: it gives the numeric code, while Err.Description supplies a short explanation; common examples include 9 (Subscript out of range), 13 (Type mismatch), and 1004 (an Excel application or object error).
What Excel VBA error codes mean
VBA errors are not all the same kind. A compile error is found before the procedure can run, while a runtime error occurs as the code is executing. A third category is an application-level failure: Excel may return a general error when a request involving a workbook, worksheet, range, chart, or other Excel object cannot be completed. The phrase “VBA error code” most often refers to the number associated with a runtime error.
Codes are clues, not complete diagnoses. The same number can arise from different lines or circumstances, and the description may not explain which assumption in the code was wrong. For example, error 9 usually means that code asked for an item that does not exist in a collection, but the missing item might be a worksheet, workbook, or array element. The line highlighted by the debugger and the values used on that line matter as much as the code itself.
Do not confuse VBA runtime codes with worksheet error values such as #VALUE!, #REF!, or #N/A. Those are results displayed by worksheet formulas. A macro can read or write cells containing those values, but they are not the same system as the number returned by VBA’s Err.Number.
How the Err object reports an error
When a VBA statement fails, the built-in Err object holds information about the most recent error. Its Number property is the numeric code, and Description is a text explanation. Source can identify the application or object that raised the error. The object also exposes properties such as HelpFile and HelpContext, which may be useful in some applications.
Read the error details promptly. A later operation can change the contents of Err, so a handler that needs to log or report the original failure should capture the number and description before doing other work. Err.Clear clears the current error information. It does not fix the underlying problem, and clearing an error too early can make diagnosis harder.
In the Visual Basic Editor, a runtime error may display a dialog with options such as Debug or End. Choosing Debug highlights the statement that failed, which is often the quickest route to the cause. You can also inspect variables in the Locals or Watch windows while execution is paused. The number in the dialog is useful, but the highlighted statement and its inputs provide the context needed to resolve it.
Common Excel VBA runtime error codes
These frequently encountered codes are useful reference points. Exact wording can vary by situation, and an error number alone does not prove which correction is appropriate.
- 5 — Invalid procedure call or argument: A method or function received an argument it cannot use, or the requested operation is not valid in the current state. Check the argument’s range and the object’s requirements.
- 6 — Overflow: A value is outside the range supported by the variable type or calculation. Check the declared type and intermediate arithmetic; a larger type may be appropriate if the data genuinely needs it.
- 9 — Subscript out of range: Code requested an array index or collection member that is unavailable. Confirm the index bounds and the exact spelling of workbook or worksheet names.
- 11 — Division by zero: A calculation attempted to divide by zero. Check the divisor before dividing, including whether it is blank or evaluates to zero.
- 13 — Type mismatch: A value cannot be used as the expected data type. Check for text where a number is expected, unexpected empty values, or an object that has not been assigned correctly.
- 53 — File not found: VBA could not locate a file at the specified path. Check the full path, file name, extension, and whether the file is available.
- 70 — Permission denied: The requested file or operation is not accessible with the current permissions or state. The file may be protected, in use, or located somewhere the user cannot write to.
- 91 — Object variable or With block variable not set: Code tried to use an object reference that has not been assigned. Check that the relevant
Setstatement succeeded before using the object. - 1004 — Application-defined or object-defined error: Excel could not complete a request through its object model. The message is broad; inspect the particular range, workbook, worksheet, or method involved.
- 424 — Object required: A statement expected an object reference but did not receive one. Review the variable declaration, assignment, and whether an object was used where a value was intended.
- 438 — Object doesn’t support this property or method: The selected object does not provide the member being called. Confirm the object’s actual type and whether the member applies to it.
- 429 — ActiveX component can’t create object: VBA could not create the requested automation object. The component may be unavailable, unregistered, or incompatible with the environment.
Some of these are general Visual Basic errors, while others commonly appear when VBA is controlling Excel or another automation component. Error 1004 is especially context-dependent: treat its description and the failing line as essential evidence rather than searching for a single universal fix.
How to find the cause of a VBA error
Start by reproducing the issue with the same workbook, inputs, and steps that triggered it. If the editor opens a runtime error dialog, choose Debug and note the highlighted line. If the macro stops only for some records, inspect the values in those records rather than assuming every row follows the same pattern.
- Read the whole message. Record the error number and description, and note which procedure was running.
- Inspect the failing statement. Check the object, argument, index, path, or value supplied to it. For a range operation, confirm which workbook and worksheet the reference points to.
- Check assumptions immediately before the failure. A worksheet name may be misspelled, a range may be empty, or a variable may contain text instead of a number.
- Step through the code. Use F8 in the Visual Basic Editor to execute one statement at a time, and inspect variables as they change. On a Mac, the key combination can depend on the keyboard and system settings; use the editor’s Debug menu if a function key is intercepted.
- Test a small case. Use a copy of the workbook and a minimal set of representative data. This helps separate a code defect from an unusual input or workbook condition.
For example, if a macro fails on Worksheets("Summary") with error 9, first establish which workbook the unqualified Worksheets collection refers to and whether that workbook has a sheet named exactly “Summary.” A tab that looks similar may contain a trailing space, or the intended sheet may belong to a different workbook. Replacing the reference blindly can hide the real issue.
Handling runtime errors in a macro
VBA provides On Error statements to control what happens after a runtime failure. A targeted handler is usually preferable to suppressing all errors: it lets the macro report a problem while keeping the relevant code and context visible. The example below catches an error in a short procedure and displays its number and description.
Save a copy of the workbook before running or changing a macro. VBA macros cannot be undone with Ctrl+Z, and a procedure may have changed cells before an error occurred.
Sub WriteStatus()
Dim target As Range
On Error GoTo ErrorHandler
' Refer to the intended worksheet explicitly.
Set target = ThisWorkbook.Worksheets("Summary").Range("A1")
target.Value = "Ready"
Exit Sub
ErrorHandler:
MsgBox "VBA error " & Err.Number & ": " & Err.Description, _
vbExclamation, "Macro error"
End Sub
On Error GoTo ErrorHandler sends execution to the named label when a runtime error occurs. The Exit Sub keeps normal execution from falling through into the handler. In a longer procedure, a handler can also record details, release resources, or tell the user what action to take. Keep the handler straightforward: if error-handling code itself fails, the original problem can become harder to understand.
On Error Resume Next tells VBA to continue with the next statement after an error. It can be useful for a narrowly defined operation where failure is expected and checked immediately, but using it across a whole procedure can allow later code to run with incomplete data. If you use it, examine Err.Number after the specific operation and restore normal error handling with On Error GoTo 0 when appropriate. Do not use it merely to make an error dialog disappear.
Custom errors and compile errors
When a macro detects an invalid condition of its own, it can raise a custom error rather than returning an ambiguous result. VBA reserves the vbObjectError range as a starting point for user-defined automation errors. A custom error should have a clear description and be documented so that the code that handles it can distinguish it from built-in errors. Avoid assigning a number that could be confused with a standard error without a deliberate convention.
Compile errors are different: they occur when VBA cannot parse or compile the code, so the procedure does not reach the failing runtime statement. Examples include a misspelled keyword, a missing End If, or a variable that is not declared when Option Explicit is in effect. In the Visual Basic Editor, use Debug > Compile VBAProject to check a project for compile problems. Fix compile errors first, then run the macro to investigate runtime errors.
For Excel for Microsoft 365 on Windows, open the Visual Basic Editor with Alt+F11, or select Developer > Visual Basic on the ribbon. If the Developer tab is not visible, enable it through File > Options > Customize Ribbon. On a Mac, use Tools > Macro > Visual Basic Editor or the Visual Basic command on the Developer tab; keyboard shortcuts can differ by keyboard and macOS settings. Excel for the web does not run VBA macros, so open the workbook in the desktop application to use or debug VBA.
Common mistakes when interpreting error codes
A frequent misconception is that every error number points to one exact correction. In reality, a number narrows the investigation. Error 13 does not automatically mean a particular cell is formatted incorrectly; the failing expression might instead involve a function argument, a conversion, or a variable that contains an unexpected value. Likewise, error 1004 is not a diagnosis by itself.
Another mistake is to add a broad error handler that displays a friendly message but discards the details. A useful report should preserve the number and description, and a development-time investigation should also identify the procedure and line. VBA does not provide a built-in line number for every statement automatically, so projects that need precise production logs can add line numbers or other deliberate logging. Keep those measures proportional to the macro’s purpose.
Finally, do not assume an error is caused by Excel being unreliable. Workbook protection, missing files, external links, changed sheet names, permissions, and unexpected input can all produce legitimate failures. Confirm the workbook state and the data first. An error handler is a way to respond responsibly to a failure, not proof that the underlying operation is safe to ignore.
Frequently Asked Questions
Where can I see the number for an Excel VBA error?
When the runtime error dialog appears, its message usually includes a number. In code, inspect Err.Number in an error handler and Err.Description for the accompanying text. The Visual Basic Editor’s highlighted line helps connect that information to the cause.
What does VBA error 1004 mean?
It is a broad application-defined or object-defined error, often raised during an Excel object-model operation. Check the exact description and failing statement, then verify the workbook, worksheet, range, and arguments used by that statement.
What is the difference between error 9 and error 91?
Error 9 indicates that a requested item or index is outside the available collection or array. Error 91 indicates that code tried to use an object variable that has not been set. One is commonly about a missing member or index; the other is about an unassigned object reference.
How do I stop a VBA error from appearing?
Correct the cause where possible, or use a focused error handler to report and manage an expected failure. Avoid placing On Error Resume Next over a large block just to suppress messages; it can let the macro continue after an operation failed.
Can I undo a macro that stopped with an error?
Usually not with Ctrl+Z. Excel’s undo history is generally cleared when a VBA macro runs, and changes made before the error may remain. Save a copy before running unfamiliar or data-changing code, and check the affected workbook after a failure.
Do VBA error codes work in Excel for the web?
Excel for the web does not execute VBA macros. To run or debug VBA, open the workbook in desktop Excel for Windows or Mac. The web app can open many macro-enabled workbooks, but it cannot run the VBA project.
Related
- Excel Named Ranges: How to Create and Use Them
- Excel Uppercase to Lowercase Shortcut: What Actually Works
- Excel Unmerge Cells Shortcut: Keys, Steps, and Fixes
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA Date and Time Functions: A Practical GuideExcel VBA’s main current-date and time functions are Date, Time, and Now; use DateSerial and TimeSerial to build…
- Excel Macro to Protect a Sheet With a PasswordTo protect the active worksheet with a password in Excel for Microsoft 365 on Windows, run a VBA…
- Excel VBA Automation Error: Causes, Fixes, and Checks“Excel VBA Automation error: Exception occurred” is a broad error from an object that VBA is controlling, often…
- Excel VBA: Loop Through Cells in a RangeTo loop through every cell in an Excel range with VBA, use a For Each loop and a…
- Unload UserForm VBA: Close Forms and Reset State SafelyIn VBA, use Unload UserForm1 to close and unload a form, or use Unload Me from code inside…
- Excel VBA Can’t Find Project or Library? Fix the ErrorIf Excel VBA says “Can’t find project or library,” the VBA project usually has a reference to a…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.