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

Question

我收到错误消息:

"Run-time error '9': Subscript out of range"

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 to Microsoft Excel Integration 上找到。网页。

Answer 1

无需完全运行代码，而是在 VBA 环境中进行调试。进入代码并按 F8 启动它，然后按住 F8。它将逐行运行，然后您可以查看哪一行会导致错误。

编辑:

如果您的工作簿不包含名为“事件”的工作表(或名为“API”的工作表)，
您将收到“下标超出范围错误”。创建这些表。