vba - 如何有效地处理错误以防止产生误导性的结果？

Question

我已经在vba中编写了一些代码，以在某些网站中针对某些名称查找某些身份。如果一切都按正确的顺序执行，则代码运行良好，这意味着如果链接有效，名称与a标记匹配，最后正则表达式可以找到身份。如果这三个中的任何一个或所有三个都是错误的搜索，则脚本将引发错误。我已经在下面的脚本中指定了发生错误的位置。

我希望您的专家为我提供有关如何处理错误的任何解决方案，并让我的脚本继续运行，直到所有链接都用尽。

由于我对VBA不太了解，因此我尝试使用On error resume next跳过错误。但是，当我查看结果时，结果显然是一团糟。我正在粘贴一个粗略的示例，当我使用On error resume next时会得到什么。

Sub Identity_Finder()

    Dim http As New XMLHTTP60, html As New HTMLDocument
    Dim post As Object, link As Variant, refined_links As String
    Dim rxp As New RegExp, identity As Object

    For Each link In [{"http://spltech.in/","http://www.unifrostindia.com/","http://www.unitfrostindia.com/","http://www.greenplanet.in/"}]
        With http
            .Open "GET", link, False
            .send                    '''throws here the first error if the link is invalid
            html.body.innerHTML = .responseText
        End With

        For Each post In html.getElementsByTagName("a")
            If InStr(post.innerText, "certain_name") > 0 Then refined_links = post.href: Exit For
        Next post

        With http
            .Open "GET", refined_links, False
            .send                          ''throws another error here if no such link is found
        End With

        With rxp
            .Pattern = "some_regex"
            .Global = True
            Set identity = .Execute(http.responseText) 
        End With

        r = r + 1: Cells(r, 1) = link
        Cells(r, 2) = identity(0)    ''''throws another error here if no such identity is noticed

    Next link
End Sub

使用 On error resume next后我得到了什么:

John executive
Mac lawyer
lulu lawyer
Robin lawyer 
Cathy student

预期产量:

John executive
Mac lawyer
lulu 
Robin  
Cathy student

当我使用 On error resume next时，空字段(当未找到它们时)将被以前的值填充。我如何解决这个误导性的结果？提前致谢。

Answer 1

在VBA中最有效的error trap方法是

1)实际上是在通过定制功能或内置编码概念或二者结合来运行输入/结果之前测试输入/结果。

2)如果绝对需要，请使用VBA内置的错误处理

示例1

例如。您可以使用自定义函数包装此语句，以测试URL是否有效。

With http
    .Open "GET", link, False
    .send                    '''throws here the first error if the link is invalid
    html.body.innerHTML = .responseText
End With

If ValidURL Then 

    With http
        .Open "GET", link, False
        .send     
         html.body.innerHTML = .responseText
    End With

End If

其中ValidURL是定义为:

Function ValidURL(URL as String) as Boolean

     Dim result as Boolean
    'I don't know how you would specify a valid link in your specific case
    'but that code goes here
    'a dummy example follows
     result = Left(URL,7) = "http://"
     ValidURL = result 'True or False

End Function

示例2

我在此声明中假设:

    With http
        .Open "GET", refined_links, False
        .send                          ''throws another error here if no such link is found
    End With

当找不到这样的链接时，会生成一个特定的错误号(代码)。发现该号码并使用此代码绕过。

With http
    .Open "GET", refined_links, False
    On Error Resume Next
    .Send
    On Error GoTo 0
End With

If err.Number <> 9999 'replace with correct number

    'continue with regex test

End If

将所有内容放到一起

最后，将所有内容放在一起，您可以像这样来构建，只需最少使用 On Error Resume Next且不使用 GoTo语句。

For Each link In [{"http://spltech.in/","http://www.unifrostindia.com/","http://www.unitfrostindia.com/","http://www.greenplanet.in/"}]

    If ValidURL(link) Then 

        With http
            .Open "GET", link, False
            .send     
             html.body.innerHTML = .responseText
        End With

        For Each post In html.getElementsByTagName("a")
            If InStr(post.innerText, "certain_name") > 0 Then refined_links = post.href: Exit For
        Next post

        With http
            .Open "GET", refined_links, False
            On Error Resume Next
            .Send
            On Error GoTo 0
        End With

        If err.Number <> 9999 'replace with correct number

            With rxp
                .Pattern = "some_regex"
                .Global = True
                Set identity = .Execute(http.responseText) 
            End With

            'i will leave it to you on how to account for no pattern match
            r = r + 1: Cells(r, 1) = link
            Cells(r, 2) = identity(0)    ''''throws another error here if no such identity is noticed

        End If

    End If

Next link