To stop an Excel VBA macro when an error occurs, let the error remain unhandled or use an error handler that reports it and exits the procedure; On Error GoTo 0 only disables a handler and does not stop code by itself. To interrupt a macro that is still running, use Ctrl+Break on Windows, or configure Application.EnableCancelKey so the interruption can be handled safely.
What “cancel on error” means in VBA
There are two different situations behind the phrase “cancel on error.” A runtime error happens while a macro is executing—for example, when code tries to divide by zero or open a workbook that cannot be found. Canceling a running macro means interrupting work that has not necessarily produced an error, such as stopping a long loop. VBA handles these situations differently, so choose the approach that matches what you want.
Without an active error handler, a runtime error normally displays an error message and stops the procedure at the failing statement. With an active handler, VBA transfers control to the handler instead. That handler may display a message, clean up resources, recover, or deliberately end the procedure. Merely adding an error handler does not automatically cancel the macro: what happens next depends on the code in the handler.
Also distinguish “stop the current procedure” from “undo everything it did.” Stopping a macro does not reverse cell edits, file changes, or other actions already completed. Excel’s Ctrl+Z undo history generally cannot undo VBA macro actions, so save a copy of the workbook before running a macro that changes important data.
Let an error stop the procedure
If you do not need custom error handling, the simplest approach is to avoid suppressing errors. For example, this procedure has no handler, so the division-by-zero error stops it and Excel displays the runtime error dialog:
Sub StopAtFirstError()
Dim numerator As Double
Dim denominator As Double
Dim result As Double
numerator = 1
denominator = 0
result = numerator / denominator
MsgBox "This line is not reached."
End Sub
In ordinary code, leaving an error unhandled can be useful while developing because VBA identifies the failing line. In a finished macro, however, a bare error dialog may be confusing, and the procedure may need to close a workbook or restore an application setting before it ends. In those cases, catch the error, perform necessary cleanup, and then stop or pass the error onward.
To turn off an active line-based handler in the current procedure, use On Error GoTo 0. This does not jump to the end of the procedure, nor does it cancel the next line. It means that a later error is no longer handled by that procedure’s handler and will be unhandled unless another applicable handler takes over.
Use an error handler that exits
A handler is appropriate when you want a useful message and a controlled stop. The following example sends execution to a label after an error, displays the error description, and then reaches the end of the procedure:
Sub ReportErrorAndStop()
Dim numerator As Double
Dim denominator As Double
Dim result As Double
On Error GoTo Failed
numerator = 1
denominator = 0
result = numerator / denominator
MsgBox "This line is not reached."
Exit Sub
Failed:
MsgBox "The macro stopped: " & Err.Description, vbExclamation
End Sub
The Exit Sub before the label matters. If the procedure completed its normal work, it would otherwise continue into the handler label and show an error message even though no error occurred. For a function, use Exit Function before the handler; for a property procedure, use the matching exit statement.
Do not use On Error Resume Next as a general “cancel on error” switch. It tells VBA to continue with the statement after an error, which can allow a macro to carry on with missing or invalid data. It is sometimes useful for a narrowly scoped operation when code checks Err.Number immediately afterward, but it should not be left active across a whole procedure without a clear reason.
Clean up before stopping
Some errors occur after a macro has changed Excel’s state—for example, after disabling screen updating, changing calculation mode, or opening another workbook. A reliable pattern saves the original settings, routes errors to one handler, and restores the settings in a shared cleanup section. Otherwise, Excel may remain in an inconvenient state after the macro stops.
For example, a procedure that changes a setting can use this structure. The example illustrates the control flow; adapt the work and saved settings to the procedure you are writing:
Sub WorkWithCleanup()
Dim oldScreenUpdating As Boolean
oldScreenUpdating = Application.ScreenUpdating
On Error GoTo Failed
Application.ScreenUpdating = False
' Put the main task here.
GoTo CleanUp
Failed:
MsgBox "The macro stopped: " & Err.Description, vbExclamation
Resume CleanUp
CleanUp:
Application.ScreenUpdating = oldScreenUpdating
End Sub
Save a copy of the workbook before running a macro that changes data: VBA actions cannot normally be undone with Ctrl+Z. Cleanup restores settings, but it does not roll back edits the macro already made. If recovery of data matters, plan a backup, a temporary copy, or explicit rollback logic.
Cancel a long-running macro with Ctrl+Break
On Windows, Ctrl+Break is the usual keyboard interruption for a running macro. Depending on the keyboard, Break may be a separate key or require Fn. If VBA is configured to break on all errors, the editor may stop at the interrupted line; otherwise, an interruption may appear as a runtime error. In the Visual Basic Editor, the Reset button can stop execution, but resetting is abrupt and does not run normal cleanup code.
VBA also provides Application.EnableCancelKey. Its modes determine how Excel responds to a user interruption: xlInterrupt allows an interruption, xlErrorHandler routes it to the active VBA error handler as error 18, and xlDisabled prevents the user from interrupting the macro. Disabling interruption is risky for a long-running procedure because Excel may appear unresponsive; avoid it unless there is a specific, controlled reason.
This example lets Ctrl+Break reach a handler, recognizes error 18 as a cancellation, and restores the setting before the procedure finishes:
Sub LongTaskThatCanBeCancelled()
Dim i As Long
Dim errorNumber As Long
Dim errorDescription As String
Dim wasCancelled As Boolean
On Error GoTo HandleError
Application.EnableCancelKey = xlErrorHandler
For i = 1 To 1000000
Cells(i, 1).Value = i
DoEvents
Next i
GoTo CleanUp
HandleError:
errorNumber = Err.Number
errorDescription = Err.Description
If errorNumber = 18 Then
wasCancelled = True
Else
MsgBox "The macro stopped: " & errorDescription, vbExclamation
End If
Resume CleanUp
CleanUp:
Application.EnableCancelKey = xlInterrupt
If wasCancelled Then MsgBox "The task was cancelled."
End Sub
This sample writes to cells, so run it only in a disposable or copied workbook; it is an illustration, not a harmless test. DoEvents yields time to Excel to process events, including a cancellation request, but frequent calls can affect performance. The code also treats other runtime errors separately from error 18 rather than mislabeling every failure as a user cancellation. On Mac, keyboard interruption can differ by keyboard and Excel version; Command+Period is commonly used to interrupt, but consult Excel’s current shortcut help if it does not work. Excel for the web does not run VBA macros; use the desktop Excel app.
Choose the right Error Trapping setting
The Visual Basic Editor’s error-trapping option affects where execution pauses during debugging; it does not replace sound error-handling code. In the editor on Windows, open Tools > Options > General and look under Error Trapping. The choices include:
- Break on Unhandled Errors: pause when an error has no active handler. This is generally the practical choice for normal debugging.
- Break in Class Module: pause in a class module when an error occurs there, which can help when debugging class-based code.
- Break on All Errors: pause whenever an error occurs, including errors that code may handle. This can be useful while investigating a problem, but can make expected, handled errors interrupt execution.
If a macro appears to ignore its handler, check whether Break on All Errors is selected. Conversely, changing the editor option does not make an unsafe macro safe for other users: their editor settings may differ. Production code should have appropriate handlers and should not rely on a developer’s local debugging preference.
Common mistakes and safer choices
A frequent misconception is that On Error GoTo 0 means “stop now.” It means “disable the current procedure’s enabled error handler.” If no error happens on the next statement, execution continues normally. To stop intentionally, use a clear exit path such as Exit Sub, or allow an error to remain unhandled when that is the intended behavior.
Another common problem is using a handler that reports the error but then continues through the rest of the procedure. Once control reaches a handler label, VBA does not automatically return to the line after the error. Add an explicit recovery path, a Resume statement where appropriate, or a cleanup-and-exit path. Do not resume blindly at the failed statement if repeating it would cause another error or duplicate a change.
Use On Error GoTo -1 only when you need to clear the current error state so a later error can be handled anew in the same procedure. It is not a substitute for On Error GoTo 0: zero disables the active handler, whereas minus one clears the current exception state. Most straightforward procedures need neither command beyond a deliberate On Error GoTo 0 when turning off a temporary handler.
- For a simple macro, leave errors unhandled during development so the failing line is visible.
- For a user-facing macro, catch expected errors, explain what failed, and exit or recover deliberately.
- For a long task, provide a way to interrupt it and ensure cleanup runs after interruption.
- For a potentially destructive macro, work on a saved copy because stopping execution does not undo previous changes.
Frequently Asked Questions
Does On Error GoTo 0 stop a VBA macro?
No. It disables the active error handler in the current procedure. Execution continues, and a later error is unhandled unless another handler applies.
How do I make a macro stop after showing an error?
Use an On Error GoTo handler, show the message there, and then exit the procedure or route through cleanup. Do not use On Error Resume Next if the goal is to stop at the first error.
Can I cancel a VBA macro without closing Excel?
Usually, press Ctrl+Break on Windows. If the macro has configured Application.EnableCancelKey, the interruption may be handled in code. Resetting execution in the VBA Editor is another option, but it can bypass cleanup.
Why does my error handler not run?
Check that On Error GoTo HandlerName runs before the failing statement, that the label is in the same procedure, and that the editor is not set to Break on All Errors. An already-active handler also cannot handle another error in the same way until execution resumes or the error state is cleared.
Can I undo a macro after cancelling it?
Usually not with Ctrl+Z. Cancellation stops future code; it does not reverse changes already made. Save a separate copy before running macros that modify important workbook contents.
Related
- Excel Shortcut to Select Cells to the End of a Column
- Excel Shortcut to Select the Active Cell’s Entire Column
- Excel Shortcut to Select to the Bottom of a Column
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA JSON: Parse, Read, and Write DataExcel VBA can work with JSON, but VBA has no built-in JSON parser. In desktop Excel, the usual…
- Excel VBA Functions With Multiple Parameters ExplainedTo create an Excel VBA function with multiple parameters, declare each argument in the function’s parentheses, separated by…
- Excel VBA AutoFilter Multiple Fields: Examples and TipsTo filter multiple Excel fields with VBA, call AutoFilter on the same range once for each column, using…
- How to Delete Blank Rows in Excel with a VBA MacroTo delete blank rows in Excel with a macro, select the data range, run a VBA procedure that…
- VBA Go to Next Item in a For LoopVBA does not have a Continue For statement. To skip the rest of the current pass and move…
- Send Email from MS Access Using VBAYes. In Microsoft Access for Windows, VBA can create and send an email through classic Outlook automation, or…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.