6. VBA: Error Handling and Debugging

There are three kinds of errors: syntax errors (the editor highlights them in red), run-time errors (division by zero, no sheet with that name, text instead of a number), and logic errors (the code runs but calculates the wrong thing). Against the second kind, you use a handler:

Sub ImportData()
    On Error GoTo ErrHandler
    Worksheets("Data").Range("A1").Value = "Start"
    ' … main work …
    Exit Sub                       ' without this line the handler always runs
ErrHandler:
    MsgBox "Error " & Err.Number & ": " & Err.Description
End Sub

• On Error GoTo label jumps to the label when an error occurs; Err.Number and Err.Description hold the error code and text;
• On Error Resume Next skips the failing line. It's handy for a single check and dangerous for a whole macro: errors become invisible;
• On Error GoTo 0 restores normal behavior; Err.Clear resets the error code;
• common codes: 9 is no such item (sheet, workbook), 11 is division by zero, 13 is a type mismatch, and 91 is an object not set (you forgot Set).

Debugging: F8 runs one line; F9 sets a breakpoint; Debug.Print x prints a value to the Immediate window (Ctrl+G); the Locals window shows all variables; and a Stop line pauses the macro at the right spot.

The macro below calculates the average order value and doesn't crash on bad data. Reproduce its result with a formula using IFERROR: it's the same idea of "try to calculate, and on an error write a fallback value".

Sub AvgOrderValue()
    Dim r As Long
    On Error Resume Next
    For r = 2 To 7
        Err.Clear
        Cells(r, 4).Value = Cells(r, 2).Value / Cells(r, 3).Value
        If Err.Number <> 0 Then Cells(r, 4).Value = "no data"
    Next r
    On Error GoTo 0
    Range("G1").Value = WorksheetFunction.CountIf(Range("D2:D7"), "no data")
End Sub

The simulator doesn't run macros or scripts. Run the code from the lesson in desktop Excel (VBA) or in Excel for the web (Office Scripts). Here you learn to read code and check yourself with formulas and a quiz.