0

I am trying to select two values from a JSON response in VBA, using the JsonConverter.bas file from GitHub. I am not sure I am calling the fields correctly. When I run the following code I get the "Run-time error '13': Type mismatch" error message. How would I be able to select the max_price value and the min_price values from the following: 

Public Sub exceljson()

Dim http As Object, JSON As Object, i As Integer

Set http = CreateObject("MSXML2.XMLHTTP")

http.Open "GET", "https://testapp.deribit.com/api/v2/public/ticker?instrument_name=BTC-27DEC19-8000-C", False

http.Send

Set JSON = ParseJson(http.ResponseText)

i = 2
For Each Item In JSON
Sheets(1).Cells(i, 1).Value = Item("result")("max_price")
Sheets(1).Cells(i, 2).Value = Item("result")("min_price")
i = i + 1

Next

MsgBox ("complete")

End Sub
user1781336
  • 85
  • 4
  • 12
  • 1
    This `JSON` object is a `Dictionary`. So the default return value will be a `Key`, not a contained dictionary object. – Ron Rosenfeld Dec 11 '19 at 17:57

1 Answers1

1

There is no need to loop through the JSON element. You can access directly those values with:

Sheets(1).Cells(1, 1).Value = JSON("result")("max_price")
Sheets(1).Cells(1, 2).Value = JSON("result")("min_price")
drec4s
  • 7,946
  • 8
  • 33
  • 54
  • 1
    I don't think that's quite right... How are you selecting the items? There must be 10, 100 or even 100000 items with min price and max price... You need to loop through them to get them all. – Damian Dec 11 '19 at 17:27
  • 2
    No you don't. The API response from the OP question has only one `result` dictionary with a single max_price and min_price key. – drec4s Dec 11 '19 at 17:32
  • I work with that same api, and it doesn't. You get a collection of items which you need to loop through... OP's sintax is good, but the local window will tell which items are inside which. – Damian Dec 11 '19 at 17:45
  • 1
    @Damian - We can only work with what the OP provides - their specific URL returns only a single result. This answer addresses the specific question asked. – Tim Williams Dec 11 '19 at 18:22