Excel VBA无法正确中断错误 [英] Excel VBA not properly breaking on error

问题描述

在Excel 2010中，我有以下代码片段:

In Excel 2010, I have this snip of code:

Application.DisplayAlerts = False                                 
On Error Resume Next
ThisWorkbook.Worksheets("Satisfaction Summary").Delete                      
On Error GoTo 0
Set Report = Worksheets.Add(after:=Worksheets("Main"))
'added this line to force an error:
I = 1 / 0

目标是删除工作表(如果存在)，如果不存在，则忽略该错误，然后以该名称添加工作表.

The goal is to delete the sheet if it exists, if not, ignore the error, then add in a sheet by that name.

当我进入工具|选项|常规并选择所有错误中断时，如果工作表不存在，IDE将停止，并且我无法继续，因为错误.如果我将该设置更改为未处理错误中断，我将完全按照我的预期克服问题.

When I go to Tools|Options|General and select Break on All Errors, if the sheet does NOT exist, the IDE will stop, and I cannot continue because of the error. If I change that setting to Break on Unhandled Errors, I get past that exactly as I would expect.

不幸的是，如果我将其设置为未处理错误中断，那么我遇到的每一个其他错误都会导致调试器停止执行，这不是应该做的.不，它不会暂停执行并向我显示错误所在的代码行，这使我可以调查问题并可能解决问题，它终止代码的执行.它确实给了我一个显示错误号&的对话框.描述，但是当我单击OK时，代码执行已终止，并且不会将我移到错误所在的代码行.

Unfortunately, if I leave it set on Break on Unhandled Errors, every other error I hit causes the debugger to stop execution, which is NOT what it's supposed to do. No, it doesn't pause execution and show me what line of code the error's on, allowing me to investigate the issue and possibly fix it, it terminates execution of the code. It does give me a dialog box showing the error number & description, but when I hit OK, code execution has terminated and it does NOT move me to the line of code where the error is.

有没有其他人遇到过这种情况，并有解决此问题的提示?

Has anyone else ever experienced this and have any tips on resolving the issue?

在代码中添加了一行以强制立即发生错误.选择未处理错误中断时，将弹出此对话框:

EDITS: Added a line to code to force an immediate error. This is the dialog box that pops up when Break on Unhandled Errors is selected:

如您所见，没有MsgBox调用.

As you can see, there is no MsgBox call.

推荐答案

我不是 ON ERROR RESUME NEXT 的忠实拥护者.如何检查工作表是否存在，并在必要时删除?

I'm not a big fan of ON ERROR RESUME NEXT. How about checking if the sheet exists, and deleting if necessary?

Declare SheetExists as Boolean

SheetExists = False

For SheetCounter = 1 To Worksheets.Count
    If Worksheets(SheetCounter).Name = "Satisfaction Summary" Then
        SheetExists = True
    End If
Next i

If SheetExists Then
    ThisWorkbook.Worksheets("Satisfaction Summary").Delete
End If

'copied from code you provided
Set Report = Worksheets.Add(after:=Worksheets("Main"))

这篇关于Excel VBA无法正确中断错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！