vba - VBA：下标超出范围或类型不匹配

Question

对 VBA 非常陌生，我真的被卡住了。下面是我的代码，你会看到我的 Des 和 DesArr 的 For 循环接近尾声。我试图用那个循环做的就是从工作表“SIC”中拉一列单元格，这是我工作簿中的 Sheet2，我要么得到错误“下标超出范围”或“类型不匹配”，每当我尝试和谷歌/更正一个，另一个错误取而代之。如果有人可以帮助我解决这个问题，我将不胜感激！

Public Sub getGoogleDescriptions(strSearch As String)
    Dim URL As String, strResponse As String
    Dim objHTTP As Object
    Dim htmlDoc As HTMLDocument
    Dim result As String
    Dim i As Integer
    Dim u As Integer
    Dim resultArr As Variant
    Dim Des As String
    Dim DesArr(2 To 48) As Long


    Set htmlDoc = CreateObject("htmlfile")
    'Set htmlDoc = New HTMLDocument

    Dim objResults As Object
    Dim objResult As Object

    strSearch = Replace(strSearch, " ", "+")

    URL = "https://www.google.com/search?q=" & strSearch

    Set objHTTP = CreateObject("MSXML2.XMLHTTP")

    With objHTTP
        .Open "GET", URL, False
        .setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
        .send
        htmlDoc.body.innerHTML = .responseText
    End With

    Set objResults = htmlDoc.getElementsByClassName("st")

    Debug.Print objResults(0).innerText

    result = CStr(objResults(0).innerText)
    resultArr = Split(result, " ", -1, 0)
    For i = LBound(resultArr) To UBound(resultArr) 'Define i to be the length of the List'
        Debug.Print i, resultArr(i) 'Prints the corresponding index value and array element'
    Next i 'repeat

    Set htmlDoc = Nothing
    Set objResults = Nothing
    Set objHTTP = Nothing


    Set wk = ActiveWorkbook

    For u = 2 To 48
        Des = Sheets("SIC").Range("C" & u).Value
        DesArr(u) = Des
    Next u

    Debug.Print DesArr(2)

End Sub

标签： vbaexcel

Answer 1

你得到类型不匹配是因为它期望 DesArr 是一个长数据类型，它是一个介于 -2,147,483,648 到 2,147,483,647 之间的数字。

1. 在子程序中使用它时，它被用作变体。所以 2 处更正 - 将其更改为如下所示的变体

2. 然后只需将您的 2 调整为 48 到您的语句中......在这种情况下，它是 2 的简单偏移量，所以只需使用 (u - 2) 并且您的 Variant 长度为 47，从 0 而不是 1 开始。

   Public Sub getGoogleDescriptions(strSearch As String)
   
       Dim URL As String, strResponse As String
       Dim objHTTP As Object
       Dim htmlDoc As HTMLDocument
       Dim result As String
       Dim i As Integer
       Dim u As Integer
       Dim resultArr As Variant
       Dim Des As String
       Dim DesArr(0) : ReDim DesArr(46)
   
       Set htmlDoc = CreateObject("htmlfile")
       'Set htmlDoc = New HTMLDocument
   
       Dim objResults As Object
       Dim objResult As Object
   
       strSearch = Replace(strSearch, " ", "+")
   
       URL = "https://www.google.com/search?q=" & strSearch
   
       Set objHTTP = CreateObject("MSXML2.XMLHTTP")
   
       With objHTTP
           .Open "GET", URL, False
           .setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
           .send
           htmlDoc.body.innerHTML = .responseText
       End With
   
       Set objResults = htmlDoc.getElementsByClassName("st")
   
       Debug.Print objResults(0).innerText
   
       result = CStr(objResults(0).innerText)
       resultArr = Split(result, " ", -1, 0)
       For i = LBound(resultArr) To UBound(resultArr) 'Define i to be the length of the List'
           Debug.Print i, resultArr(i) 'Prints the corresponding index value and array element'
       Next i 'repeat
   
       Set htmlDoc = Nothing
       Set objResults = Nothing
       Set objHTTP = Nothing
   
   
       Set wk = ActiveWorkbook
   
       For u = 2 To 48
           Des = Sheets("SIC").Range("C" & u).Value
           DesArr(u - 2) = Des
       Next u
   
       Debug.Print DesArr(0)
   

   结束子