Subscript out of range error where debug button is not displayed in error pop-up box

Question

I am getting the error:

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

Excel is not telling me which line is triggering this error. It only gives me the "OK" and "Help" command buttons in the error pop-up box. My Excel VBA normally gives me the Debug option, but not in this case.

I have found three related topics. I understand that this is likely due to an incorrect array configuration. Here is the code:

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

This code is for integrating an Excel workbook with a ServiceNow instance via the REST web services. More information and the source of the code can be found on ServiceNowElite's ServiceNow to Microsoft Excel Integration webpage.

So you don't have the option to `Debug` when the error occurs? That would highlight the offending line. — BigBen, Jan 29 '20 at 15:16
Strangely enough, I do not. It only gives me the "OK" and "Help" command buttons in the error pop-up box. My Excel VBA normally gives me the Debug option, but not in this case. — Joseph316, Jan 29 '20 at 15:24
I would replace the hard-coded `0`s in your loops with `LBound`: `For x = LBound(TablesArray) To UBound(TablesArray)` and `For n = LBound(ColumnsArray) To UBound(ColumnsArray)` — BigBen, Jan 29 '20 at 15:27
Side note: also see https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba — BigBen, Jan 29 '20 at 15:28
Thanks BigBen, I just did added the LBounds and I still get the subscript error. This one is stubborn. / What am I supposed to focus on in the link that you posted? — Joseph316, Jan 29 '20 at 15:29
My guess is that the offending line is `ActiveCell.Offset(i, n).Value = Result.SelectSingleNode(ColumnsArray(n)).Text`. Re that link - avoid using `Select` or `ActiveCell`. — BigBen, Jan 29 '20 at 15:30
Hmm, I'm not sure how to do that off the top of my head. This code was written by someone else who has far more understanding of both the code and how this integration works. — Joseph316, Jan 29 '20 at 15:33

score 2 · Accepted Answer · edited Jan 29 '20 at 16:49

2

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.

edited Jan 29 '20 at 16:49

BigBen

46,229
7
24
40

answered Jan 29 '20 at 15:32

Joey

59
5

Thank you!!! That lets me get much more insight into this given that the Debug button is not available in the error prompt. You're awesome. :) Okay, the last line that gets highlighted before the subscript error triggers is "Set ws = Sheets("incidents")" – Joseph316 Jan 29 '20 at 15:36
1

Do you have a Sheet called "incidents" in your workbook? If you do, try switching the code to this. I sometimes run into weird things when just Sheets is used. `Set ws = Worksheets("incidents")` – Joey Jan 29 '20 at 15:38
Thank you again Joey! Would it be too obvious if I just said "nevermind" right now? lol. Nope, no such sheet. After creating it I get a new error "Run-time error '1004': Application-defined or object-defined error" on "ThisWorkbook.Sheets("API").Range("A1").Select". Do I need to mark this question as solved and create a new one for the new error? – Joseph316 Jan 29 '20 at 15:41
2

At this point you might consider editing your answer with the answer - that if no sheet with that name exists, you'll get a subscript out of range. – BigBen Jan 29 '20 at 15:47
Thanks BigBen, is that something that I would do or Joey would do? I don't want to step on any toes and I'm not sure who your suggestion was directed to. – Joseph316 Jan 29 '20 at 15:52
BigBen. I believe that since he already checked it as solved I can't update my answer to better explain. I apologize. The edit pencil is disabled. You are right that I should've answered that better. – Joey Jan 29 '20 at 16:32
2

Edited it in for you, but you should be able to edit it as well. – BigBen Jan 29 '20 at 16:50
BigBen. Thank you. I came back to the page just now, and it now it gives me the option to edit. Odd. – Joey Jan 29 '20 at 16:51

Subscript out of range error where debug button is not displayed in error pop-up box

1 Answers1