Excel VBA error handling lets a macro respond to runtime problems instead of stopping unexpectedly. Use On Error GoTo Handler for a procedure-level handler, then report or recover from the problem and exit cleanly; use On Error Resume Next only for a small operation whose error you check immediately.
What VBA error handling does
A VBA error is a problem that occurs while code is running, such as trying to use a worksheet that does not exist or converting text that cannot be treated as a number. Without an effective handler, Excel may show a runtime error and stop the macro at the failing statement. A handler gives the procedure a chance to explain the problem, clean up, or take another action.
Error handling does not make faulty code correct, and it cannot guarantee that a macro can recover safely. Its purpose is to make failures understandable and controlled. A good handler identifies the operation that failed, preserves useful information, and avoids leaving the workbook or Excel application in a confusing state.
It is also useful to distinguish runtime errors from other problems. A syntax or compile error, such as a misspelled keyword, must be fixed before the macro can run; an On Error statement cannot catch it. A worksheet cell displaying #VALUE! is an Excel formula error, not automatically a VBA runtime error. VBA can inspect or handle such a cell value, but that is separate from trapping a runtime failure.
How On Error works
VBA provides three common forms of the On Error statement. They change what happens when a runtime error occurs in the current procedure:
On Error GoTo HandlerNameenables the named error-handling section. When an unhandled runtime error occurs in that procedure, execution transfers to the label.On Error Resume Nexttells VBA to continue with the statement immediately after the one that failed. The code must inspect the error before continuing as if the operation succeeded.On Error GoTo 0disables the current procedure’s enabled handler. It does not mean “go to the top” or automatically clear theErrobject’s information.
A handler label is just a label inside the procedure, commonly named ErrorHandler:. After handling an error, the code should normally leave the handler with Exit Sub or Exit Function, or use Resume to continue at a deliberate point. If it simply reaches the end of the handler and falls through into normal code, it may do work that should not run after a failure.
The built-in Err object provides details about the most recent error. Its Number property identifies the error, Description contains a description, and Source can identify the object or procedure that raised it. Capture or display these details before another operation can replace them. Err.Clear clears the stored error information; it is different from disabling a handler.
Choosing the right handling pattern
For most procedures, use On Error GoTo when an error should stop the current task and produce one clear response. This makes the failure path visible in the code and is easier to review than silently skipping unexpected problems. The handler can show a message, write a diagnostic entry, or route execution through a cleanup section.
Use On Error Resume Next when failure is expected and limited to a specific statement—for example, checking whether an optional object is available. Keep the scope narrow: turn it on just before the operation, inspect Err.Number, and turn it off or restore normal handling immediately afterward. Leaving it active across many statements can hide unrelated mistakes.
Choose based on what the procedure should do, not on which syntax is shortest:
- Use a handler label when a failure means the main operation cannot continue.
- Use a brief
Resume Nextsection when an expected failure has a clear, checked alternative. - Raise a descriptive error when the procedure detects an invalid condition that VBA has not automatically treated as an error.
- Do not suppress an error merely to make a message disappear. If the macro cannot safely continue, stop it and explain why.
A basic handler with a practical example
This example copies a value from Input!A1 to Report!B1. It treats a blank input as an intentional, named error and handles it in the same place as unexpected runtime errors. Change the worksheet names or cell addresses to fit the workbook. Save a copy of the workbook before running a macro: VBA changes cannot be undone with Ctrl+Z.
Option Explicit
Public Sub CopyInputToReport()
Dim sourceCell As Range
Dim targetCell As Range
On Error GoTo ErrorHandler
' Set the source and destination cells in this workbook.
Set sourceCell = ThisWorkbook.Worksheets("Input").Range("A1")
Set targetCell = ThisWorkbook.Worksheets("Report").Range("B1")
' Raise a clear error for an input the procedure cannot use.
If Len(CStr(sourceCell.Value)) = 0 Then
Err.Raise vbObjectError + 1000, "CopyInputToReport", "Input!A1 is blank."
End If
targetCell.Value = sourceCell.Value
MsgBox "Report updated.", vbInformation
Exit Sub
ErrorHandler:
MsgBox "The update failed." & vbCrLf & _
"Error " & Err.Number & ": " & Err.Description & vbCrLf & _
"Source: " & Err.Source, vbExclamation
End Sub
vbObjectError + 1000 gives the deliberately raised error a number in the range commonly used for application-defined errors. The handler displays the error number, description, and source; it does not pretend the copy succeeded. In a larger application, a message box might be replaced or supplemented with a log entry and a controlled return to the calling procedure.
Using Resume Next without hiding failures
Sometimes code needs to attempt one operation that may legitimately fail. For example, a macro may try to find a worksheet by name and then create it if it is absent. A short Resume Next block is appropriate only if the code immediately checks whether the lookup worked. The key is to make the expected failure explicit and prevent the setting from masking later errors.
For an object lookup, the general pattern is to set the object variable to Nothing, enable On Error Resume Next, perform only the lookup, inspect Err.Number or the resulting object, clear the error state if appropriate, and restore the intended error handling. If a procedure already has a label handler, use On Error GoTo 0 only with care: it disables that procedure’s handler. Do not assume it merely turns off Resume Next while preserving every previous setting.
Another option for a worksheet is to loop through the workbook’s Worksheets collection and compare names. That avoids deliberately triggering an error for a normal “not found” result, and can be clearer when lookup behavior is part of ordinary program flow. Prefer an explicit test when it is practical.
Custom errors and useful diagnostics
Not every condition that should stop a task produces a runtime error by itself. A missing required value, an invalid range choice, or an unsupported option may be logically unacceptable even though VBA can execute the next line. Err.Raise lets code signal that condition with a chosen number, source, and description. This gives callers a consistent way to distinguish known business rules from unexpected failures.
Descriptions should tell the user what failed and, when possible, what they can do next. “Error occurred” is less useful than “The required Report worksheet was not found.” Avoid presenting raw technical details as the only explanation to a general user, but preserve the number and source somewhere useful for troubleshooting. If code re-raises an error after adding context, it should not accidentally discard the original details that would help diagnose the underlying cause.
In larger projects, central logging can record the procedure name, error number, description, and relevant inputs. Be cautious about writing sensitive workbook contents into logs. A log also needs its own failure plan: if the log location is unavailable, the original error should not be replaced by an unrelated logging failure.
Common mistakes and how to avoid them
- Putting normal code after the handler: execution can fall through into the error section on a successful run. Add
Exit SuborExit Functionbefore the handler label. - Using Resume Next for an entire macro: failed assignments and lookups may be silently skipped. Restrict it to the expected statement and check the result right away.
- Checking Err too late: another operation may change the error details. Read
Err.Numberand related properties immediately after the operation being checked. - Assuming every failure is recoverable: continuing after a missing worksheet or incomplete update can leave a misleading partial result. Stop when the macro cannot establish a safe state.
- Confusing compile errors with runtime errors: fix syntax and declaration problems in the editor; an error handler only responds to errors that occur while code is executing.
- Ignoring cleanup: if a macro changes application settings, opens files, or creates temporary resources, plan how to restore or close them on both success and failure.
Cleanup deserves special care. A handler that jumps to a cleanup label can restore settings or close resources, but cleanup operations can fail too. Keep cleanup simple, guard resources that may not have been initialized, and avoid a handler that repeatedly jumps back to itself. For application-wide settings such as screen updating or events, save the original value and restore that value rather than assuming it was on. In complex procedures, test both the success path and several failure paths.
Debugging a VBA error in Excel
When a macro stops, note the exact message and the line highlighted by the editor. Use the Visual Basic Editor’s debugging tools to step through code and inspect variables rather than immediately adding a broad suppression statement. In the editor, Debug > Compile VBAProject can help locate compile problems, while breakpoints and stepping help reveal which runtime operation fails.
On Windows, press Alt+F11 to open the Visual Basic Editor from Excel. On a Mac, the shortcut can depend on the keyboard and function-key settings; use Tools > Macro > Visual Basic Editor where available, or the Visual Basic command on the Developer tab. In the editor, Tools > Options > General includes Error Trapping choices. “Break on Unhandled Errors” is generally useful for ordinary debugging; other settings can make execution stop on errors even when code has a handler.
For a reproducible diagnosis, try the macro with a copy of the workbook and the input that causes the problem. Check that expected sheets, named ranges, files, and values exist. Add temporary diagnostic output only where it helps narrow down the failing step, then remove or deliberately retain it as a documented log. VBA’s error handler is not a substitute for testing normal and exceptional inputs.
Excel versions, macros, and safety
These VBA principles apply to desktop Excel, including Microsoft 365 for Windows. Excel for the web can open a macro-enabled workbook, but it cannot create, edit, or run VBA macros; use desktop Excel for those tasks. Excel for Mac supports VBA, although some Windows-specific APIs, controls, references, and automation features may not work the same way. Code intended for both platforms should avoid assuming that every Windows object or external component exists on a Mac.
VBA code is executable, so only enable macros in files from sources you trust. Do not lower macro security globally just to make one workbook run. Follow your organization’s security policy, and use a separate test copy for unfamiliar code. Saving a backup matters because VBA edits to cells, sheets, and workbook structures are not reversed by Excel’s Ctrl+Z undo history.
Frequently Asked Questions
What is the simplest way to handle an error in VBA?
For a procedure that should stop when something goes wrong, use On Error GoTo with a handler label. Display or record useful details, then exit or clean up deliberately.
What is the difference between On Error GoTo 0 and Err.Clear?On Error GoTo 0 disables the enabled error handler in the current procedure. Err.Clear clears the stored error information in the Err object; neither statement performs the other’s job.
Can On Error Resume Next ignore every error safely?
No. It can let the macro continue after a failed statement, leaving variables or workbook state different from what later code expects. Use it narrowly and check the operation immediately.
Can VBA error handling catch a compile error?
No. A compile error prevents the affected code from running. Correct the syntax, declaration, or reference problem in the Visual Basic Editor before relying on runtime error handling.
Can I run VBA macros in Excel for the web?
No. Excel for the web can store and open macro-enabled workbooks, but VBA macros must be run, edited, or created in desktop Excel.
Why does my error handler not run?
Check that the error occurs after the On Error GoTo statement, that the label is in the same procedure, and that the editor’s Error Trapping setting is not configured to break on all errors. Also confirm the error is a runtime error rather than a compile issue.
Related
- Excel Text Filters: How to Find Matching Rows Easily
- Excel Text Box Wrap Text: How to Enable It
- Excel Template for a Social Media Calendar
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA Button: Add One to a Worksheet Step by StepTo insert a VBA button in an Excel worksheet, show the Developer tab, choose Developer > Insert >…
- TG Macro Settings: Best Options for Telegram UsersFor most people asking about TG macro settings, Telegram Business quick replies are the best starting point: they…
- Excel VBA Open a Given File: Code and OptionsYes. In Excel for Microsoft 365 on Windows, VBA opens a specific workbook with the Workbooks.Open method, supplying…
- Excel UserForm ComboBox: Load a List with VBATo load an Excel UserForm ComboBox from worksheet values, add a ComboBox to the form, then fill it…
- Excel VBA Find Method: Syntax, Examples, and TipsIn Excel VBA, use the Range.Find method to search a range and return the first matching cell; if…
- Excel Macro Range: How to Reference and Use RangesAn Excel macro range is one cell or a group of cells that VBA can read, change, format,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.