Excel: Break on Error does not work in custom function

Question

It appears that when VBA code is called from a cell formula (ie, "Custom Function" or UDF), a typical VBA Break On Error does not work.

The only place I can find this behavior is a couple of lines in an article titled “Developing Custom Functions for Excel 2007 and Excel Services” :

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

Even if you set Error Trapping to "Break on All Errors" and one step of your code **, you will never see the VBA Run-time Error dialog box - Excel simply calmly stops executing without telling you what went wrong. Of course, this makes debugging more difficult than necessary.

There are several possible workarounds related to On Error , but I would prefer not to clutter up my code to find out where the error was raised.

Is there any Excel / VBA option that I skipped that would make Break On Error work fine? I am using Excel 2003.

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

Answer 1

I know that it is not funny to hear this when you specifically requested something else than On Error , but I am afraid that this is the only option for my knowledge.

You can simply use the On Error Goto ErrorHandler during debugging (and comment on it to get the default On Error Goto 0 at another time). ErrorHandler can only contain a couple of lines, so it does not clutter up your code too much:

 ErrorHandler: MsgBox Err.Number & vbCrLf & Err.Source & vbCrLf & Err.Description Resume 

always with a breakpoint on Resume to return you to the error-causing message at step - and avoid an endless loop of error handling.

Answer 2

The best method is to use the On Error GoTo ErrorHandler with a Stop link followed by a Resume .

You need to be careful not to get into an infinite loop with Resume , since UDFs run almost continuously (if this happens, press Esc )

So in your code add: On Error GoTo ErrorHandler next to the beginning of your function, and then at the end to End Function :

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

Exit Function stops the execution of this code during normal operation. If an error occurs, a message with detailed information will appear, the code will break (due to Stop ), and you can return to your code (jumping back using the Resume instruction) using the following command on the debug toolbar.

Of course, don't forget to comment on the On Error GoTo ErrorHandler when you are happy with your UDF.