In Excel VBA, On Error Resume Next tells a procedure to continue with the statement immediately after the one that caused a run-time error. Use it only around a specific operation you expect might fail, then check the Err object and turn the setting off; otherwise, important failures can be silently ignored.
What On Error Resume Next Does
VBA normally stops a procedure when an unhandled run-time error occurs, such as trying to refer to a worksheet that does not exist. On Error Resume Next changes that behavior for the procedure where the statement runs. If a later statement raises a run-time error, VBA skips the rest of that statement and continues at the next statement.
“Resume Next” does not mean try the failed operation again, skip to the next loop iteration, or continue at the next line in the editor regardless of what the code does. It means proceed to the next executable statement after the statement that failed. Your code still needs to decide what to do about the failure.
The setting remains active in that procedure until another error-handling statement changes it or the procedure ends. That makes it powerful but risky: a missing worksheet, an invalid object, or another unexpected failure can be concealed, and subsequent code may produce incomplete or misleading results.
How the Err Object Helps
When VBA encounters a run-time error, it records information in the built-in Err object. Its Number property gives the error number, and Description provides a description. Check those properties immediately after the operation that might fail. If another operation fails before you inspect them, the information may describe that later error instead.
After handling an error, use Err.Clear if you want to clear the recorded error information. This is separate from turning off the Resume Next behavior: On Error GoTo 0 disables the active error-handling setting in the current procedure, while Err.Clear clears the Err object’s stored details. They are often used together.
Also distinguish a run-time error from a logic mistake. Resume Next can affect run-time errors that happen while the procedure executes; it cannot fix an incorrect formula, a wrong assumption about the data, or a compile error such as a misspelled variable when Option Explicit is in effect. Those problems need to be corrected rather than bypassed.
A Safer Pattern for Using It
Keep the period during which errors are being suppressed as short as possible. A practical pattern is to enable Resume Next for one operation, save the error details, clear them, turn the setting off, and then make an explicit decision. That keeps ordinary code from quietly running under a broad error-suppression setting.
- Identify the exact statement that may legitimately fail.
- Place
On Error Resume Nextimmediately before that statement. - Check and save
Err.Number(and, if useful,Err.Description) immediately afterward. - Call
Err.Clearand useOn Error GoTo 0to restore normal error behavior. - Branch on the saved error number: handle the expected case or report an unexpected problem.
Checking the saved number is preferable to merely checking whether an object variable is Nothing. It tells you that the attempted operation failed, and saving it before other work avoids losing the original diagnostic.
Example: Check for an Optional Worksheet
This example looks for a worksheet named Optional. A missing worksheet is a plausible condition when a workbook supports optional tabs, so the lookup is isolated. Before running any macro, save a copy of the workbook: VBA changes cannot be reversed with Ctrl+Z. To keep the macro in the file, save it as an Excel Macro-Enabled Workbook (.xlsm).
Sub CheckOptionalSheet()
Dim ws As Worksheet
Dim errorNumber As Long
Dim errorDescription As String
' Suppress an error only for the worksheet lookup.
On Error Resume Next
Set ws = ThisWorkbook.Worksheets("Optional")
errorNumber = Err.Number
errorDescription = Err.Description
Err.Clear
On Error GoTo 0
' Decide what to do after normal error handling is restored.
If errorNumber <> 0 Then
MsgBox "Could not get the Optional worksheet: " & errorDescription
Exit Sub
End If
MsgBox "The Optional worksheet is available."
End Sub
If the sheet exists, the lookup succeeds and the final message confirms it. If it does not, the lookup raises a run-time error, the error number and description are saved, and the procedure displays a message rather than continuing as though the sheet were available. The code does not assume every possible failure means the tab is missing; it reports the lookup failure. In a larger macro, you could use the saved number to distinguish a specifically expected error from an unexpected one.
When Resume Next Is Appropriate
This statement is useful for a narrow operation where failure is anticipated and the code has a clear alternative. Examples include checking for an optional worksheet, trying to access an optional object supplied by another application, or attempting a cleanup operation when the object may already be closed. In each case, the macro should inspect the outcome and continue only when doing so is safe.
- Optional resources: Try to find a sheet, named range, or other resource that may not be present, then handle the absent case.
- Best-effort cleanup: Attempt to close or release a resource that may already have been closed, while ensuring that any important cleanup failure is not hidden.
- Controlled probing: Test whether a particular object or property is available, then use an explicit fallback if it is not.
“Best effort” does not mean “ignore every error.” If the operation is essential—for example, writing a required result to a worksheet—then silently continuing can make the macro appear successful when it has not completed its job. Check the error and stop or report a useful message when the result matters.
When to Choose a Different Error Handler
For a larger procedure with a central recovery or cleanup path, On Error GoTo label is often clearer. It transfers control to a named error-handling section, where the code can record the error, close resources, notify the user, and exit in a controlled way. The handler should not simply conceal the problem: it should have an intentional recovery or reporting action.
For example, a routine that performs several required steps may need to stop at the first failure and show which step failed. Resume Next is a poor fit if the remaining steps depend on the failed one. A labeled handler makes that dependency easier to manage. Conversely, a one-line optional lookup may be simpler with Resume Next plus an immediate check than with a separate handler section.
On Error GoTo 0 is not an alternative that catches errors. It disables the current procedure’s enabled error handler, so later unhandled run-time errors follow VBA’s normal error behavior. Use it after a narrow Resume Next section when you want subsequent code to fail normally rather than being silently skipped.
Common Mistakes and Misconceptions
- Leaving it active for too long: A broad block can hide unrelated problems. Put the statement close to the risky operation and restore normal handling promptly.
- Not checking Err: If the code never reads
Err.Number, it may continue without knowing whether the operation succeeded. - Checking too late: Another failed operation can replace the error details. Save the number and description immediately after the operation you meant to test.
- Assuming it fixes every error: It handles run-time errors, not compile-time problems or incorrect results that VBA considers valid.
- Assuming one procedure’s setting covers all called code: Error handling is procedure-scoped. A called procedure needs its own appropriate handling if it must manage errors internally; do not rely on a caller’s Resume Next as a substitute for designing error handling in that routine.
Another common misconception is that continuing means the macro is safe to continue. VBA only changes the control flow; it does not verify that variables or workbook contents are in a usable state. After any failed statement, consider whether later code depends on the value or side effect that statement was supposed to produce.
Excel Platform and Macro Safety
These examples apply to desktop Excel VBA, including Excel for Microsoft 365 on Windows. Excel for the web cannot create, edit, or run VBA macros, so a workbook that relies on this code needs to be opened in a desktop version of Excel. Excel for Mac also supports VBA, but some object-model features and automation integrations differ from Windows; test macros that interact with other applications or platform-specific resources on the intended system.
Because Resume Next can hide failures, review the code before enabling or running a macro, especially in a workbook received from someone else. Store important work separately before running macros that change data, and include a clear error message or log for operations that matter. A macro that finishes without displaying an error is not necessarily a macro that completed every intended task.
Frequently Asked Questions
Does On Error Resume Next skip the rest of the macro?
No. It skips the failed statement and continues at the next executable statement in the same procedure. Depending on what the rest of the code does, the procedure may then finish normally, take another branch, or encounter more errors.
How do I turn off On Error Resume Next?
Use On Error GoTo 0 in the same procedure. If you also want to remove the currently stored error details, call Err.Clear; disabling the handler and clearing the error information are separate actions.
Can I use it to check whether a worksheet exists?
Yes. Apply it only to the worksheet lookup, check Err.Number immediately, and then restore normal error handling. Do not leave it active while the macro uses the worksheet, because a later error could be hidden too.
Does it handle errors inside another Sub or Function?
Do not depend on the caller’s Resume Next to manage errors inside another procedure. Each procedure should handle errors it can resolve or allow an appropriate error handler to deal with them as control returns. Keep the handling strategy explicit at the procedure where the risk occurs.
Can Ctrl+Z undo a VBA macro?
No. Running a VBA macro generally clears Excel’s undo history, and Ctrl+Z does not reverse the macro’s changes. Save a separate copy before running a macro that modifies workbook data.
Related
- Excel Unmerge Cells and Fill Every Cell with Its Data
- Excel Undo Shortcut: Ctrl+Z and How It Works
- Excel UK Number Format: Dates, Currency and Separators
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA Pivot Table Sort: Code, Options, and Practical FixesTo sort a PivotTable with VBA, use the target row field’s AutoSort method: for example, set its order…
- Excel VBA FindNext: How to Find the Next MatchIn Excel VBA, use Range.FindNext to move from one match to the next after starting a search with…
- Excel VBA Copy and Paste Range: Examples and TipsIn Excel VBA, copy a range to another location with sourceRange.Copy Destination:=destinationRange. For values only, assign the ranges…
- Excel VBA Export Range to CSV, Text, or Another SheetTo export an Excel range with VBA, copy it to a temporary workbook and save that workbook as…
- Excel VBA InputBox for Dates: How to Validate InputTo ask for a date in Excel VBA, collect the entry as text, validate it, and convert it…
- Excel VBA GoTo: Syntax, Examples, and Error HandlingIn Excel VBA, GoTo transfers execution to a named label elsewhere in the same procedure. It is most…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.