vba 循环中的vba错误处理

vba error handling in loop

提问by justin cress

New to vba, trying an 'on error goto' but, I keep getting errors 'index out of range'.

vba 新手，尝试“错误转到”，但是，我不断收到错误“索引超出范围”。

I just want to make a combo box that is populated by the names of worksheets which contain a querytable.

我只想制作一个由包含查询表的工作表名称填充的组合框。

    For Each oSheet In ActiveWorkbook.Sheets
        On Error GoTo NextSheet:
         Set qry = oSheet.ListObjects(1).QueryTable
         oCmbBox.AddItem oSheet.Name

NextSheet:
    Next oSheet

I'm not sure whether the problem is related to nesting the On Error GoTo inside a loop, or how to avoid using the loop.

我不确定问题是否与在循环中嵌套 On Error GoTo 或如何避免使用循环有关。

回答by Gavin Smith

The problem is probably that you haven't resumed from the first error. You can't throw an error from within an error handler. You should add in a resume statement, something like the following, so VBA no longer thinks you are inside the error handler:

问题可能是您没有从第一个错误中恢复。您不能从错误处理程序中抛出错误。您应该添加一个 resume 语句，如下所示，以便 VBA 不再认为您在错误处理程序中：

For Each oSheet In ActiveWorkbook.Sheets
    On Error GoTo NextSheet:
     Set qry = oSheet.ListObjects(1).QueryTable
     oCmbBox.AddItem oSheet.Name
NextSheet:
    Resume NextSheet2
NextSheet2:
Next oSheet

回答by Patrick Honorez

As a general way to handle error in a loop like your sample code, I would rather use:

作为在示例代码等循环中处理错误的一般方法，我宁愿使用：

on error resume next
for each...
    'do something that might raise an error, then
    if err.number <> 0 then
         ...
    end if
 next ....

回答by Joe

How about:

怎么样：

    For Each oSheet In ActiveWorkbook.Sheets
        If oSheet.ListObjects.Count > 0 Then
          oCmbBox.AddItem oSheet.Name
        End If
    Next oSheet

回答by AndrewM

There is another way of controlling error handling that works well for loops. Create a string variable called hereand use the variable to determine how a single error handler handles the error.

还有另一种控制错误处理的方法，它适用于循环。创建一个名为的字符串变量here并使用该变量来确定单个错误处理程序如何处理错误。

The code template is:

代码模板为：

On error goto errhandler

Dim here as String

here = "in loop"
For i = 1 to 20 
    some code
Next i

afterloop:
here = "after loop"
more code

exitproc:    
exit sub

errhandler:
If here = "in loop" Then 
    resume afterloop
elseif here = "after loop" Then
    msgbox "An error has occurred" & err.desc
    resume exitproc
End if

回答by AndrewM

I do not want to craft special error handlers for every loop structure in my code so I have a way of finding problem loops using my standard error handler so that I can then write a special error handler for them.

我不想为代码中的每个循环结构制作特殊的错误处理程序，所以我有一种使用标准错误处理程序查找问题循环的方法，这样我就可以为它们编写一个特殊的错误处理程序。

If an error occurs in a loop, I normally want to know about what caused the error rather than just skip over it. To find out about these errors, I write error messages to a log file as many people do. However writing to a log file is dangerous if an error occurs in a loop as the error can be triggered for every time the loop iterates and in my case 80 000 iterations is not uncommon. I have therefore put some code into my error logging function that detects identical errors and skips writing them to the error log.

如果循环中发生错误，我通常想知道是什么导致了错误，而不是跳过它。为了找出这些错误，我像许多人一样将错误消息写入日志文件。但是，如果循环中发生错误，则写入日志文件是危险的，因为每次循环迭代时都会触发错误，在我的情况下，80 000 次迭代并不少见。因此，我将一些代码放入我的错误日志功能中，用于检测相同的错误并跳过将它们写入错误日志。

My standard error handler that is used on every procedure looks like this. It records the error type, procedure the error occurred in and any parameters the procedure received (FileType in this case).

我在每个过程中使用的标准错误处理程序看起来像这样。它记录错误类型、发生错误的过程以及过程接收到的任何参数（在本例中为 FileType）。

procerr:
    Call NewErrorLog(Err.number, Err.Description, "GetOutputFileType", FileType)
    Resume exitproc

My error logging function which writes to a table (I am in ms-access) is as follows. It uses static variables to retain the previous values of error data and compare them to current versions. The first error is logged, then the second identical error pushes the application into debug mode if I am the user or if in other user mode, quits the application.

我写入表的错误日志功能（我在 ms-access 中）如下。它使用静态变量来保留错误数据的先前值并将它们与当前版本进行比较。记录第一个错误，然后第二个相同的错误将应用程序推入调试模式，如果我是用户，或者在其他用户模式下退出应用程序。

Public Function NewErrorLog(ErrCode As Variant, ErrDesc As Variant, Optional Source As Variant = "", Optional ErrData As Variant = Null) As Boolean
On Error GoTo errLogError

    'Records errors from application code
    Dim dbs As Database
    Dim rst As Recordset

    Dim ErrorLogID As Long
    Dim StackInfo As String
    Dim MustQuit As Boolean
    Dim i As Long

    Static ErrCodeOld As Long
    Static SourceOld As String
    Static ErrDataOld As String

    'Detects errors that occur in loops and records only the first two.
    If Nz(ErrCode, 0) = ErrCodeOld And Nz(Source, "") = SourceOld And Nz(ErrData, "") = ErrDataOld Then
        NewErrorLog = True
        MsgBox "Error has occured in a loop: " & Nz(ErrCode, 0) & Space(1) & Nz(ErrDesc, "") & ": " & Nz(Source, "") & "[" & Nz(ErrData, "") & "]", vbExclamation, Appname
        If Not gDeveloping Then  'Allow debugging
            Stop
            Exit Function
        Else
            ErrDesc = "[loop]" & Nz(ErrDesc, "")  'Flag this error as coming from a loop
            MsgBox "Error has been logged, now Quiting", vbInformation, Appname
            MustQuit = True  'will Quit after error has been logged
        End If
    Else
        'Save current values to static variables
        ErrCodeOld = Nz(ErrCode, 0)
        SourceOld = Nz(Source, "")
        ErrDataOld = Nz(ErrData, "")
    End If

    'From FMS tools pushstack/popstack - tells me the names of the calling procedures
    For i = 1 To UBound(mCallStack)
        If Len(mCallStack(i)) > 0 Then StackInfo = StackInfo & "\" & mCallStack(i)
    Next

    'Open error table
    Set dbs = CurrentDb()
    Set rst = dbs.OpenRecordset("tbl_ErrLog", dbOpenTable)

    'Write the error to the error table
    With rst
        .AddNew
        !ErrSource = Source
        !ErrTime = Now()
        !ErrCode = ErrCode
        !ErrDesc = ErrDesc
        !ErrData = ErrData
        !StackTrace = StackInfo
        .Update
        .BookMark = .LastModified
        ErrorLogID = !ErrLogID
    End With


    rst.Close: Set rst = Nothing
    dbs.Close: Set dbs = Nothing
    DoCmd.Hourglass False
    DoCmd.Echo True
    DoEvents
    If MustQuit = True Then DoCmd.Quit

exitLogError:
    Exit Function

errLogError:
    MsgBox "An error occured whilst logging the details of another error " & vbNewLine & _
    "Send details to Developer: " & Err.number & ", " & Err.Description, vbCritical, "Please e-mail this message to developer"
    Resume exitLogError

End Function

Note that an error logger has to be the most bullet proofed function in your application as the application cannot gracefully handle errors in the error logger. For this reason, I use NZ() to make sure that nulls cannot sneak in. Note that I also add [loop] to the second identical error so that I know to look in the loops in the error procedure first.

请注意，错误记录器必须是应用程序中最可靠的功能，因为应用程序无法正常处理错误记录器中的错误。出于这个原因，我使用 NZ() 来确保不会出现空值。请注意，我还将 [loop] 添加到第二个相同的错误中，以便我知道首先查看错误过程中的循环。

回答by Makah

Actualy the Gabin Smith's answer needs to be changed a bit to work, because you can't resume with without an error.

实际上，Gabin Smith 的答案需要稍作更改才能起作用，因为您无法在没有错误的情况下继续。

Sub MyFunc()
...
    For Each oSheet In ActiveWorkbook.Sheets
        On Error GoTo errHandler:
        Set qry = oSheet.ListObjects(1).QueryTable
        oCmbBox.AddItem oSheet.name

    ...
NextSheet:
    Next oSheet

...
Exit Sub

errHandler:
Resume NextSheet        
End Sub

回答by Reafidy

What about?

关于什么？

If oSheet.QueryTables.Count > 0 Then
  oCmbBox.AddItem oSheet.Name
End If 

Or

或者

If oSheet.ListObjects.Count > 0 Then
    '// Source type 3 = xlSrcQuery
    If oSheet.ListObjects(1).SourceType = 3 Then
         oCmbBox.AddItem oSheet.Name
    End IF
End IF