Excel VBA:On Error Goto 语句在 For-Loop 中不起作用 [英] Excel VBA: On Error Goto statement not working inside For-Loop

问题描述

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

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

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

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

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

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)

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

Can anyone explain why the On Error statement stops working?

推荐答案

使用如图所示的代码，当您点击 时，您实际上仍然被视为在错误处理例程中下一个 语句.

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.

更好的架构应该是:

    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.

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

错误处理块和错误跳转

错误处理块，也称为错误处理程序，是通过 On Error Goto : 语句将执行转移到的代码段.此代码应设计为修复问题并在主代码块中恢复执行或终止过程的执行.您不能使用 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 Err1:
    Debug.Print 1 / 0
    ' more code
Err1:
    On Error GoTo Err2:
    Debug.Print 1 / 0
    ' more code
Err2:

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

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.

这篇关于Excel VBA:On Error Goto 语句在 For-Loop 中不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！