发生错误时如何重新启动循环

Question

我正在使用VBA进行IE自动化（基本上我打开IE并从表格中获取特定的URL，然后使用表格中的凭据登录，然后从网页中提取数据）必须在20个网站上发生，所以我添加了循环，并且可以正常工作。

我想要的是，如果循环中发生任何错误，则循环必须重新启动。我还尝试了"错误获取0，错误获得-1"，但它没有起作用。以下是我的代码 - 对我的编码不佳，请原谅我是新手VBA。

    Sub Get_Data()
    Sheets("Sheet2").Select
    Range("E2").Select
    Range("H6:H120").ClearContents
    Dim IE As Object
    Set IE = CreateObject("InternetExplorer.application")
    IE.Visible = True
    Dim E As Long
    Dim S As Long
    E = Range("A" & Rows.Count).End(xlUp).Row
JumpToHere:
    For j = S To E
        S = Range("H" & Rows.Count).End(xlUp).Row
        Sheets("Sheet2").Select
        Range("E" & S).Select
        ActiveCell.Offset(1, -2).Select
        Dim X As Variant
        X = ActiveCell.Value
        IE.navigate X
        Do
            If IE.ReadyState = 4 Then
                IE.Visible = True
                Exit Do
            Else
                DoEvents
            End If
        Loop
        ActiveCell.Offset(0, 1).Select
        Dim Y As Variant
        Y = ActiveCell.Value
        IE.document.all("username").Value = Y
        ActiveCell.Offset(0, 1).Select
        Dim Z As Variant
        Z = ActiveCell.Value
        IE.document.all("password").Value = Z
        IE.document.all("merchant_login_submit_button").Click
        Application.Wait (Now + TimeValue("0:00:8"))
        Set ElementCol = IE.document.getElementsByTagName("span")
        For Each link In ElementCol
            If link.innerHTML = "Authentication Failed" Then
                ActiveCell.Offset(0, 3).Value = "Authentication Failed"
                GoTo JumpToHere
            End If
        Next
        Set tags = IE.document.getElementsByTagName("input")
        For Each tagx In tags
            If tagx.Value = "Continue to Control Panel" Then
                tagx.Click
                Application.Wait (Now + TimeValue("0:00:3"))
                Exit For
            End If
        Next

        Set ElementCol = IE.document.getElementsByTagName("a")
        For Each link In ElementCol
            If link.innerHTML = "Reports" Then
                link.Click
            End If
        Next
        Application.Wait (Now + TimeValue("0:00:06"))
        Dim checkdate As Integer
        checkdate = Format(Date, "dd") - 1
        IE.document.getElementById("snapshot_group_by").Value = "payment_processor"
        IE.document.getElementById("snapshot_end_date_day").Value = checkdate
        IE.document.all("reports_submit_button").Click
        Application.Wait (Now + TimeValue("0:00:3"))
        Dim ws As Worksheet
        Dim rng As Range
        Dim tbl As Object
        Dim rw As Object
        Dim cl As Object
        Dim tabno As Long
        Dim nextrow As Long
        Dim I As Long
        Set ws = Worksheets.Add
        For Each tbl In IE.document.getElementsByTagName("TABLE")
            tabno = tabno + 1
            nextrow = nextrow + 1
            Set rng = ws.Range("B" & nextrow)
            rng.Offset(, -1) = "Table " & tabno
            For Each rw In tbl.Rows
                For Each cl In rw.Cells
                    rng.Value = cl.outerText
                    Set rng = rng.Offset(, 1)
                    I = I + 1
                Next cl
                nextrow = 0
                Set rng = rng.Offset(1, -I)
                I = 0
            Next rw
        Next tbl
        ws.Cells.ClearFormats
        Sheets("Sheet2").Select
        ActiveCell.Offset(0, 3).Value = ActiveSheet.Previous.Range("F4")
        Application.DisplayAlerts = False
        ActiveSheet.Previous.Delete
        Application.DisplayAlerts = True
        Set ElementCol = IE.document.getElementsByTagName("a")
        For Each link In ElementCol
            If link.innerHTML = "Logout" Then
                link.Click
            End If
        Next
    Next j
End Sub

Answer 1

听起来您的真正问题是您的代码无法正确等待。无论您调用IE.Navigate或任何元素.Click或表格.Submit事件时，都使用适当的等待循环，而不是Application.Wait。

vba html不在所有计算机上运行

否则，您的代码中没有任何活动错误。如下所示，用On Error语句包装循环。

第一个，On Error GoTo MyErrorHandler指示程序在循环中遇到错误时该怎么办。如果有错误，则MyErrorHandler标签下方的代码将执行，并在NextJ标签上恢复。循环完成后，On Error GoTo 0将返回正常（即无）错误处理。循环外发生的任何错误仍然会在运行时引起例外。

Option Explicit
Sub Get_Data()
    '// Dim your variables
    '// Executable code starts here
JumpToHere:    
    For j = S To E
        On Error GoTo MyErrorHandler
        ' Now ANY ERROR, ANYWHERE in the loop will go to the error handler
NextJ:
    Next j
    '// Code below this line won't be subject to the error handler
    On Error GoTo 0
    '// more code if you have it
    ' Exit gracefully if there was no error:
    Exit Sub
    '// Here is the error handler:
    MyErrorHandler:
        Err.Clear()
        Resume NextJ
End Sub

如果您真的想重新启动循环，那么而不是NextJ，请执行Resume JumpToHere。