Type Mismatch Error (Array): parsing JSON array of strings in VBA

Question

I keep receiving a "type mismatch error" (indicating that its not an array?) at:

Sub FillTaxiInfo

For i = 0 To UBound(data("prices")) - 1

The code is attempting to parse JSON from (see "prices" below):

{"id":1,"prices":[{"name":"expressTaxi","fare":{"fareType":"standard", "base":"$2.50"...}}

When I place a breakpoint and inspect "prices", it tells me that the 'Value' is Expression not defined in context and 'Type' is Empty.

Any other suggestions for improvement would be much appreciated.

My full code:

Option Explicit

Sub Run()

Dim myUrls As Variant
myUrls = Array("URL1, URL2, URL3")
FillMultipleCityInfo myUrls, ActiveWorkbook

End Sub

Function GetJson(ByVal url As String) As Dictionary
With New WinHttpRequest
    .Open "GET", url
    .Send
    Set GetJson = JsonConverter.ParseJson(.ResponseText)
End With
End Function

Sub FillTaxiInfo(data As Dictionary, sheet As Worksheet)
Dim i As Integer, taxi As Dictionary
For i = 0 To UBound(data("prices")) - 1
    Set taxi = data("prices")(i)
    If taxi.Exists("name") Then
      sheet.Cells(i, 1) = taxi("name")
      sheet.Cells(i, 2) = taxi("fare")("fareType")
    End If
Next i
End Sub

Sub FillMultipleCityInfo(urls As Variant, book As Workbook)
Dim i As Integer, data As Dictionary, sheet As Worksheet

For i = 0 To UBound(urls) - 1
    Set data = GetJson(urls(i))
    Set sheet = book.Sheets(i + 1)
    FillTaxiInfo data, sheet
Next i
End Sub

Ryan Wildry · Accepted Answer · 2016-09-01 12:40:28Z

1

You are trying to receive the UBound() of an Dictionary data structure and not an Array. UBound() will only function on an Array.

Instead it appears you want to iterate over the keys of a Dictionary. Here is a small example how to do this.

Public Sub Dict_Iter()
    Dim key As Variant 'Even though the key is a string --
                       'Objects/Variant are needed in a For Each Loop
    Dim dict As New Dictionary

    'Add several items to the dictionary
    With dict
        .Add "a", "a"
        .Add "b", "b"
        .Add "c", "c"
    End With

    'Iterate over the keys
    For Each key In dict.Keys()
       Debug.Print dict(key)
    Next
End Sub

answered Sep 1, 2016 at 12:40

Ryan Wildry

5,6971 gold badge17 silver badges36 bronze badges

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

3 Comments

Kyle Over a year ago

The "a", "a" confuses me -- as it almost looks like a key-value pair. Using a concrete example, I would use: .Add "prices", "names" both of which are keys, with "names" at a lower level/index than "prices"? Further, on allocating these values to cells as I was attempting to do above, I would simply structure the bottom iteration as: sheet.Cells(i, 1) = Debug.Print dict(key) after initializing i and layering in i+1?

Ryan Wildry Over a year ago

"a", "a" is in fact a key value pair, as that's required for Dictionary data type. The key is whatever is going to be a unique element, while the value is the value returned when a matching key is found. Again, not sure what your specific program needs to do, but I think I answered the question posed "type mismatch error" (indicating that its not an array?)

Kyle Over a year ago

Got it, I'lll check answered but unfortunately a static value solution won't work for me. I'm scraping JSON from the web, which has static keys but values are variable/unknown.

Collectives™ on Stack Overflow

Type Mismatch Error (Array): parsing JSON array of strings in VBA

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related