houghtonap
commented
3 years ago The reason you cannot get the data for the parsed JSON object is because you don't understand how VBA-JSON maps a JSON Object to an object in the VBA realm. There are multiple issues with your sample code. You need to first understand the following:
- You need to understand the basics of VBA coding and the VBA Object model of the application you are using, e.g., Excel, Access, Word, etc.
- You need to understand how VBA-JSON maps JSON
ObjectandArrayinto VBA objects.- VBA-JSON maps JSON
Objectinto aScripting.Dictionaryobject in VBA. - VBA-JSON maps JSON
Arrayinto aVBA.Collectionobject in VBA.
- VBA-JSON maps JSON
- You need to understand how the
VBA.Collectionobject works: https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/collection-object - You need to understand how the
Scripting.Dictionaryobject works: https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/dictionary-object - You need to understand that accessing objects in VBA is distinctly different from accessing objects in JavaScript.
- VBA does not have short circuit evaluation of conditionals while JavaScript does.
- VBA's
Debug.Printcan only print primitive values while JavaScript'sconsole.logcan print objects and arrays. - VBA's
( )indices used with an object do not function the same as JavaScript's[ ]indices used with an object.
Your replyTxt is a JSON Object, therefore VBA-JSON will map that to a Scripting.Dictionary object in VBA. You need to consult the Microsoft Documentation for the methods and properties on that object to see how to use it within the VBA context. The Scripting.Dictionary object does not have a default accessor like some VBA objects do. The way you need to access the "value" of a "key" in the dictionary in to use the item accessor, but you also need to consider what the item accessor will be returning, i.e., another Scripting.Dictionary object. For example your code: Debug.Print JSON("avgzone") has multiple issues:
JSON("avgzone")is not the proper way to access aScripting.Dictionaryobject's value with the key"avgzone".JSON.item("avgzone")is the proper way to access the value of the key"avgzone"however, it returns anotherScripting.Dictionaryobject which requires the use of theSetstatement and further theDebug.Printstatement in VBA only prints primitive values not objects likeScripting.DictionaryorVBA.Collection.- While your code sample is presumably paired down so readers can help you,
JsonConverterwill return different VBA objects based on the JSON passed to it. So when the API you are using returns a particular JSON for success and different JSON for an error you need to provide the appropriate type checking in your code, otherwise it fail.
A more robust coding of your sample code would be:
Sub SendPOSTRequest_AndPrintOut()
Dim replyTXT As String
Dim JSON As Object ' Depending upon what the API returns you might need to use Variant instead.
Dim API as Scripting.Dictionary
Dim avgzone as Scripting.Dictionary
replyTXT = "{""avgzone"": {""green_end"": 1491.5602768288572, ""green_start"": 1744.3302566228513, ""red_end"": 199.04817530731853, ""red_start"": 304.2799829937485, ""yellow_end"": 304.2799829937485, ""yellow_start"": 1491.5602768288572}, ""skuzone"": [{""index"": 0, ""matid"": ""844595465000"", ""zone"": {""green_end"": 1511.2477987044917, ""green_start"": 1761.4327906976744, ""red_end"": 199.04817530731856, ""red_start"": 304.27998299374843, ""yellow_end"": 304.27998299374843, ""yellow_start"": 1511.2477987044917}}, {""index"": 1, ""matid"": ""849910852500"", ""zone"": {""green_end"": 526.8717049227604, ""green_start"": 906.3060869565215, ""red_end"": 199.04817530731856, ""red_start"": 304.27998299374843, ""yellow_end"": 304.27998299374843, ""yellow_start"": 526.8717049227604}}]}"
Set JSON = JsonConverter.ParseJson(replyTXT)
' First let's make sure that the Json parsed was a Json Object.
' Note, depending on the API being used checking for a successful message could be much more
' complicated since the API could return a JSON Object for success and a JSON Array, Object,
' String, Boolean, Number, Null for an error.
'
If TypeOf JSON Is Scripting.Dictionary _
Then
' Reference for the top level JSON Object of the message.
' Strictly, you don't need to do this, but it does gives you intellisense.
'
Set API = JSON
' When using the Scripting.Dictionary object you must test for the presence of the key
' **before** using the key, otherwise the Scripting.Dictionary object will **add** the key if it
' is not in the dictionary.
' See Remarks section: https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/item-property-dictionary-object
'
If API.Exists("avgzone") _
Then
' In VBA we cannot make the following conditional part of the enclosing If statement's
' conditional because VBA does not perform short circuit evaluation of conditionals.
' See Wikipedia article: https://en.wikipedia.org/wiki/Short-circuit_evaluation
'
If TypeOf API.item("avgzone") Is Scripting.Dictionary _
Then
Set avgzone = API.item("avgzone")
' The rest of your code to do whatever you need to do.
Else
Debug.Print "API returned a JSON Object with an avgzone key whose value was not a JSON Object"
End If
Else
Debug.Print "API returned a JSON Object without an avgzone key"
End If
Else
Debug.Print "API did not return a JSON Object"
End If
Exit Sub
End Sub
noppGithub
I can not access the value of
avgzonekey from the sample of code below. could you please suggest?