Nested For loops with JSON-VBA

Question

So basically what I am doing here is a double search for some info about a customer. I can do it in a single search, however it will take 5 times the duration of what is supposed to happen here.

First I search by the customer phone number and see the IDs of each of his accounts that are linked to his phone number. Then I initiate another For loop to search each account through another xmlhttprequest to see if he has an active account. The totalCount is used to see how many accounts the user has (how many nested arrays are in the first for loop request). Then the process should keep looping through the accounts of each user and once it finds the first active account then the whole process should return "1" in the corresponding excel adjacent cell.

But I am receiving:

error 9, subscript out of range

.

What should I correct in the For loops architecture?

For x = 1 To NumRows
    Dim var As String: var = Cells(ActiveCell.Row, 2)
    Dim RowNote As String: RowNote = ActiveCell.Row
    Dim http As Object, html As New HTMLDocument, document As Object

    Set http = CreateObject("MSXML2.XMLHTTP")

    http.Open "GET", "http://controlpanel.zoho/rest/Accounts/criteria/idlist?AccountPhone=" & var, False
    http.send

    Dim totalCount As Integer, count As Integer
    totalCount = ParseJson(http.responseText)("totalCount")

    For count = 1 To totalCount 'totalCount can be 1 sometimes
        Dim userID As String
        userID = ParseJson(http.responseText)("results")(count)("AccountId")

        Set http = CreateObject("MSXML2.XMLHTTP")
        http.Open "GET", "http://controlpanel.zoho/rest/AccountDetails/" & userID, False
        http.send

        Dim status As String, jsonDate As Integer
        status = ParseJson(http.responseText)("results")(1)("CustomerStatus")
        If status = "LIVE_ACTIVE" Then 'can introduce Date condition to see if peolpe activated after a certain date
            Sheet3.Cells(RowNote, 3).Value = 1
            Else
            count = count + 1
        End If
    Next count
Next x

End Sub

EDIT: This is a sample of the first JSON array for the first For loop:

URL e.g. http://controlpanel.zoho/rest/Accounts/criteria/idlist?AccountPhone=67545678

and the JSON:

 {"totalCount":4,
  "messages":[],
   "results":[
        {"Type":"FX","AccountId":14237},
        {"Type":"FX","AccountId":17152},
        {"Type":"FX","AccountId":17553},
        {"Type":"FX","AccountId":17553}
    ],
   "resultClass":"com.zoho.dao.dto.zohoAccountMarketTypeDTO"}

and this is a sample of the individual second JSON in the second For loop:

URL 2 e.g. http://controlpanel.zoho/rest/AccountDetails/17152

{"totalCount":1,
 "messages":[],
  "results":[
      {"AccountAgrt":false, 
       "accountType":"FOLLOWER",
       "CustomerId":9069,
       "logins":81,
       "CustomerStatus":"LIVE_ACTIVE",
       "dateLastLogin":1510153414000,
       "state":null}
    ],
   "resultClass":"com.zoho.dao.dto.zohoAccountInfoDTO"}

What is the value of rowNote? (also, why is it declared as a String? it would make more sense for it to be a Long) — CallumDA
– CallumDA, Commented Feb 7, 2018 at 13:54
@CallumDA i have many other modules through my project working normally with rownote. This is the first time i have nested loops and i usually use rownote just to iterate through the rows of the spreadsheet. why are you implying the problem would be from rownote? — Leb_Broth
– Leb_Broth, Commented Feb 7, 2018 at 13:57
Then you need to do something like If count >= totalCount Then Exit For — Cindy Meister
– Cindy Meister, Commented Feb 7, 2018 at 16:36

Tim Williams · Accepted Answer · 2018-02-07 22:45:19Z

1

You're re-using your http variable inside the loop, so you unload the original response on the first pass through. Just parse the original response and keep that in a variable, then loop over the items, and fetch details for each one.

Untested but something like this should work:

Sub Tester()

    Dim theListing As Object, theDetails As Object
    Dim totalCount As Integer, count As Integer
    Dim userID As String
    Dim status As String, jsonDate As Integer

    For x = 1 To NumRows

        Set theListing = JSONObject("http://controlpanel.zoho/rest/Accounts/" & _
                                    "criteria/idlist?AccountPhone=" & var)

        totalCount = theListing("totalCount")

        For count = 1 To totalCount 'totalCount can be 1 sometimes

            userID = theListing("results")(count)("AccountId")


            Set theDetails = JSONObject("http://controlpanel.zoho/rest/" & _
                                        "AccountDetails/" & userID)

            status = theDetails("results")(1)("CustomerStatus")
            If status = "LIVE_ACTIVE" Then 'can introduce Date condition to see if peolpe activated after a certain date
                Sheet3.Cells(RowNote, 3).Value = 1
                Exit For
            End If
        Next count
    Next x

End Sub

'fetch a response as parsed JSON object
Function JSONObject(url As String)
    Dim http As Object
    Set http = CreateObject("MSXML2.XMLHTTP")
    http.Open "GET", url, False
    http.send
    Set JSONObject = parsejson(http.responseText)
End Function

edited Feb 7, 2018 at 22:45

answered Feb 7, 2018 at 18:28

Tim Williams

169k8 gold badges104 silver badges144 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Leb_Broth Over a year ago

Thank you Tim for your answer, regarding count = count + 1 it is still inside the for loop although you notified me I shouldn’t modify it. Or am I misunderstanding something.

Tim Williams Over a year ago

I did not try to figure out why that was there; just pointing out it's typically not something you'd do with a For loop variable. If it works then fine, but it's not a good practise...

Leb_Broth Over a year ago

Tim would I have an iteration problem if sometimes totalcount happens to be 1? Also, once the condition is true, shouldn’t I add: exit for , in order to go and look for other users in the first for loop?

Tim Williams Over a year ago

Yes sounds like Exit For is what you need there

Collectives™ on Stack Overflow

Nested For loops with JSON-VBA

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related