vba - Access VBA : Is it possible to reset error handling?

Question

我在程序的第一部分中使用

出错时转到开始

假设在我的第二部分中我再次使用

出错时继续下一步

第二个错误陷阱不会被激活，因为第一个错误陷阱仍然处于事件状态。有没有办法在使用第一个错误处理程序后将其停用？

Set objexcel = CreateObject("excel.Application")
                     objexcel.Visible = True
                     On Error GoTo Openwb
                     wbExists = False
                     Set wbexcel = objexcel.Workbooks.Open("C:\REPORT3.xls")
                     Set objSht = wbexcel.Worksheets("Sheet1")
                     objSht.Activate
                     wbExists = True
Openwb:
                     
                     On Error GoTo 0
                     If Not wbExists Then
                     objexcel.Workbooks.Add
                     Set wbexcel = objexcel.ActiveWorkbook
                     Set objSht = wbexcel.Worksheets("Sheet1")

                     End If
                     
                     On Error GoTo 0
                                         
Set db = DBEngine.opendatabase("C:\book.mdb")
Set rs = db.OpenRecordset("records")

Set rs2 = CreateObject("ADODB.Recordset")
rs2.ActiveConnection = CurrentProject.Connection


For Each tdf In CurrentDb.TableDefs
   
   If Left(tdf.Name, 4) <> "MSys" Then
        rs.MoveFirst
        strsql = "SELECT * From [" & tdf.Name & "] WHERE s=15 "

        Do While Not rs.EOF
            On Error Resume Next
            
            rs2.Open strsql 

执行最后一条语句后，我想忽略错误并继续执行下一个表，但错误处理似乎不起作用。

Answer 1

On error goto 0 交给 Visual Basic 进行错误处理(在一般消息框中)

出错时转到标签会将您的代码重定向到标签:

出现错误继续下一步将忽略错误并继续

Resume next 错误发生后将代码重定向到下一行

这意味着指令的组合，例如

    On Error goto 0
    ...
    On Error goto 0

没有意义

如果您想重定向“错误”指令，则必须这样做:

    Do While Not rs.EOF
        
        On Error Resume Next
        rs2.Open strsql
        On error Goto 0

        rs2.moveNext

    Loop

如果你想将错误重定向到标签(用于处理或其他)，然后返回到发生错误的代码，你必须编写如下内容:

    On error goto label
    ...
    ...
    On error goto 0
    exit sub (or function)

    label:
    ....
    resume next
    end function

但我真的建议您对错误管理更加严格。您首先应该能够执行类似的操作:

    Set objexcel = CreateObject("excel.Application")
    objexcel.Visible = True

    On Error GoTo error_Treatment
    wbExists = False
    Set wbexcel = objexcel.Workbooks.Open("C:\REPORT3.xls")
    Set objSht = wbexcel.Worksheets("Sheet1")
    objSht.Activate
    wbExists = True
    On error GoTo 0

    Set db = DBEngine.opendatabase("C:\book.mdb")
    Set rs = db.OpenRecordset("records")

    Set rs2 = CreateObject("ADODB.Recordset")
    rs2.ActiveConnection = CurrentProject.Connection

    For Each tdf In CurrentDb.TableDefs
        ....
        'there are a number of potential errors here in your code'
        'you should make sure that rs2 is closed before reopening it with a new instruction'
        'etc.'
    Next tdf

    Exit sub

    error_treatment:
    SELECT Case err.number
       Case **** '(the err.number raised when the file is not found)'
           objexcel.Workbooks.Add
           Set wbexcel = objexcel.ActiveWorkbook
           Set objSht = wbexcel.Worksheets("Sheet1")
           Resume next 'go back to the code'
       Case **** '(the recordset cannot be opened)'
           ....
           ....
           Resume next 'go back to the code'
       Case **** '(whatever other error to treat)'
           ....
           ....
           Resume next 'go back to the code'
       Case Else
           debug.print err.number, err.description '(check if .description is a property of the error object)'
           'your error will be displayed in the immediate windows of VBA.' 
           'You can understand it and correct your code until it runs'
       End select
    End sub

下一步将是预测代码中的错误，以便不会引发 err 对象。例如，您可以编写一个像这样的通用函数:

    Public function fileExists (myFileName) as Boolean

然后，您可以通过测试 xls 文件是否存在来在代码中利用此功能:

    if fileExists("C:\REPORT3.xls") Then
        Set wbexcel = objexcel.Workbooks.Open("C:\REPORT3.xls")
    Else
       objexcel.Workbooks.Add
       Set wbexcel = objexcel.ActiveWorkbook
    Endif        
    Set objSht = wbexcel.Worksheets("Sheet1")
    objSht.Activate

您不再需要 wbExist 变量...

同样，您应该预见到记录集没有记录的情况。在测试之前写下 rs.MoveFirst 可能会引发错误。然后你应该写

    If rs.EOF and rs.BOF then
    Else
        rs.moveFirst
        Do while not rs.EOF
             rs.moveNext
        Loop
    End If