VBA Error Handling Examples for Reliable Macros

VBA error handling examples usually use On Error GoTo to catch unexpected failures, On Error Resume Next briefly for an expected failure, and a cleanup section to restore resources or Excel settings. Choose the narrowest pattern that fits the risk, and record enough detail to diagnose errors rather than silently ignoring them.

Before running VBA examples

Save a copy of the workbook before running any macro in it. VBA macros cannot be undone with Ctrl+Z, and a macro can change or delete data even when its error handling is correct. Only enable macros in files you trust. The examples below are written for Excel VBA on Windows; the VBA error-handling statements also apply to other Office VBA hosts, though Excel objects such as worksheets are Excel-specific.

How VBA error handling works

Without an active error handler, a run-time error normally stops the procedure and displays a message. An On Error statement changes what VBA does when a later statement fails:

On Error GoTo HandlerName transfers control to a labeled handler in the same procedure.
On Error Resume Next skips a statement that raises an error and continues with the next statement.
On Error GoTo 0 disables the procedure’s enabled handler. It does not mean “go to line zero.”

The Err object provides details about the most recent error. Its commonly used properties are Number, Description, and Source. Capture those details promptly in a handler: later operations can change the contents of Err. Error handling is local to a procedure, so a called procedure that needs its own recovery or cleanup should handle its own errors or allow them to propagate to a caller with an active handler.

Example: catch and explain an unexpected error

This basic pattern sends execution to a labeled handler, displays a useful message, and exits normally after success. The example raises a deliberate error if the divisor is zero, rather than allowing an invalid calculation.

Sub DivideValues()
    Dim numerator As Double
    Dim divisor As Double
    Dim result As Double

    On Error GoTo HandleError
    numerator = 12
    divisor = 0

    If divisor = 0 Then
        Err.Raise vbObjectError + 1000, "DivideValues", "The divisor cannot be zero."
    End If

    result = numerator / divisor
    MsgBox "Result: " & result
    Exit Sub

HandleError:
    MsgBox "The calculation failed. " & Err.Description, vbExclamation
End Sub

The Exit Sub before the handler is important. Without it, a successful run would continue into the handler label and show an error message even though no error occurred. A custom error number based on vbObjectError helps distinguish an application-defined error from a built-in VBA error.

Example: handle a worksheet that may not exist

A missing worksheet can be an expected condition—for example, a macro may look for an optional input sheet. In that narrow case, use On Error Resume Next for just the lookup, then check whether the object was assigned and immediately restore normal handling.

Sub CheckForInputSheet()
    Dim ws As Worksheet

    On Error Resume Next
    Set ws = ThisWorkbook.Worksheets("Input")
    On Error GoTo 0

    If ws Is Nothing Then
        MsgBox "This workbook does not contain an Input sheet.", vbInformation
        Exit Sub
    End If

    MsgBox "The Input sheet is available."
End Sub

ThisWorkbook refers to the workbook containing the macro, unlike ActiveWorkbook, which refers to the workbook currently active in Excel. That distinction can prevent a macro from checking the wrong file. The object test matters: suppressing an error does not itself tell you whether the lookup succeeded. Avoid leaving On Error Resume Next active for a long sequence of unrelated statements.

Example: release a resource after an error

When code opens a file or otherwise acquires a resource, it should release that resource on both the success and failure paths. This example reads one line from a text file and closes the file if an error occurs after opening it.

Sub ReadFirstLine(ByVal filePath As String)
    Dim fileNo As Integer
    Dim fileIsOpen As Boolean
    Dim firstLine As String
    Dim savedNumber As Long
    Dim savedDescription As String

    On Error GoTo Failed
    fileNo = FreeFile
    Open filePath For Input As #fileNo
    fileIsOpen = True

    Line Input #fileNo, firstLine
    Close #fileNo
    fileIsOpen = False
    MsgBox firstLine
    Exit Sub

Failed:
    savedNumber = Err.Number
    savedDescription = Err.Description
    Resume Cleanup

Cleanup:
    On Error Resume Next
    If fileIsOpen Then Close #fileNo
    On Error GoTo 0
    MsgBox "Could not read the file. Error " & savedNumber & ": " & savedDescription, vbExclamation
End Sub

The handler saves the original error before attempting cleanup, because a failed cleanup operation could otherwise replace the details being reported. Here, On Error Resume Next is restricted to the close attempt: the original file error remains the one shown to the user. Similar cleanup logic is useful for closing workbooks or restoring application settings, but each resource needs its own appropriate cleanup action.

Example: restore an Excel setting after failure

Macros sometimes turn off screen updating to reduce visual refresh while they work. If a later line fails, leaving that setting changed can confuse the user. Save its previous value and restore that value in a shared exit section rather than assuming the setting was originally on.

Sub UpdateReport()
    Dim oldScreenUpdating As Boolean
    Dim savedNumber As Long
    Dim savedDescription As String

    oldScreenUpdating = Application.ScreenUpdating
    On Error GoTo Failed
    Application.ScreenUpdating = False

    ' Add report updates here.
    Application.ScreenUpdating = oldScreenUpdating
    Exit Sub

Failed:
    savedNumber = Err.Number
    savedDescription = Err.Description
    Resume Cleanup

Cleanup:
    Application.ScreenUpdating = oldScreenUpdating
    MsgBox "The report update failed. Error " & savedNumber & ": " & savedDescription, vbExclamation
End Sub

For a real macro, put the work between the setting change and the successful restoration. If several settings or resources must be restored, list them explicitly in cleanup and consider how to respond if cleanup itself fails. Do not assume that restoring ScreenUpdating reverses changes to worksheet data.

Choosing the right pattern

Use the pattern that matches what can go wrong and what the macro should do next:

Use On Error GoTo when a failure should be reported, logged, or followed by cleanup. It is the usual choice for a procedure with multiple operations.
Use a short On Error Resume Next when one specific operation is expected to fail sometimes and you will check the result immediately, as with an optional worksheet lookup.
Use On Error GoTo 0 to stop suppressing errors after that narrow operation.
Raise an error deliberately with Err.Raise when an invalid input or state should follow the same handler path as a run-time failure.

For a simple macro, a message box may be sufficient. For a macro used regularly, a log can be more useful than a message alone. For example, developers can write error number, description, procedure name, and timestamp to a controlled log location. Do not put sensitive workbook contents into a log by default, and make sure a logging failure does not hide the original problem.

Common error-handling mistakes

Suppressing errors too broadly: A typo, missing range, or unrelated failure can be skipped, leaving incomplete results that look successful. Keep suppression around the one statement whose failure is expected.
Forgetting the exit before a handler: Normal execution can fall through into the handler. Use Exit Sub or Exit Function before the handler label.
Showing only “an error occurred”: Include the error number and description, and identify the operation or procedure when useful. That gives someone troubleshooting the macro a starting point.
Continuing after a failed essential step: Resume Next does not retry the failed statement; it continues at the next one. If later code depends on the failed operation, continuing may produce misleading results.
Assuming errors are undone: Error handling controls what code does after a failure; it does not roll back earlier changes. Design important macros to validate inputs before editing, or save data that must be restored.

In VBA, Resume retries the statement that caused the error, Resume Next continues with the following statement, and Resume label transfers control to a chosen location. Retrying is appropriate only when the cause may have changed and the code has a clear retry strategy. Otherwise, cleanup and a clear failure message are usually safer than an automatic retry.

Frequently Asked Questions

What is the simplest VBA error handler?
Use On Error GoTo followed by a handler label, and put Exit Sub before that label. In the handler, report Err.Number and Err.Description or perform necessary cleanup.

Is On Error Resume Next bad practice?
Not by itself. It is useful for a narrowly scoped operation whose failure is expected, provided the code checks the outcome immediately and turns normal error handling back on. Leaving it active across a procedure can hide real defects.

How do I ignore a specific error in VBA?
Use a focused error-suppression block for the statement that may fail, then inspect the result to confirm whether the expected condition occurred. Avoid ignoring errors just by number across a large block unless the code can also distinguish the expected case from other failures.

What is the difference between Err.Clear and On Error GoTo 0?
Err.Clear clears the current error information held in the Err object. On Error GoTo 0 disables the enabled error handler in that procedure. They do different jobs and are not interchangeable.

Can VBA error handling undo a macro?
No. A handler can stop, report, or clean up after a failure, but it does not reverse completed worksheet edits. Save a copy before running a macro, and use explicit validation or rollback logic if changes need to be reversible.

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.