How do I break on errors?

Question

I have a Function that has some bug in it somewhere causing it to return #VALUE when I try to execute it in excel.

I have no idea where the error is, and stepping through the code is just tedious. So I'd like the debugger to break as soon as an error occurs.

I tried going to Tools->options->General->"Break on All Errors" but noticed no change.

How do I get the VBA IDE to break on an error?

Answer 1

Just add an error handler in your function like the one below. If an error occurs, the IDE will print the error description in the immediate window and stop on Debug.Assert 0. Then press F8 two times to go to the line where the error occured.

Function Test() As Variant
    On Error GoTo ErrHandler

    Dim v(), n&, r&, c&
    For r = 1 To 3
        For c = 1 To 4
            n = n + 1
            ReDim Preserve v(1 To r, 1 To c)
            v(r, c) = n
        Next c
    Next r
    Test = v

    Exit Function

ErrHandler:
    Debug.Print Err.Number & ": " & Err.Description
    Debug.Assert 0
    Resume
End Function