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?

score 4 · Answer 1 · answered Apr 16 '16 at 18:11

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

+1 for this. Should be mentioned that `Resume` is the reason for the behavior that `F8` will go back to the error line. Good approach for debugging `UDF`s. — Axel Richter, Apr 17 '16 at 05:43

Gary's Student · Accepted Answer · 2016-04-16T16:19:15.167

2

Something like:

Public Function dividddeee(a As Variant, b As Variant) As Double
    On Error GoTo wtf
    dividddeee = a / b
    Exit Function
wtf:
    On Error GoTo 0
    MsgBox "Houston, we've had a problem here"
    MsgBox a & vbCrLf & b
End Function

edited Apr 16 '16 at 16:19

answered Apr 16 '16 at 15:51

Gary's Student

91,920
8
47
75

2

`Debug.Assert False` is much more useful than a `MsgBox` for actual debugging - it lets you check values, etc. instead of just letting Houston know there was a problem. – Comintern Apr 16 '16 at 16:13

Comintern · Answer 3 · 2016-04-16T16:33:06.280

If you add error handlers, you can take advantage of Debug.Assert to force a break if you don't want the standard handler to execute. You can define a compiler constant to just let your error handlers deal with it when you release it to the wild. If you want to see what specific line caused the error, you can put Resume Next after the Debug.Assert. When you step through it, it will take you to the line immediately after the one that caused the error.

Drop this small demo into Module and run the Sub with both Release = True and Release = False for an example:

Option Explicit
#Const Release = False

Private Sub Demo()
    Debug.Print DivByZero(5)
End Sub

Public Function DivByZero(inValue As Integer) As Double
    On Error GoTo Handler
    DivByZero = inValue / 0

    Exit Function
Handler:
    #If Release Then
        MsgBox Err.Description & " in DivByZero"
    #Else
        Debug.Assert False
        Resume Next
    #End If
End Function

score 0 · Answer 4 · answered Apr 16 '16 at 16:01

If you call a VBA function as UDF the settings of the VBA IDE are not involved. So no chance for error debugging this way.

Try calling the function from a test Sub. Then the error debugging will work.

But there are some things a function cannot do as a UDF but can do called from a Sub. If one of those things is the reason for the #VALUE error, then no way around setting a breakpoint in the function and stepping forward until the next step is not possible. The last line in stepping is the error line.

score 0 · Answer 5 · answered Apr 17 '16 at 14:11

You should really mention if the function is

Called from an Excel cell.
Has an event handler.
Show how your variables are declared.

If called from a cell, the inputs to the function can cause you problems with different inputs. Sometimes preventing the call of the function if the types significantly change to something unexpected. For example a parameter declared as variant in its signature will pass in an error but fail in the function. You may be trapping this error and returning #VALUE in the code. No way for us to know that.

If you have an event handler, for a quick test, you could put a 'Stop' in the event handler to stop like you are asking. If not you can put one in as already stated. Assertions are nice, I like them and use a lot of them - but here since you know the function and are working on this explicit problem a Stop should be good enough for your testing. Just don't save it for production code. Make a Test copy of the book.

Hope that helps.

How do I break on errors?

5 Answers5

Linked