excel - 下标超出范围错误，错误弹出框中不显示调试按钮

Question

我收到错误消息：

“运行时错误'9'：下标超出范围”

Excel 没有告诉我哪一行触发了这个错误。它只在错误弹出框中给我“确定”和“帮助”命令按钮。我的 Excel VBA 通常会给我 Debug 选项，但在这种情况下不会。

我找到了三个相关的主题。我了解这可能是由于阵列配置不正确造成的。这是代码：

Sub ServiceNowRestAPIQuery()

    ' Replace with your Service Now Inctance URL
    InstanceURL = "https://dev#####.service-now.com"
    ' Replace with your Authorization code
    AuthorizationCode = "Basic ########################"
    ' Add more tables as comma seperated with no spaces
    TableNames = ("incident,problem")

    Dim ws As Worksheet
    Dim objHTTP As New WinHttp.WinHttpRequest
    Dim columns As String
    Dim Header As Boolean
    Dim jsonString As String
    Dim Resp As New MSXML2.DOMDocument60
    Dim Result As IXMLDOMNode
    Dim ColumnsArray As Variant

    TablesArray = Split(TableNames, ",")

    For x = 0 To UBound(TablesArray)

        'Table Choices
        Select Case TablesArray(x)

        Case "incident"
            Set ws = Sheets("incidents")
            columns = "number,company,close_notes,impact,closed_at,assignment_group"
            ColumnsArray = Split(columns, ",")
            OtherSysParam = "&sysparm_limit=100000"
            SysQuery = "&sysparm_query=active%3Dtrue"

        Case "problem"
            'Sheet name
            Set ws = Sheets("problem")
            'Columns to Query
            columns = "number,short_description,state"
            ColumnsArray = Split(columns, ",")
            'Query filter Parameters
            OtherSysParam = "&sysparm_query=state=1"
            'Other Query Parameters
            SysQuery = ""
        End Select

        Url = InstanceURL & "/api/now/table/"
        Table = TablesArray(x) & "?"
        sysParam = "sysparm_display_value=true&sysparm_exclude_reference_link=true" & OtherSysParam & SysQuery & "&sysparm_fields=" & columns
        Url = Url & Table & sysParam
        objHTTP.Open "get", Url, False
        objHTTP.SetRequestHeader "Accept", "application/xml"
        objHTTP.SetRequestHeader "Content-Type", "application/xml"

        ' Authorization Code
        objHTTP.SetRequestHeader "Authorization", AuthorizationCode
        objHTTP.Send                             '("{" & Chr(34) & "short_description" & Chr(34) & ":" & Chr(34) & "Test API2" & Chr(34) & "}")

        Debug.Print objHTTP.Status
        Debug.Print objHTTP.ResponseText
        ws.Select
        Header = False
        i = 1
        ThisWorkbook.Sheets("API").Range("A1").Select
        Cells.Clear

        Resp.LoadXML objHTTP.ResponseText
        For Each Result In Resp.getElementsByTagName("result")
            For n = 0 To UBound(ColumnsArray)
                If Header = False Then
                    ActiveCell.Offset(0, n).Value = ColumnsArray(n)
                End If
                ActiveCell.Offset(i, n).Value = Result.SelectSingleNode(ColumnsArray(n)).Text
            Next n
            i = i + 1
            Header = True
        Next Result
        'MsgBox Time
    Next x
End Sub

此代码用于通过 REST Web 服务将 Excel 工作簿与 ServiceNow 实例集成。更多信息和代码来源可以在 ServiceNowElite 的ServiceNow 到 Microsoft Excel 集成网页上找到。

标签： excelvba

Answer 1

Instead of running the code fully, debug within the VBA environment. Go into the code and start it by pressing F8, then keeping pressing F8. It will go line by line and you can then see which line will cause the error.

EDIT:

If your workbook does not contain a sheet named "incidents" (or one named "API"), you'll get a "Subscript out of Range error." Create those sheets.