如何优化Excel VBA点击url

VBA 运行时出现运行时错误“70”。

有时代码运行顺利，但有时则不然。想知道是否有更可靠的代码可以继续。它总是停在If link.innerHTML = "Balance Sheet" Then end if

Public Sub Get()

Dim ie As Object
Dim URL As String, link As Object, alllinks As Object
Dim eRowa As Long, eRowb As Long, eRowc As Long
Dim var As Object

Set var = ThisWorkbook.Worksheets("Sheet2").Cells(1, 1)
URL = "https://www.marketwatch.com/investing/stock/" & var & "/financials"

Set ie = CreateObject("internetexplorer.application")

With ie

    .Visible = True
    .navigate URL

    While .Busy Or .readyState < 4: DoEvents: Wend

    Set alllinks = ie.document.getElementsByTagName("a")

    For Each link In alllinks

         If link.innerHTML = "Balance Sheet" Then

             link.Click

         End If

    Next link

    While .Busy Or .readyState < 4: DoEvents: Wend

End With

Set ie = Nothing


End Sub

预计运行顺利，无错误70

使用定时循环来等待出现a标签。使用属性 = 值 css 选择器 https://developer.mozilla.org/en-US/docs/Web/CSS/Attribute_selectorswith $ 以运算符结尾，以便更快地定位适当的元素

Option Explicit

Public Sub GetInfo()
    Dim ie As Object, url As String, link As Object
    Dim var As Range, t As Date
    Const MAX_WAIT_SEC As Long = 10

    Set var = ThisWorkbook.Worksheets("Sheet2").Cells(1, 1)

    url = "https://www.marketwatch.com/investing/stock/" & var.value & "/financials"

    Set ie = CreateObject("InternetExplorer.Application")

    With ie
        .Visible = True
        .navigate2 url

        While .Busy Or .readyState < 4: DoEvents: Wend

        t = Timer
        Do
            On Error Resume Next
            Set link = .document.querySelector("[href$='/balance-sheet']")
            On Error GoTo 0
            If Timer - t > MAX_WAIT_SEC Then Exit Do
        Loop While link Is Nothing

        If link Is Nothing Then Exit Sub

        link.Click

        While .Busy Or .readyState < 4: DoEvents: Wend

        Stop '<== Delete me later
        .Quit
    End With
End Sub