类型不匹配错误(数组):在VBA中解析字符串的JSON数组 [英] 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

该代码试图 从中解析JSON(请参见下面的价格"):

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.

我的完整代码:

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

推荐答案

您正在尝试接收 Dictionary 数据结构而不是Array的UBound(). UBound()仅在阵列上起作用.

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

相反，您似乎想要遍历Dictionary的键.这是一个小示例，该怎么做.

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

这篇关于类型不匹配错误(数组):在VBA中解析字符串的JSON数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！