Excel VBA JSON - object access issue

[Nasti, Paul]

Hi all,

I'm working with Excel to access the xml data, in my test case, access a specific ticket.

I'm using the handy VBA function created by Tim Hall. This seems to be widely used.

I successfully extract/parse the ticket info, however, I'm having issues with ways of accessing the data within the VBA object I set.

See small snippet of code I'm struggling with.

'declarations

Dim jsonObject As Object, item As Variant

Set jsonObject = ParseJson(sResponse) 'successfully creates object with xml data

For Each item In jsonObject("fields")

       'this debug print doesn't work
       debug.print item("customfield_11721")
Next

'the debug print works fine.
'Debug.Print jsonObject("fields")("customfield_11721")

Looking at the 'Locals' window shows no additional array values of 'item' so I see why it's not working in my case.  There are no values other the value of 'customefield...' etc. 

I can query the object using the last debug print statement so the data is there and correctly stored.

I've seen examples of code that shows this method works within the for each statement, using the same Tim Hall code. I was wondering if this is the case and I'm missing the proper syntax?

Thanks

[Bill Wallis]

Not too sure what you're trying to do within your For Each block, but that is indeed a syntax error -- it's worth noting that the jsonObject you get back from ParseJson is a Scripting.Dictionary object (hence why you need the Microsoft Scripting Runtime library enabled)

I'd recommend checking out how to use the Scripting.Dictionary object first as this will help you work with your jsonObject variable (e.g. VBA for smarties: Dictionaries (snb-vba.eu))

It's worth noting that a dict (short for dictionary) is made up of keys and their values, and you access the value of a key by putting the key inside the brackets after your dict variable

Also, when you do a For Each on a dict the variable you loop over (the item variable in your code) is just a string which is why it needs to be added to the end of your dict to get its value:

For Each item In jsonObject("fields")
    Debug.Print VarType(item) ' 8 (String)
    Debug.Print item          ' The key name
Next

See: VarType function (Visual Basic for Applications) | Microsoft Docs

Additionally, I'll also note that using the brackets after a dict is "the same as" using its Item method, which is the method to get the value for a specific key:

Debug.Print jsonObject("Key") = jsonObject.Item("Key")  ' True

---

For your specific example, you have the jsonObject variable which is now a Scripting.Dictionary object. This variable will have a few different keys and values in it, and you can get them by doing something like the following:

Public Sub ShowKeysAndValues()
    Dim jsonObject As Scripting.Dictionary
    Dim vKey As Variant

    Set jsonObject = ParseJson(sResponse)  ' This sResponse is your sResponse

    On Error Resume Next
        For Each vKey In jsonObject
            Debug.Print vKey, jsonObject(vKey)
        Next vKey
    On Error GoTo 0
End Sub

Note that I've renamed your item variable as vKey to illustrate that you get the list of keys back when you do the For Each block and not the values. To get the value corresponding to a specific key, you put the key inside the brackets after your dict variable (jsonObject(vKey))

This will produce an output something like:

expand   renderedFields,names,schema,...
id       99999
self     https://domain.atlassian.net/rest/api/3/issue/99999
key      ABC-123
fields   

In this output, the keys are on the left-hand-side and their corresponding values are on the right-hand-side. Note that fields doesn't have a value here because its 'value' is another dict so you can't Debug.Print it by default (and is also the reason for the On Error lines which should be handled more gracefully)

When you write jsonObject("fields"), you get back a dict so you can also loop through these to see what keys and values are on this new dict using something like:

Public Sub ShowKeysAndValuesOfFields()
    Dim jsonObject As Scripting.Dictionary
    Dim vKey As Variant

    Set jsonObject = ParseJson(sResponse) ' This sResponse is your sResponse

    On Error Resume Next
        For Each vKey In jsonObject("fields")
            Debug.Print vKey, jsonObject("fields")(vKey)
        Next vKey
    On Error GoTo 0
End Sub

The output for this is much larger since there are typically a lot of fields on an issue so I won't put it here, but you should be able to find the line that corresponds to your customfield_11721 key; alternatively, you can write exactly what you've written to get the value for this key:

jsonObject("fields")("customfield_11721")

Note that this is identical to:

jsonObject.Item("fields").Item("customfield_11721")

---

Link to Tim Hall's JSON parser repo:

• GitHub - VBA-tools/VBA-JSON: JSON conversion and parsing for VBA