Today's Goals

  • Understand why error handling matters
  • Master the On Error statement
  • Write your own error handlers

Key Concepts

1. The On Error Statement

vba
' Jump to a label when an error occurs
On Error GoTo LabelName

' Ignore the error and keep going
On Error Resume Next

' Turn error trapping back on
On Error GoTo 0

Three forms, three jobs: On Error GoTo LabelName is the safety net for the main flow, On Error Resume Next skips over a single, expected risk, and On Error GoTo 0 restores default error reporting. The recommended pattern is "GoTo as the net, plus narrowly targeted Resume Next exemptions".

2. The Error Handling Structure

vba
Sub Example()
    On Error GoTo ErrorHandler
    ' Code that may fail
    Exit Sub

ErrorHandler:
    ' Handle the error here
    MsgBox "Error: " & Err.Description
End Sub

Two details matter. There must be an Exit Sub before the handler — otherwise normal execution "falls into" the error-handling section — and once you are done handling, clear the error with Err.Clear.

3. The Err Object

Err holds the most recent run-time error: Err.Number is the error code (0 means no error), Err.Description is the English description, and Err.Clear resets it. The standard test is If Err.Number <> 0 Then.

Today's Code

vba
Sub ErrorHandlerExample()
    On Error GoTo ErrorHandler

    Dim ws As Worksheet

    ' Operation that may fail
    Set ws = Worksheets("NoSuchSheet")
    ws.Range("A1").Value = "test"

    Exit Sub

ErrorHandler:
    MsgBox "An error occurred!" & vbCrLf & _
           "Number: " & Err.Number & vbCrLf & _
           "Description: " & Err.Description, vbCritical, "Error Info"
End Sub

Sub ResumeNextExample()
    On Error Resume Next

    ' Keep going even if a line fails
    Worksheets("Sheet1").Range("A1").Value = "test"
    Worksheets("NoSuchSheet").Range("A1").Value = "this line will not raise an error"

    On Error GoTo 0  ' Restore error trapping

    MsgBox "Code finished", vbInformation
End Sub

Real-World Scenario

You export results to a CSV, but the target folder may not exist — let error handling catch that instead of crashing:

vba
Sub ExportData()
    On Error GoTo ErrorHandler

    Open "D:\Reports\Summary.csv" For Output As #1
    Print #1, "Date,Sales"
    Close #1
    MsgBox "Export succeeded", vbInformation
    Exit Sub

ErrorHandler:
    Close #1  ' Prevent a leaked file handle
    MsgBox "Export failed: " & Err.Description, vbCritical
End Sub

If the folder is missing, Open raises Run-time error '76': Path not found and control jumps to ErrorHandler instead of crashing. In real projects the MsgBox is usually replaced with logging.

Exercises

  1. Build a program with complete error handling
  2. Handle the file-not-found error
  3. Log errors to a file

Exercise Solutions

  1. Use the On Error GoTo ErrorHandler structure and handle the error at the ErrorHandler label.
  2. Before opening a file, check whether it exists with the Dir function, or catch the failure with On Error Resume Next.
  3. Write the error information to a text file or a cell using the Open statement and Print #.

Common Errors and Troubleshooting

  1. Run-time error '9': Subscript out of range — you referenced a sheet that does not exist. Probe for it with Resume Next plus Err.Number.
  2. Run-time error '13': Type mismatch — InputBox returns an empty string when the user cancels, and it goes straight into a calculation; check for "" first.
  3. Run-time error '91': Object variable or With block variable not set — the Set was swallowed by Resume Next, so the object is still Nothing.
  4. Normal execution falls into the error handler — you forgot Exit Sub, so the main flow runs on into the code under the label.
  5. Forgetting On Error GoTo 0 after Resume Next — error trapping stays off, and later errors are silently skipped.

Extension Exercises

  1. Rework the real-world scenario: use Dir to check whether the output file already exists; if it does, Kill it first and handle that failure too.
  2. Write a SafeOpenWorkbook function that returns Nothing on failure, and have callers test the result with Is Nothing.

由在线工具箱(www.vba.net)整理制作