VBA Error Handling – Build Robust Macros That Don't Crash

A macro runs perfectly for weeks—until someone deletes a column, renames a file, or a network drive goes offline. Without error handling, the macro crashes silently. Worse: Excel stays in a broken state—ScreenUpdating stuck on False, calculation mode stuck on Manual, events disabled. The user sees a frozen screen and loses trust in the entire automation.

Proper error handling prevents all of this. It catches errors, displays clear messages, and reliably restores the system state—no matter what goes wrong.

Got a bug? The VBA Debug Assistant analyzes your code, finds runtime errors, and delivers corrected code with explanations.

---

Why Error Handling Is Essential

Without error handling, a runtime error causes the following:

1. The macro stops immediately—mid-process, with no indication of what happened.
2. Excel states remain altered—if you set Application.ScreenUpdating = False before the error, the screen stays frozen. Application.Calculation = xlCalculationManual stays on manual. Events are disabled.
3. Data is inconsistent—if the macro crashes after processing 500 of 1000 rows, the spreadsheet is half-finished.
4. Users lose trust—after the third crash, nobody uses your macros anymore.

All of this can be prevented with a few lines of code.

---

The Three Error-Handling Strategies in VBA

On Error GoTo — The Standard Approach

This is the most important and most common strategy. It redirects program flow to a label when an error occurs, where you can handle the error:

Sub ProcessData()
    On Error GoTo ErrHandler
    
    ' --- Main code ---
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Data")
    Dim lastRow As Long
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
    Dim i As Long
    For i = 2 To lastRow
        ws.Cells(i, 3).Value = ws.Cells(i, 1).Value * ws.Cells(i, 2).Value
    Next i
    
    MsgBox "Processing complete.", vbInformation
    Exit Sub

ErrHandler:
    MsgBox "Error " & Err.Number & ": " & Err.Description & vbCrLf & _
           "In procedure: ProcessData", vbCritical, "Error"
End Sub

Key Err properties:

• Err.Number – The error number (e.g., 9, 13, 1004)
• Err.Description – Human-readable description of the error
• Err.Source – Source of the error (module/class)
• Err.Clear – Resets the Err object (important after Resume Next)

On Error Resume Next — Use Sparingly

This strategy skips errors and continues with the next line. Warning: This is not a license to ignore errors! Use Resume Next only in two situations:

Good use: Checking if an object exists:

Function WorksheetExists(ByVal sheetName As String) As Boolean
    Dim ws As Worksheet
    On Error Resume Next
    Set ws = ThisWorkbook.Worksheets(sheetName)
    On Error GoTo 0
    WorksheetExists = Not ws Is Nothing
End Function

Good use: Checking if a file exists:

Function FileExists(ByVal filePath As String) As Boolean
    On Error Resume Next
    FileExists = (Dir(filePath) <> "")
    On Error GoTo 0
End Function

Bad use: Blanket error suppression—this hides bugs and makes debugging impossible:

' BAD – never do this!
Sub BadCode()
    On Error Resume Next
    ' 200 lines of code...
    ' Errors silently swallowed
End Sub

On Error GoTo 0 — Reset Error Handling

This statement deactivates the active error handler and restores default behavior (errors trigger the runtime error dialog). Use it after a Resume Next block to return to normal error handling:

Sub MixedApproach()
    On Error GoTo ErrHandler
    
    ' Normal code with error handling...
    
    ' Short Resume Next block:
    On Error Resume Next
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Optional")
    On Error GoTo 0  ' Back to normal handling
    
    ' From here, errors are handled normally again
    ' ...
    
    Exit Sub
ErrHandler:
    MsgBox "Error: " & Err.Description, vbCritical
End Sub

---

The Perfect Error-Handling Template

Copy this template into any procedure. It saves Excel states, runs your main code, and reliably restores everything on success or failure:

Sub MyRobustProcess()
    ' --- Save states ---
    Dim oldCalc As XlCalculation
    oldCalc = Application.Calculation
    Dim oldScreen As Boolean
    oldScreen = Application.ScreenUpdating
    Dim oldEvents As Boolean
    oldEvents = Application.EnableEvents
    
    On Error GoTo ErrHandler
    
    ' --- Performance optimization ---
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Application.EnableEvents = False
    
    ' =====================
    ' YOUR MAIN CODE HERE
    ' =====================
    
    ' ... your code ...
    
    ' =====================

Cleanup:
    ' --- ALWAYS restore states ---
    Application.EnableEvents = oldEvents
    Application.Calculation = oldCalc
    Application.ScreenUpdating = oldScreen
    Exit Sub

ErrHandler:
    MsgBox "Error " & Err.Number & ": " & Err.Description & vbCrLf & _
           "Procedure: MyRobustProcess", vbCritical, "Error"
    Resume Cleanup
End Sub

Why this pattern works:

1. Original states are saved before any changes.
2. The Cleanup label runs on both success and failure (Resume Cleanup).
3. Exit Sub before the ErrHandler prevents the error code from running on success.

---

The Most Common VBA Runtime Errors and Their Solutions

Runtime Error 9 – Subscript out of Range

Cause: You're accessing a worksheet, array element, or collection item that doesn't exist.

' ERROR: Sheet "Data2024" doesn't exist
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Data2024")  ' Error 9!

' SOLUTION: Check existence first
If WorksheetExists("Data2024") Then
    Set ws = ThisWorkbook.Worksheets("Data2024")
Else
    MsgBox "The sheet 'Data2024' was not found.", vbExclamation
    Exit Sub
End If

More details and solutions: VBA Error 9 – Subscript out of Range.

Runtime Error 13 – Type Mismatch

Cause: A value has the wrong data type—e.g., text in a cell that's expected to be a number.

' ERROR: Cell contains text instead of a number
Dim amount As Double
amount = ws.Cells(i, 1).Value  ' Error 13 if cell contains "abc"!

' SOLUTION: Check type first
If IsNumeric(ws.Cells(i, 1).Value) Then
    amount = CDbl(ws.Cells(i, 1).Value)
Else
    MsgBox "Row " & i & ": Not a valid number.", vbExclamation
End If

More on this: VBA Type Mismatch Error.

Runtime Error 1004 – Application/Object-defined Error

Cause: Invalid range reference, protected sheet, or a method that's not allowed in the current context.

' ERROR: Sheet is protected
ws.Cells(1, 1).Value = "Test"  ' Error 1004 on protected sheet!

' SOLUTION: Check protection and temporarily unprotect
If ws.ProtectContents Then
    ws.Unprotect Password:="myPassword"
End If
ws.Cells(1, 1).Value = "Test"
ws.Protect Password:="myPassword"

Detailed guide: VBA Error 1004 – Causes and Solutions.

Runtime Error 91 – Object Variable Not Set

Cause: You're using an object variable without assigning it with Set, or the object is Nothing.

' ERROR: Missing Set keyword
Dim ws As Worksheet
ws = ThisWorkbook.Worksheets("Data")  ' Error 91! Set is missing

' SOLUTION: Always use Set
Set ws = ThisWorkbook.Worksheets("Data")

' ERROR: Using a Nothing reference
Dim rng As Range
Set rng = ws.Range("A1").Find("SearchTerm")
rng.Interior.Color = vbYellow  ' Error 91 if Find returns Nothing!

' SOLUTION: Check for Nothing
Set rng = ws.Range("A:A").Find("SearchTerm")
If Not rng Is Nothing Then
    rng.Interior.Color = vbYellow
Else
    MsgBox "Search term not found.", vbInformation
End If

---

Error Logging – Recording Errors

MsgBox is fine during development, but production macros need a log. Here are two approaches:

Option 1: Write errors to a text file

Sub LogToFile(ByVal proc As String, ByVal errNum As Long, _
              ByVal errDesc As String)
    Dim logPath As String
    logPath = ThisWorkbook.Path & "\errorlog.txt"
    
    Dim f As Integer
    f = FreeFile
    Open logPath For Append As #f
    Print #f, Format(Now, "yyyy-mm-dd hh:nn:ss") & " | " & _
              proc & " | Error " & errNum & ": " & errDesc
    Close #f
End Sub

Option 2: Log errors to a hidden worksheet

Sub LogToSheet(ByVal proc As String, ByVal errNum As Long, _
               ByVal errDesc As String, Optional ByVal context As String = "")
    Dim ws As Worksheet
    On Error Resume Next
    Set ws = ThisWorkbook.Worksheets("_ErrorLog")
    On Error GoTo 0
    
    If ws Is Nothing Then
        Set ws = ThisWorkbook.Worksheets.Add
        ws.name = "_ErrorLog"
        ws.Visible = xlSheetVeryHidden
        ws.Range("A1:E1").Value = Array("Timestamp", "Procedure", _
                                         "Error No", "Description", "Context")
    End If
    
    Dim r As Long
    r = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row + 1
    ws.Cells(r, 1).Value = Now
    ws.Cells(r, 2).Value = proc
    ws.Cells(r, 3).Value = errNum
    ws.Cells(r, 4).Value = errDesc
    ws.Cells(r, 5).Value = context
End Sub

---

Best Practices

1. Always use cleanup code: Every procedure that changes ScreenUpdating, Calculation, or EnableEvents needs a Cleanup label. Without cleanup, Excel stays in an unusable state after an error.

2. Clear Err after Resume Next: After an On Error Resume Next block, always call Err.Clear or set On Error GoTo 0. Otherwise, you carry stale errors forward.

3. Write specific error messages: Instead of MsgBox Err.Description, provide concrete context: which file, which row, which step. This saves hours of debugging time.

4. Don't use error handling for control flow: Use If...Then or Select Case instead of intentionally triggering errors and catching them with Resume Next. Error handling is for unexpected situations.

5. Use a consistent pattern: Standardize your error-handling template across all modules. The VBA Code Generator can add it automatically.

---

FAQ

Should I use On Error Resume Next or On Error GoTo?

In 95% of cases, On Error GoTo is the right choice. Use Resume Next only for short, targeted checks—such as testing whether a worksheet exists or whether Find returned a result. For the main body of a procedure, always use On Error GoTo with a Cleanup label.

How do I find out which line caused the error?

Use Erl (Error Line) together with line numbers, or use the VBA Debugger with breakpoints and the Immediate Window. In production code, a log with the procedure name and current processing step (e.g., loop row number) is the most practical approach.

Can I define custom error numbers?

Yes, with Err.Raise. Custom error numbers should start at vbObjectError + 512 to avoid conflicts with VBA's internal numbers: Err.Raise vbObjectError + 1001, "MyModule", "Invalid date range".

What is the difference between Err.Raise and Error?

Err.Raise is the modern method and allows custom error numbers, Source, and Description. Error is the legacy syntax and only accepts an error number. Always use Err.Raise.

How do I test my error handling?

Create small test procedures that deliberately trigger errors: reference invalid ranges, divide by zero, open nonexistent files. Verify that the cleanup code runs correctly and the error message is clear and informative.

---

Find and Fix Errors Automatically

The VBA Debug Assistant analyzes your code, finds runtime errors, and delivers corrected code with explanations. The VBA Error Reference also helps you quickly resolve common error messages.

Try it free—10 requests included, no registration required.