Access object when property name unknown

[alketraz]

I have this response

{"errorMessages":[],"errors":{"reporter":"Reporter is required."}}

How can I read the reporter property and value without knowing the property name.

So, I need to read the errors object, which could be anything?

Something like Object.values

[houghtonap]

VBA JSON maps JSON Objects to the Scripting.Dictionary object. This means that you have a Scripting.Dictionary object for the JSON document which has two keys errorMessages and errors. The errors key has a value that is another Scripting.Dictionary which has a key reporter. That key reporter has a value that is a string.

In order to access data using VBA JSON:

1. You first need to understand the basics of VBA coding and in this case additionally the Excel VBA Object model.
2. You need to understand how VBA-JSON maps JSON objects and arrays to VBA objects.
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.

The unique part about what you are requesting is that you don't know what the key name will be in the Scripting.Dictionary object. After you read up on Microsoft's site about the Scripting.Dictionary object, you will notice that it has a keys property. You can use that property to access the name of the unknown property. You will do something like:

Option Explicit

' Dependency, requires VBA Reference to "Microsoft Scripting Runtime"
' Dependency, requires the VBA-JSON module to be imported into the VBA Project

Public Sub Issue195()

  Const mesg As String = "{""errorMessages"":[],""errors"":{""reporter"":""Reporter is required.""}}"

  Dim json As Object                ' Reference to object created by VBA-JSON.
  Dim dict As Scripting.Dictionary  ' Reference to the top level JSON object.
  Dim errs As Scripting.Dictionary  ' Reference to the errors JSON object.

  Dim indx As Long                  ' Index into error properties.

  Set json = ParseJson(mesg)

  ' 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 dict = json

    ' Next let's make sure the "errors" property exists in the message.
    If dict.Exists("errors") _
    Then

      ' In VBA we cannot make the following TypeOf dict.Item("errors") Is Scripting.Dictionary
      '  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
      '
      ' In addition, 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 TypeOf dict.Item("errors") Is Scripting.Dictionary _
      Then

        ' This is where you don't know what property name the server returned so we are going to query
        ' the "errors" JSON Object (Scripting.Dictionary created by VBA-JSON) and get the property names
        ' and values.
        Set errs = dict.Item("errors")

        ' Output goes to the immediate Window in the VBA Project
        For indx = 0 To errs.Count - 1 Step 1
          Debug.Print errs.Keys(indx) & "=" & errs.Items(indx)           ' One way to print the property name and value.
          Debug.Print errs.Keys(indx) & "=" & errs.Item(errs.Keys(indx)) ' Another way to print the property name and value.
        Next indx

      End If

    End If

  End If

  Exit Sub
End Sub

[RayCulp]

Thanks for that very informative response, @houghtonap! My initial comment was completely incorrect, so I deleted it.