Kyle Kyle - 1 year ago 109
JSON Question

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

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
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

Answer Source

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)
End Sub