VBA & Macro Problems

How to Correctly Use On Error Resume Next in VBA Excel Macros

Jason MillerJason Miller Nov 3, 2026 870 views

Question details

The user needs to understand how to correctly implement VBA error handling using 'On Error Resume Next' without hiding underlying code failures or breaking object cleanup.

How to Correctly Use On Error Resume Next in VBA Error Handling
Product
Excel
Device & OS
not provided
Scenario
Writing or debugging VBA macros where specific errors need to be bypassed or handled locally without crashing the entire script.
Observed behavior
Using 'On Error Resume Next' too broadly in the code or within an error handler suppresses all subsequent errors, making it difficult to debug the actual cause of a failure and leading to unreliable object cleanup.
Before you start

Before modifying your VBA code, ensure you have enabled the Developer tab in your spreadsheet application to easily access the Visual Basic Editor and debug your scripts.

Solution 1Recommended

Implement Structured VBA Error Handling with Localized Resume Next

Limit the use of 'On Error Resume Next' to specific, short code blocks to intentionally bypass expected errors, and immediately restore normal error reporting.

Placing 'On Error Resume Next' at the beginning of a subroutine hides all subsequent errors throughout the execution. Instead, you should wrap only the specific lines that might fail, check the error object immediately, and restore normal error reporting using 'On Error GoTo 0'.

1
Open the Visual Basic Editor

Press Alt + F11 to open the Visual Basic Editor (VBE) and locate the macro you want to edit.

2
Set Up a Structured Error Handler

At the start of your procedure, type 'On Error GoTo ErrorHandler' to direct unexpected errors to a specific cleanup section at the end of your code.

3
Wrap Risky Operations

Right before an operation that you expect might fail (such as attempting to select a sheet that might not exist), insert the line 'On Error Resume Next'.

4
Check the Error and Reset

Immediately after the risky operation, evaluate 'Err.Number' to see if an error occurred. Once handled, type 'On Error GoTo 0' on the very next line to turn standard error trapping back on.

5
Create the Cleanup Block

At the bottom of your subroutine, right before 'End Sub', add 'Exit Sub' followed by your 'ErrorHandler:' label. In this block, display a MsgBox with 'Err.Description', set any open objects to 'Nothing', and safely exit.

Best Practice for Error Blocks: Never use 'On Error Resume Next' globally within your ErrorHandler block. Doing so will mask secondary errors that occur during your cleanup process, making debugging exceedingly difficult.
Powerful Macro Support in WPS Office

Write and Debug VBA Macros Seamlessly in WPS Spreadsheets

WPS Spreadsheets provides a built-in Macro Editor that fully supports Visual Basic for Applications (VBA), allowing you to write, debug, and execute your macro scripts with structural error handling just like Microsoft Excel.

  1. 1. Open Your Macro Workbook: Launch WPS Spreadsheets and open your existing .xlsm or .xlsb document.
  2. 2. Access the Developer Tab: Navigate to the Developer tab on the top ribbon and click the 'Visual Basic' or 'Macro' icon.
  3. 3. Edit and Debug VBA: Use the VBE interface to properly structure your 'On Error Resume Next' logic and debug step-by-step.
  4. 4. Save with Macros: Save your document in the .xlsm format to preserve your newly fixed VBA scripts.
Fully compatible with Microsoft Excel macro-enabled formats (.xlsm, .xlsb).Built-in Visual Basic Editor (VBE) for writing and debugging complex VBA code.Lightweight, fast execution, and a highly familiar ribbon interface.Free and easy to download, allowing seamless migration of your automated tasks.
microsoft office alternative - wps office

Frequently Asked Questions

What is the difference between On Error Resume Next and On Error GoTo 0 in VBA?

'On Error Resume Next' instructs the VBA compiler to ignore an error and seamlessly continue executing the next line of code. 'On Error GoTo 0' disables any active error handler in the current procedure, restoring the default behavior where VBA stops execution and displays a system error message.

Why is my VBA ErrorHandler block not catching errors?

If you have an active 'On Error Resume Next' statement earlier in your code that hasn't been explicitly reset with 'On Error GoTo 0', it will override your subsequent error handlers (like 'On Error GoTo ErrorHandler'). This causes VBA to silently ignore errors instead of jumping to your cleanup block.

How do I clear the VBA Err object after an error occurs?

You can clear the Err object by inserting the 'Err.Clear' method in your VBA code. This resets the Err.Number to zero and clears the Err.Description, which is highly recommended when you are evaluating multiple sequential errors within a single procedure.