Excel VBA：On Error Goto 语句在 For-Loop 中不起作用

Excel VBA: On Error Goto statement not working inside For-Loop

提问by Swiftslide

I'm trying to cycle through a table in excel. The first three columns of this table have text headings, the rest of them have dates as headings. I want to assign those dates, sequentially, to a Date-type variable, and then perform some operations based on the date

我正在尝试在 excel 中循环浏览表格。该表的前三列有文本标题，其余的列有日期作为标题。我想将这些日期按顺序分配给一个日期类型的变量，然后根据日期执行一些操作

To do this I am using a foreach loop on myTable.ListColumns. Since the first three columns do not have date headers, I have tried to set the loop up so that, if there is an error assigning the header string to the date-type variable, the loop goes straight to the next column

为此，我在 myTable.ListColumns 上使用了 foreach 循环。由于前三列没有日期标题，我尝试设置循环，以便在将标题字符串分配给日期类型变量时出错，循环会直接转到下一列

This seems to work for the first column. However, when the second column's header is 'assigned' to the date-type variable, the macro encounters an error even though it is within an error-handling block

这似乎适用于第一列。但是，当第二列的标题被“分配”给日期类型变量时，即使它在错误处理块中，宏也会遇到错误

Dim myCol As ListColumn
For Each myCol In myTable.ListColumns
    On Error GoTo NextCol

    Dim myDate As Date
    myDate = CDate(myCol.Name)

    On Error GoTo 0

    'MORE CODE HERE

NextCol:
    On Error GoTo 0
Next myCol

To reiterate, the error is thrown on the second round of the loop, at the statement

重申一下，错误是在第二轮循环中抛出的，在语句处

myDate = CDate(myCol.Name)

Can anyone explain why the On Error statement stops working?

谁能解释为什么 On Error 语句停止工作？

回答by paxdiablo

With the code as shown, you're actually still considered to be withinthe error handling routine when you strike the nextstatement.

随着如图所示的代码，你实际上仍然被认为是内部错误处理程序时，你罢工的next声明。

That means that subsequent error handlers are not allowed until you resume from the current one.

这意味着在您从当前错误处理程序恢复之前不允许后续错误处理程序。

A better architecture would be:

更好的架构是：

    Dim myCol As ListColumn
    For Each myCol In myTable.ListColumns
        On Error GoTo ErrCol
        Dim myDate As Date
        myDate = CDate(myCol.Name)
        On Error GoTo 0
        ' MORE CODE HERE '
NextCol:
    Next myCol
    Exit Sub ' or something '

ErrCol:
    Resume NextCol

This clearly delineates error handling from regular code and ensures that the currently executing error handler finishes before you try to set up another handler.

这清楚地描述了常规代码中的错误处理，并确保当前正在执行的错误处理程序在您尝试设置另一个处理程序之前完成。

This sitehas a good description of the problem:

这个网站对问题有很好的描述：

---

---

Error Handling Blocks And On Error Goto

错误处理块和错误转到

An error handling block, also called an error handler, is a section of code to which execution is tranferred via a On Error Goto <label>:statement. This code should be designed either to fix the problem and resume execution in the main code block or to terminate execution of the procedure. You can't use the On Error Goto <label>:statement merely skip over lines. For example, the following code will not work properly:

错误处理块，也称为错误处理程序，是通过On Error Goto <label>:语句将执行转移到的一段代码。此代码应设计为修复问题并在主代码块中恢复执行或终止过程的执行。您不能On Error Goto <label>:仅使用该语句跳过行。例如，以下代码将无法正常工作：

    On Error GoTo Err1:
    Debug.Print 1 / 0
    ' more code
Err1:
    On Error GoTo Err2:
    Debug.Print 1 / 0
    ' more code
Err2:

When the first error is raised, execution transfers to the line following Err1:. The error hander is still active when the second error occurs, and therefore the second error is not trapped by the On Errorstatement.

当第一个错误出现时，执行转移到下面的行Err1:。发生第二个错误时，错误处理程序仍处于活动状态，因此On Error语句不会捕获第二个错误。

回答by enderland

You need to add resumeof some sorts in your error handling code to indicate the error handling is over. Otherwise, the first error handler is still active and you are never "resolved."

您需要resume在错误处理代码中添加某种类型的代码以指示错误处理已结束。否则，第一个错误处理程序仍处于活动状态，您永远不会“得到解决”。

See http://www.cpearson.com/excel/errorhandling.htm(specifically the heading "Error Handling Blocks And On Error Goto" and following section)

请参阅http://www.cpearson.com/excel/errorhandling.htm（特别是标题“错误处理块和错误转到”和以下部分）

回答by AjV Jsy

Follow-up to paxdiablo's accepted answer. This is possible, allowing two error traps in the same sub, one after the other :

paxdiablo 接受的答案的后续行动。这是可能的，允许在同一个 sub 中出现两个错误陷阱，一个接一个：

Public Sub test()
    On Error GoTo Err1:
    Debug.Print 1 / 0
    ' more code
Err1:
    On Error GoTo -1     ' clears the active error handler
    On Error GoTo Err2:  ' .. so we can set up another
    Debug.Print 1 / 0
    ' more code
Err2:
    MsgBox "Got here safely"
End Sub

Using On Error GoTo -1cancels the active error handler and allows another to be set up (and err.cleardoesn't do this!). Whether this is a good idea or not is left as an exercise for the reader, but it works!

使用会On Error GoTo -1取消活动的错误处理程序并允许设置另一个错误处理程序（并且err.clear不这样做！）。这是否是一个好主意留给读者作为练习，但它有效！

回答by Profex

Clearing all property settings of the Err object is not the same as resetting the error handler.

清除 Err 对象的所有属性设置与重置错误处理程序不同。

Try this:

尝试这个：

Sub TestErr()
Dim i As Integer
Dim x As Double
    On Error GoTo NextLoop
    For i = 1 To 2
10:     x = i / 0
NextLoop:
        If Err <> 0 Then
            Err.Clear
            Debug.Print "Cleared i=" & i
        End If
    Next
End Sub

You'll notice the just like the OP, it will catch the error properly when i =1but it will fail on line 10 when i = 2, even though we used Err.Clear

你会注意到就像 OP 一样，它会正确地捕获错误，i =1但它会在第 10 行失败i = 2，即使我们使用了Err.Clear