Unable to get data for parsed JSON object

[noppGithub]

I can not access the value of avgzone key from the sample of code below. could you please suggest?

Option Explicit

Sub SendPOSTRequest_AndPrintOut()
    Dim replyTXT As String
    Dim JSON As Object
    
    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)
    Debug.Print JSON("avgzone")
    
End Sub

[houghtonap]

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:

1. 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.
2. You need to understand how VBA-JSON maps JSON Object and Array into VBA objects.
   • VBA-JSON maps JSON Object into a Scripting.Dictionary object in VBA.
   • VBA-JSON maps JSON Array into a VBA.Collection object in VBA.
3. You need to understand how the VBA.Collection object works:
   https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/collection-object
4. You need to understand how the Scripting.Dictionary object works:
   https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/dictionary-object
5. 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.Print can only print primitive values while JavaScript's console.log can 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:

1. JSON("avgzone") is not the proper way to access a Scripting.Dictionary object's value with the key "avgzone".
2. JSON.item("avgzone") is the proper way to access the value of the key "avgzone" however, it returns another Scripting.Dictionary object which requires the use of the Set statement and further the Debug.Print statement in VBA only prints primitive values not objects like Scripting.Dictionary or VBA.Collection.
3. While your code sample is presumably paired down so readers can help you, JsonConverter will 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]

@houghtonap Sorry for did not close the issue, after reading your guide, I can access any values in the JSON object.

Thanks for making this package for VBA