Excel：Break on Error在用户定义的函数中不起作用

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.

似乎从单元格公式（即“用户定义的函数”或UDF）调用VBA代码时，通常的VBA Break On Error不起作用。

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":

我能找到这种行为的唯一地方是一篇名为“为Excel 2007和Excel Services开发用户定义函数”的文章中的几行：

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

错误处理返回#VALUE错误。 UDF代码抛出的所有异常都将作为#VALUE错误返回到Excel工作表中。

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.

即使您将错误陷阱设置为“中断所有错误”并单步执行代码**，您也永远不会看到VBA运行时错误对话框 - Excel只是静静地放弃执行而不告诉您出了什么问题。当然，这使得调试比实际需要更加困难。

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.

有一些潜在的解决方法涉及On Error但我宁愿不弄乱我的代码只是为了找出引发错误的位置。

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

是否有一些我忽略的Excel / VBA选项会使Break On Error正常工作？我正在使用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

**从单元格调用时进入调试器的唯一方法是设置断点或使用Stop语句

2 个解决方案

#1

I'm aware that it isn't any fun hearing this when you specifically asked for something else than On Error, but I'm afraid it's the only option to my knowledge.

我知道当你特别要求其他东西而不是On Error时听到这个并不是很有趣，但我担心这是我所知道的唯一选择。

You could just use On Error Goto ErrorHandler while debugging (and comment it out to get the default On Error Goto 0 at other times). The ErrorHandler could have just a couple of lines so it doesn't clutter up your code too much:

您可以在调试时使用On Error Goto ErrorHandler（并将其注释掉以在其他时间获取默认的On Error Goto 0）。 ErrorHandler可能只有几行，因此它不会使代码混乱太多：

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

always with a breakpoint on Resume to guide you back to the error-causing statement upon stepping -- and to avoid an infinite error-handling loop.

总是在Resume上有一个断点，引导你在步进时回到导致错误的语句 - 并避免无限的错误处理循环。

#2

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

最好的方法是使用On Error GoTo ErrorHandler和Stop引用，然后是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)

因为UDF几乎不断运行（如果这种情况反复发生在Esc上），需要注意不要进入带有简历的无限循环

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

所以在你的代码中添加：On Error GoTo ErrorHandler在函数开头附近，然后在End函数结束之前：

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.

Exit函数在正常操作中停止此代码运行。如果遇到错误，将弹出带有详细信息的消息框，代码将中断（由于Stop），您可以使用调试工具栏上的下一行命令逐步返回代码（通过resume语句跳回）。

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

当然，当您对UDF感到满意时，不要忘记注释On Error GoTo ErrorHandler行。

#1