Skip to content
Tutorials Center
My learning

Topics

Lesson 30 of 37 ยท Forms, Events and Errors

VBA Error Handling and Debugging

Handle run-time errors in VBA with On Error GoTo, Resume, the Err object and custom errors, and debug with breakpoints, Watch, Locals and the Immediate window.

Every real macro meets errors: a file is missing, a sheet was renamed, a user types text where a number is needed. If you do nothing, VBA shows the cryptic "Run-time error" dialog with a Debug button, and your macro stops halfway, maybe leaving ScreenUpdating off or half a report written.

Good error handling does three things: it expects what can fail, it recovers or reports clearly, and it always restores the application state. This lesson covers the On Error statements, the Err object, cleanup patterns, raising your own errors, and the debugging tools that find mistakes faster.

VBA is read-only on this site. The runnable stand-ins are JavaScript try/catch/finally examples that follow the same structure, which maps directly to VBA's cleanup pattern.

Kinds of errors

Kind Example When found
Syntax error If x Then without End If When you type or compile
Compile error Undeclared variable under Option Explicit Before the macro runs
Run-time error Divide by zero (11), subscript out of range (9), type mismatch (13), object not set (91), file not found (53) While it runs
Logic error The code runs but the answer is wrong Only through testing and debugging

Start every module with Option Explicit; it turns typos in variable names into compile errors. In the editor choose Tools > Options > Require Variable Declaration so it is added automatically.

On Error statements

VB
On Error GoTo Handler      ' jump to a label when an error occurs
On Error Resume Next       ' ignore the error and continue with the next line
On Error GoTo 0            ' turn error handling off (default behaviour)

Resume Next is dangerous when used widely, because errors vanish silently. Use it only around a single line that you then check:

VB
Dim ws As Worksheet
On Error Resume Next
Set ws = ThisWorkbook.Worksheets("Archive")
On Error GoTo 0
If ws Is Nothing Then
    MsgBox "There is no Archive sheet."
    Exit Sub
End If

The standard handler with cleanup

VB
Sub ImportData()
    On Error GoTo ErrHandler
    Application.ScreenUpdating = False
    Application.EnableEvents = False

    ' ... the real work ...
    Workbooks.Open "C:\Data\input.xlsx"

CleanExit:
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    Exit Sub

ErrHandler:
    MsgBox "Import failed (" & Err.Number & "): " & Err.Description, vbCritical
    Resume CleanExit
End Sub

The pattern: normal path ends with Exit Sub so it never falls into the handler; the handler reports and then Resumes at a cleanup label so state is always restored. There are three forms of Resume:

  • Resume retries the line that failed (after you fix the cause, careful with endless loops).
  • Resume Next continues after the failed line.
  • Resume Label continues at a label.

The Err object

Err.Number, Err.Description and Err.Source describe the last error. Err.Clear resets it. Err.Raise creates one.

VB
Function SafeDivide(a As Double, b As Double) As Double
    If b = 0 Then Err.Raise 1001, "SafeDivide", "Divisor must not be zero"
    SafeDivide = a / b
End Function

Custom error numbers should start at 513 or use vbObjectError + n, to avoid clashing with built-in numbers. A handler in the calling procedure catches them, so low-level helpers can raise errors and the top-level macro decides how to tell the user.

Handling specific errors

VB
ErrHandler:
    Select Case Err.Number
        Case 53        ' file not found
            MsgBox "Please check the file path."
        Case 9         ' subscript out of range (e.g. missing sheet)
            MsgBox "A required sheet is missing."
        Case Else
            MsgBox "Unexpected error " & Err.Number & ": " & Err.Description
    End Select
    Resume CleanExit

Debugging tools

  • Breakpoint (F9 on a line) pauses the macro there. F8 steps one line at a time; Shift+F8 steps over a call; Ctrl+Shift+F8 steps out.
  • Locals window shows every variable of the current procedure, and Watches track expressions or break when a value changes.
  • Immediate window (Ctrl+G): run statements (?Range("A1").Value) and see Debug.Print output.
  • Call Stack (Ctrl+L) shows which procedures led here.
  • Debug.Assert pauses when a condition is False: Debug.Assert lastRow > 1.
  • Tools > Options > General > Error Trapping: use "Break on Unhandled Errors" for normal work and "Break on All Errors" to find hidden ones.

A habit that saves time: reproduce the bug, shrink the example until the failing line is obvious, then step through with F8 watching Locals.

Try it: try/catch/finally cleanup

JS The ErrHandler / CleanExit pattern

Press Run (or Ctrl + Enter) to see the console output here.

No output.

JS Err.Raise and Select Case on error numbers

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Common mistakes

  • Using On Error Resume Next for a whole procedure and never noticing failures.
  • Forgetting Exit Sub before the handler label, so the handler runs after every success.
  • Leaving ScreenUpdating, EnableEvents or DisplayAlerts off after an error.
  • Showing only Err.Description to users; include where it happened for easier support, and log to a file or sheet in larger tools.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which statement jumps to a labeled handler when an error occurs?

    On Error GoTo Label transfers control to that label when an error is raised.
  2. 2. Why put Exit Sub before the ErrHandler label?

    Without Exit Sub, execution would fall through into the handler after a successful run.
  3. 3. What is the best use of On Error Resume Next?

    Keep it narrow, then test the result and restore normal handling.
  4. 4. What does F8 do in the VBA editor?

    F8 is Step Into, the main tool for following what the code does.

Summary

  • Use Option Explicit, and expect failures: files, sheets, user input.
  • On Error GoTo with a handler and a CleanExit label restores state and reports Err.Number and Err.Description.
  • Keep Resume Next to single lines and check afterwards.
  • Err.Raise signals your own errors; handle specific numbers with Select Case.
  • Debug with breakpoints, F8, Locals, Watches and the Immediate window.

Next: File System Operations in VBA