ExcelVBA如何从json对列表中查找最小值？-ExcelVBAHowtofindaminimumvaluefromalistofjsonpairs?

0  

Please see the explanation in the comments

请参阅评论中的说明

Set Json = JsonConverter.ParseJson(result)
Set shipping = Json("shipping") 
methodcount = shipping.Count
targetCell.Value=getMinPrice(shipping) 'Display the min Price in the cell you want.

And here's the function to get minimum price

这是获得最低价格的功能

Public Function getMinPrice(shipping as Variant)As Double 'Variant is nothing but a convenient VBA way to specify that the shipping argument could be of any type. 
    getMinPrice=10000000.0        ' Set initial minPrice
                                  'Iterate through each record in shipping list. For each loop rec is assigned the new set of price list record. The loop is run till all records are exhausted.
    For Each rec In shipping      'rec is nothing but a variable like i=0, So you can replace rec here and in the following statements with whatever you like
                                  'You could avoid using 'Val' function here, I put it as a precautionary measure
        If getMinPrice > Val(rec("price")) Then 'Check if new price is less the minimum we already have
            getMinPrice = Val(rec("price"))     'Set the new minimum Price.
        End If
    Next
End Function

0  

This is not solution base on json api. Just refer to it(If your data is all as above content).

这不是基于json api的解决方案。只需参考它（如果您的数据都是上述内容）。

Sub test()
    Dim s As String
    s = Range("a1") '<~~  if your json text in range("a1") else enter json instead
    's = json.text
    JsonToArray Range("b1"), s, "price"
    JsonToArray Range("c1"), s, "name"
    JsonToArray Range("d1"), s, "delivery"
    JsonToArrayMin Range("a4"), s, "price"
End Sub
Sub test2()
    Dim s As String
    s = Range("a1") '<~~  if your json text in range("a1") else enter json instead
    JsonToArrayMin Range("a4"), s, "price"
End Sub
Sub JsonToArray(rng As Range, Json As String, Item As String)
    Dim vR() As Variant, vSplit, v
    Dim n As Long, i As Long
    Item = Item & Chr(34) & ":"
    vSplit = Split(Json, Item)
    For i = 1 To UBound(vSplit)
        v = vSplit(i)
        n = n + 1
        ReDim Preserve vR(1 To n)
        vR(n) = Split(v, ",")(0)
        vR(n) = Replace(vR(n), Chr(34), "")
        vR(n) = Replace(vR(n), ":", "")
        vR(n) = Replace(vR(n), "}", "")
        vR(n) = Replace(vR(n), "]", "")

    Next i
    If n > 0 Then
        rng.Resize(n) = WorksheetFunction.Transpose(vR)
    End If
End Sub

Sub JsonToArrayMin(rng As Range, Json As String, Item As String)
    Dim vR() As Variant, vSplit, v
    Dim n As Long, i As Long
    Item = Item & Chr(34) & ":"
    vSplit = Split(Json, Item)
    For i = 1 To UBound(vSplit)
        v = vSplit(i)
        n = n + 1
        ReDim Preserve vR(1 To n)
        vR(n) = Split(v, ",")(0)
        vR(n) = Replace(vR(n), Chr(34), "")
        vR(n) = Replace(vR(n), ":", "")
        vR(n) = Replace(vR(n), "}", "")
        vR(n) = Replace(vR(n), "]", "")
        vR(n) = Val(vR(n))
    Next i
    If n > 0 Then
        rng = WorksheetFunction.Min(vR)
    End If
End Sub