Excel: Break on Error doesn't work in user-defined function

Question

It seems that when VBA code is called from a cell formula (ie. a "User Defined Function" or UDF), the usual VBA Break On Error doesn't work.

The only place I can find this behavior documented is a couple of lines in an article called "Developing User-Defined Functions for Excel 2007 and Excel Services":

Error handling returns #VALUE errors. All exceptions thrown by the UDF code are returned into the Excel worksheet as #VALUE errors.

Even if you set Error Trapping to "Break on All Errors" and single-step your code**, you will never see the VBA Run-time Error dialog - Excel just quietly abandons execution without telling you what went wrong. Of course this makes debugging more difficult than it needs to be.

There are some potential workarounds involving On Error but I'd rather not clutter up my code just to find out where an error was raised.

Is there some Excel / VBA option I've overlooked which will make Break On Error work normally? I'm using Excel 2003.

** The only way to get into the debugger when called from a cell is to set a breakpoint or use a Stop statement

Answer 1

Best method would be to use the On Error GoTo ErrorHandler with a Stop reference followed by Resume.

Need to be careful not to get into an infinite loop with resume as UDFs run almost continually (if this happens hit Esc repeatedly)

So in your code add: On Error GoTo ErrorHandler near the start of your function and then right at the end before End Function:

Exit Function
ErrorHandler:
MsgBox Err.Number & vbCrLf & Err.Source & vbCrLf & Err.Description
Stop
Resume

The Exit Function stops this code running in normal operation. If an error is encountered a messagebox with the details will pop up, the code will break (due to Stop) and you can step through back into your code (hopping back via the resume statement) using the next line command on the debug toolbar.

Of course don't forget to comment out the On Error GoTo ErrorHandler line when you're happy with your UDF.