Excel VBA error numbers are numeric identifiers that help you diagnose a problem when a macro fails; the number and its description are available through VBA’s Err object. Common examples include 9 for Subscript out of range, 13 for Type mismatch, and 1004 for a broad Application-defined or object-defined error, but the number alone rarely explains the exact cause.
What an Excel VBA error number means
When VBA encounters a runtime error, it records details about that failure in the Err object. The most useful properties are Err.Number, the numeric code; Err.Description, a text explanation; and Err.Source, the application or code component that reported it. Err.Number is 0 when no error is currently recorded.
An error number is a clue, not a complete diagnosis. The same number can arise in different parts of a macro, and the surrounding code determines what operation failed. For example, error 1004 might occur while setting a cell value, selecting a range, or changing a worksheet property. The line that failed, the object involved, and the exact description are all important.
VBA runtime errors are also different from compile errors. A misspelled variable or invalid statement can stop VBA before a procedure runs; that is a compile-time problem, not a runtime error with an Err.Number you can handle in the procedure. Fix the highlighted code or declaration first, then investigate runtime errors that occur while the macro is executing.
Common VBA error numbers and causes
These frequently encountered numbers are useful starting points. Descriptions are the familiar VBA wording, though an application or object may supply additional detail.
- 5 — Invalid procedure call or argument: A procedure received an argument outside its allowed range or format. Check values passed to functions, methods, and file operations.
- 6 — Overflow: A value cannot fit in the variable’s data type. Check calculations and consider whether a variable needs a wider type, such as
Longinstead ofInteger. - 9 — Subscript out of range: Code referred to an item that does not exist in a collection or array. Check worksheet and workbook names, array bounds, and collection indexes.
- 11 — Division by zero: A calculation attempted to divide by zero. Check the divisor before performing the calculation.
- 13 — Type mismatch: A value cannot be used as the type the code expects. Inspect variable declarations, cell contents, and values returned by functions.
- 52 — Bad file name or number: A file operation used an invalid name, path, or file number. Verify the path and how the file was opened.
- 53 — File not found: The requested file could not be located at the supplied path. Confirm the filename, folder, and whether the file is available.
- 91 — Object variable or With block variable not set: An object variable was used before it was assigned a valid object with
Set, or the object is unavailable. - 1004 — Application-defined or object-defined error: Excel or an object raised a broad error. Find the exact failing statement and check its range, worksheet, workbook, and current state.
- 424 — Object required: Code expected an object but received something else or no usable object.
- 438 — Object doesn’t support this property or method: The requested member is not available on that object. Check the object type and spelling of the property or method.
This list is not exhaustive. A third-party component or another Office application can report additional errors, and a familiar number such as 1004 often needs the code line and description to make sense.
Where the number comes from
VBA has built-in runtime errors, while Excel’s object model can report errors when a requested action is invalid in the current context. For instance, trying to use a worksheet name that is absent can produce error 9. An invalid operation on an Excel range may produce error 1004. A message such as 1004 does not identify one single fault; it is a general report from the application or object involved.
The Err object describes VBA runtime failures. It should not be confused with an error value displayed in a worksheet cell, such as #N/A, #VALUE!, or #DIV/0!. A cell error is a value in Excel’s calculation system. VBA can inspect such a value with functions such as IsError, while a runtime failure is described through properties such as Err.Number and Err.Description. Depending on how code calls Excel’s worksheet functions, an invalid calculation may appear as a cell error or raise a VBA runtime error.
When VBA communicates with external components through automation, the reported number can also be a component-specific error rather than one of the common built-in examples. For custom errors, code can use Err.Raise with a number based on vbObjectError. That gives a project a way to distinguish its own failure conditions from ordinary VBA errors.
How to find the real cause
Start by identifying the exact statement that failed. If the error dialog offers Debug, choose it to open the Visual Basic Editor and highlight the line. In the editor, press F8 on Windows to step through a procedure one line at a time. The equivalent key may depend on the Mac keyboard and function-key settings; the Visual Basic Editor is available in Excel for Mac, but VBA macros cannot run in Excel for the web.
Inspect the objects and values used on the failing line. If it refers to a worksheet, confirm that the workbook contains a sheet with exactly that name. If it uses a range, check that the range belongs to the intended workbook and worksheet. If it reads a cell into a numeric variable, check for blanks, text, or worksheet error values. For error 91, look for an object variable that was declared but never assigned with Set.
You can also examine the error details in a handler or print them to the Immediate window. In the Visual Basic Editor, press Ctrl+G on Windows to show the Immediate window. On a Mac, use the editor’s View menu to show it. Record the error number, description, source, and the line that failed. Those details are more useful than searching for a number without knowing what the macro was doing.
Handle runtime errors with a clear handler
Use an error handler when a macro needs to respond to a failure, report useful information, or clean up resources. The following example catches a missing worksheet or another runtime error and displays the recorded number and description. Before running any macro, save a copy of the workbook: changes made by VBA generally cannot be undone with Ctrl+Z.
Sub WriteReportDate()
Dim ws As Worksheet
Dim message As String
On Error GoTo HandleError
Set ws = ThisWorkbook.Worksheets("Summary")
ws.Range("A1").Value = Date
Exit Sub
HandleError:
message = "Error " & Err.Number & ": " & Err.Description
Debug.Print message
MsgBox message, vbExclamation, "Macro error"
End Sub
On Error GoTo HandleError directs execution to the named handler if a runtime error occurs. The Exit Sub prevents successful execution from falling through into the handler. The handler builds its message before displaying it, so it preserves the error information the macro needs to report. In a larger procedure, it is often useful to include Err.Source as well.
Use On Error Resume Next sparingly. It tells VBA to continue at the statement immediately following the one that failed, which can conceal a problem if the code does not check whether an error occurred. If you use it for a specific operation, check Err.Number straight away, handle or record the result, and then restore normal error handling. Err.Clear clears the recorded error details; it does not make a failed operation successful.
Common troubleshooting mistakes
- Assuming one number has one cause: Error 1004 is especially broad. Read the description and inspect the exact statement rather than applying a generic fix.
- Ignoring workbook context:
ActiveWorkbookandActiveSheetdepend on what is currently active. A macro can act on the wrong workbook if focus changes. Use explicit references such asThisWorkbook.Worksheets("Summary")when that is the intended file. - Suppressing all errors: Leaving
On Error Resume Nextactive across many lines can let a macro continue with missing objects or incomplete results. Limit its scope and check the outcome. - Confusing the displayed number with a worksheet error: A cell containing
#VALUE!is not the same thing as VBA error 13. Determine whether execution stopped or the cell merely contains an error result. - Changing data types without checking values: A wider numeric type may address an overflow, but it will not fix text in a cell that causes a type mismatch. Confirm the actual input before changing declarations.
Choosing how to report or recover
Not every error should be handled in the same way. If a required worksheet is missing, a clear message and a safe stop may be better than continuing. If a file is optional, the macro might report that it was skipped and continue with other files. If a temporary resource was opened, the handler may need to close it before exiting. Choose a response based on whether the remaining work can still produce a reliable result.
For reusable procedures, consider reporting both the number and description, and include context such as the worksheet or filename being processed. If a procedure handles an error only to add context, it can raise an error again with Err.Raise; custom errors can use a number based on vbObjectError. Avoid returning a normal-looking result after an operation failed, because that can make incomplete work appear successful. Test error paths with a copy of the workbook and data, not the only working file.
Frequently Asked Questions
What does VBA error 1004 mean in Excel?
It means Excel or an Excel object reported an Application-defined or object-defined error. It is a broad code, so check the highlighted statement, the full description, and the workbook, worksheet, or range involved.
How do I display the current VBA error number?
Inside an error handler, read Err.Number. To show both the number and its description, combine Err.Number and Err.Description in a message or print them to the Immediate window.
What does error 9 mean in VBA?
Error 9 is Subscript out of range. It commonly means code requested a worksheet, workbook, array element, or collection item that does not exist at that index or under that name.
Is a VBA error number the same as an Excel cell error?
No. A VBA runtime error is reported through the Err object. A cell error such as #N/A is a worksheet value. Code may encounter or inspect cell errors, but the two systems are not interchangeable.
Can I run VBA macros in Excel for the web?
No. Excel for the web can preserve a workbook containing macros, but it cannot create, edit, or run VBA macros. Use the Excel desktop application for VBA.
Related
- Excel Shortcut to Select to the Bottom of a Column
- Excel Shortcut to Select All Rows in a Column at Once
- Excel Data Validation from a List: Setup and Troubleshooting
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel Macro to Generate a Chart Automatically in VBAYes. In desktop Excel for Microsoft 365 on Windows, a VBA macro can create a chart from a…
- VBA Sort Collection: Methods and Practical ExamplesVBA’s Collection object has no built-in sort method. To sort a collection, copy its items into an array,…
- Excel VBA Name Errors: Causes and FixesThere is no single standard Excel VBA message called “Name error.” The phrase usually refers to a name-related…
- Excel VBA Optional Parameters: Syntax and ExamplesYes. In Excel VBA, declare an optional parameter with the Optional keyword, usually followed by a default value,…
- Excel VBA Else If: Syntax, Examples, and AlternativesIn Excel VBA, the correct keyword for a chain of conditions is ElseIf, written as one word—not Else…
- TinyTask Macro: How to Record, Save, and Run TasksTo set up a TinyTask macro on Windows, open TinyTask, click Record, carry out the actions you want…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.