Error handling and the classic pitfalls

On Error in place of try/catch, a cleanup pattern that always restores state, and the mistakes that bite every VBA author.

On Error instead of try/catch

VBA has no exceptions and no finally. Error handling is a jump to a label with a single global error object, Err, which holds Number, Description and Source until you clear it or leave the procedure.

Public Function ReadNumber(ByVal text As String) As Double
    On Error GoTo Bad
    ReadNumber = CDbl(text)          ' raises error 13 on "abc"
    Exit Function                    ' required: otherwise the handler runs on success

Bad:
    Select Case Err.Number
        Case 13                       ' Type mismatch
            ReadNumber = 0
        Case Else
            ReadNumber = 0
            Debug.Print "unexpected: " & Err.Number & " - " & Err.Description
    End Select
End Function

Public Sub WrapUp()
    On Error GoTo Cleanup            ' VBA's stand-in for finally
    Dim f As Long
    f = FreeFile
    Open "C:\temp\input.txt" For Input As #f

    Dim line As String
    Do While Not EOF(f)
        Line Input #f, line
        ' ... use line ...
    Loop
    Close #f

    Exit Sub
Cleanup:
    On Error Resume Next             ' closing may itself fail; do not loop
    Close #f
    On Error GoTo 0
    If Err.Number <> 0 Then
        MsgBox "Failed: " & Err.Number & " - " & Err.Description, vbCritical
        Err.Clear
    End If
End Sub

• On Error GoTo 0 disables the current handler; On Error Resume Next ignores errors until you turn it off, which is how bugs hide.
• An Exit Sub or Exit Function placed just before the handler label is what stops the handler running when nothing failed.
• Errors do not unwind the call stack: a handler in a caller sees the same error object, but a handler never set in a callee leaves the error unhandled and shows the standard dialog.
• Err.Raise 5, "MyModule", "custom message" raises your own error, and Err.Clear resets the object to zero.

The pitfalls that actually bite

' Two conversions that look equivalent and are not
Debug.Print CInt(2.5), CLng(2.5)        ' 2  2    banker's rounding, not half-up
Debug.Print Application.Round(2.5, 0)   ' 2
Debug.Print Int(-2.5), Fix(-2.5)        ' -3  -2  Int floors, Fix truncates

' Safe comparison for floating point
Public Function NearlyEqual(ByVal a As Double, ByVal b As Double, _
                            Optional ByVal tol As Double = 0.0000001) As Boolean
    NearlyEqual = Abs(a - b) < tol
End Function

' Variant results need explicit handling: IsError tests a cell error value
Public Sub ShowErrors()
    Dim v As Variant
    v = Application.VLookup("missing", Range("A1:B10"), 2, False)
    If IsError(v) Then
        Debug.Print "not found: " & CStr(CVErr(v))
    Else
        Debug.Print v
    End If
End Sub

Making it testable

Option Explicit

' Keep the logic free of Excel objects so it can be called from a test harness.
Public Function NetTotal(ByVal amounts As Variant, _
                         ByVal refunds As Variant) As Double
    Dim total As Double, i As Long
    For i = LBound(amounts) To UBound(amounts)
        If IsNumeric(amounts(i)) Then total = total + CDbl(amounts(i))
    Next i
    For i = LBound(refunds) To UBound(refunds)
        If IsNumeric(refunds(i)) Then total = total - CDbl(refunds(i))
    Next i
    NetTotal = total
End Function

Public Sub TestNetTotal()
    Dim got As Double
    got = NetTotal(Array(10, 20, 30), Array(5))
    If got <> 55 Then
        Debug.Print "FAIL: expected 55, got " & got
    Else
        Debug.Print "PASS"
    End If
End Sub

' Wrap anything that touches the UI so the pure parts stay testable
Public Sub ImportAll()
    On Error GoTo Cleanup
    Application.ScreenUpdating = False
    Application.EnableEvents = False

    Dim rows As Variant
    rows = ThisWorkbook.Worksheets("Input").Range("A2:C100").Value2
    ' ... validate, then write ...
    Debug.Print NetTotal(Array(1, 2), Array(0))

    Exit Sub
Cleanup:
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    If Err.Number <> 0 Then Debug.Print Err.Number & ": " & Err.Description
End Sub

• Pass arrays into functions and return values: a procedure that reads cells directly can only be tested by opening the workbook and running it.
• Assertions via Debug.Print "FAIL: ..." are crude but work, and they cost nothing in a production file once the test procedures are not called.
• Keep a known-good copy of the workbook and a version history: VBA has no diff, so comparing two modules by eye is the only review you get.
• Delete or password-protect the VBA project before distribution if the workbook is meant to be used rather than read.

FAQ

Related

Macros, worksheets and ranges The VBA editor and the language

Last refreshed 2026-09-18.