VBA & Macro Problems

Troubleshoot Excel VBA Crashes Caused by On Error Resume Next

Amos GikundaAmos Gikunda Nov 12, 2026 869 views

Question details

The user needs to identify and fix Excel VBA crashes that occur when On Error Resume Next suppresses the original error.

Product
Microsoft Excel VBA
Device & OS
not provided
Scenario
Older Excel VBA tools or projects begin failing while code uses On Error Resume Next, making the underlying problem difficult to locate.
Observed behavior
The macro continues past a failing statement or appears to crash without clearly identifying the original error line.
Before you start

Open the workbook in Excel, press Alt+F11 to launch the Visual Basic Editor, and locate the procedure that contains On Error Resume Next. Make a note of the affected procedure and the statements it is expected to skip when an error occurs.

Solution 1Recommended

Debug the exact VBA statement instead of suppressing every error

Use this approach when a macro crashes or behaves unpredictably because On Error Resume Next hides the original failure.

On Error Resume Next should be limited to a statement or small group of statements where an error is expected. Check Err.Number immediately afterward, then use On Error GoTo 0 so later errors are reported normally.

1
Enable break-on-all-errors debugging

In the Visual Basic Editor, select Tools > Options, open the General tab, choose Break on All Errors, and click OK.

2
Compile the VBA project

In the Visual Basic Editor, select Debug > Compile VBAProject. Correct any compile error that Excel highlights before running the macro again.

3
Temporarily remove broad error suppression

Find the procedure's On Error Resume Next statement and comment it out or move it directly above the single statement that may legitimately fail.

4
Check the error immediately

Add an If Err.Number <> 0 Then condition immediately after the risky statement, inspect Err.Number and Err.Description, and use Debug.Print or a message box to identify the failure.

5
Restore normal error handling

After the error check, select Run > Reset if needed, then add On Error GoTo 0 so subsequent statements use Excel's normal VBA error reporting.

Why this works: Break on All Errors and a local Err.Number check expose the failing line instead of allowing a global On Error Resume Next statement to conceal it.
Free Microsoft Office alternative

Use WPS Office as a Lightweight Alternative for Excel Workbook Tasks

WPS Office provides a free, lightweight spreadsheet alternative with a familiar interface and strong compatibility with Microsoft Excel workbook formats. It can help with everyday spreadsheet editing and migration, while Excel remains the appropriate environment for diagnosing Excel-specific VBA macro behavior.

  1. 1. Download WPS Office: Select Download WPS Office Free and install WPS Office from the official WPS website.
  2. 2. Open the workbook: Launch WPS Spreadsheets, select Open from the home screen, and choose the Microsoft Excel .xlsx or .xls file you want to review.
  3. 3. Review or edit spreadsheet content: Use the spreadsheet editing tools to inspect formulas, worksheets, formatting, and data while preserving a compatible Office file format when saving.
Free and lightweight spreadsheet tools for everyday workbook editingFamiliar menus and interface that make migration from Excel easierCompatible with Microsoft Excel .xlsx and .xls workbook formatsUseful for reviewing, formatting, and sharing spreadsheet content without a full Microsoft Office installation
QA img-9

Frequently Asked Questions

Why can On Error Resume Next make an Excel VBA crash difficult to diagnose?

It suppresses runtime errors and allows execution to continue, so the original failing statement may be hidden. Restrict it to the expected risky statement and inspect Err.Number and Err.Description immediately afterward.

How do I make Excel stop on the actual VBA error?

Open the Visual Basic Editor with Alt+F11, select Tools > Options > General, choose Break on All Errors, and click OK. Run the procedure again so Excel highlights the failing line.

What does On Error GoTo 0 do in VBA?

On Error GoTo 0 disables the active error handler and restores normal VBA error reporting. Place it immediately after the controlled operation that uses On Error Resume Next.

How can I find compile problems before running the macro?

In the Visual Basic Editor, select Debug > Compile VBAProject. Excel will identify compile errors that must be corrected before the project can run reliably.

Can WPS Office fix an Excel VBA error caused by On Error Resume Next?

No. This is an Excel VBA debugging issue, so Excel and its Visual Basic Editor are needed to inspect the macro. WPS Office can provide a free alternative for editing and reviewing compatible .xlsx and .xls spreadsheets.