Excel VBA:错误Goto语句在for循环中不起作用

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语句停止工作吗?

5 个解决方案

#1

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

使用如下所示的代码，当执行下一个语句时，您实际上仍然被认为处于错误处理例程中。

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 site has 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 :语句将执行传递给该代码。这段代码应该被设计成修复主代码块中的问题并恢复执行，或者终止过程的执行。不能使用On Error Goto :语句仅仅跳过行。例如，下列代码将不能正常工作:

    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 Error statement.

当第一个错误被提出时，执行转移到以下Err1:。当第二个错误发生时，error hander仍然是活动的，因此第二个错误不会被On error语句捕获。

#2

You need to add resume of 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."

您需要在错误处理代码中添加一些恢复，以表明错误处理已经结束。否则，第一个错误处理程序仍然是活动的，并且永远不会“解析”您。

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(特别是标题“错误处理块和错误Goto”和以下部分)

#3

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

对paxdiablo认可的答案的跟进。这是可能的，允许两个错误陷阱在同一个子，一个接一个:

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 -1 cancels the active error handler and allows another to be set up (and err.clear doesn'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)。清楚不这样做!)这是不是一个好主意留给读者作为练习，但它确实有效!

#4

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 =1 but it will fail on line 10 when i = 2, even though we used Err.Clear

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

#5

I had a VBA program that had worked a long time. Then the error trapping stopped working. Looked at all the threads. Somehow error trapping on my computer had been changed to break on all lines. This is found on the the Developer / Visual Basic / Tools / Options / General Tab (Error Trapping). It should be set to Break on Unhandled Errors.

我有一个VBA项目，它已经运行了很长时间。然后错误捕获停止工作。看看所有的线。在我的电脑上的错误捕获已经被改变，以打破所有的线。这可以在Developer / Visual Basic / Tools / Options / General选项卡(错误捕获)中找到。它应该被设置为对未处理的错误进行中断。

#1