vba ---Iterating over MS Access Dictionary

[jdraw]

Note: This has also been posted at
http://www.accessforums.net/showthread.php?t=63480

I have searched but can not find a clear example for iterating over a dictionary object. The dictionary contains different object types.

There are 3 item(s) in the dictionary/object
DictItems(0) is a Collection called <items> with 3 element(s)
DictItems(1) is a Dictionary called <obj> with 2 element(s)
DictItems(2) is a String called <message>

My goal is to iterate over the dictionary and report/process each of the elements in each of the Items.
I'd like the solution to be sufficiently generic in that keys and values can be determined via the vba code.

I am attaching a jpg showing the watches related to the dictionary object I am testing with.

If you have a sample showing a vba procedure for iterating over a dictionary, or a link to same or a tutorial, or have some ideas on approach, I would appreciate any suggestions.

Thanks in advance.

 

[Uncle Gizmo]

I found this:-

Using the Dictionary Class in VBA
https://www.experts-exchange.com/articles/3391/Using-the-Dictionary-Class-in-VBA.html


I note:-
In a Dictionary, an item can only be returned in reference to its key.

Also you can enumerate the items in a Dictionary:

For Each x In MyDictionary
    MsgBox MyDictionary.Item(x)
Next

 

[jdraw]

Thanks Tony,
I saw that reference and went through it several times. I can not get it to display any data. My Dictionary is called Dict, and I had a variable x, so used xA here.

  'from Gizmo
        Dim xA As Variant
300     For Each xA In dict
310         MsgBox dict.Item(xA)
320     Next

This gives an error 450 on line 310

Most samples I found deal with simple strings.

The material I am placing in this dictionary are: (different object types)
- a 3 element collection
- a 2 element dictionary and a string.

The Collection and Dictionary have multiple elements, so the sample code doesn't address that.

I haven't used Dictionary before, so it may be something simple (hopefully).
I am trying to parse some JSON and make sense of the returned data.

Thanks.

 

[MarkK]

If this is a Scripting.Dictionary then there are a couple of functions that returns arrays, namely Items and Keys, so you could enumerate Items, and check the type of each object, like...

dim var
for each var in dict.items
   debug.print typename(var)
next

...so knowing that, you could act selectively on the type of object you found, like...

dim var
for each var in dict.items
   select case typename(var)
      case "Collection"
         dim c as vba.collection
         set c = var
         debug.print "collection: " & c.count & " elements"
      case "Dictionary"
         dim d as scripting.dictionary
         set d = var
         debug.print "dictionary: " & ubound(d.keys) + 1 & " entries"
      case else
         debug.print "object is: " & typename(var)
   end select
next

...but if you've put stuff into the dictionary by key, then you might want to enumerate the keys collection, which are all strings, and then get at the dictionary's contents by key, like....

dim var
for each var in dict.keys
   debug.print "Key: " & var, "Type: " & typename(dict.item(var))
next

I think.

 

[jdraw]

Thanks Markk,

I think your approach is where I should head next. I have tried to get the contents of the dictionary items
but did see reference to setting a variant to the Items and Keys and working with those. I'm finding it (dictionary and iterating) confusing because I can see the material in the watch window, but haven't
grasped some "concept or other" that lets me code to get it.

I have done some coding to look at the returned string and break it into pieces with some verbiage.

.....
 For r = 0 To dict.Count - 1
230         If (TypeName(dict.Items(r)) = "String") Then
240             Debug.Print "Dict.Items(" & r & ") is a " & TypeName(dict.Items(r)) & " <" & dict.keys(r) & ">"
250             Debug.Print "These are the contents of the dictionary " & vbCrLf & Join(dict.keys, vbCrLf)
260         Else
270             Debug.Print "Dict.Items(" & r & ") is a " & TypeName(dict.Items(r)) & " <" & dict.keys(r) & ">" _
                            & " with " & dict.Items(r).Count & " element(s)"
280         End If
290     Next r.....................

example:
We are dealing with a JSON object
There are 3 item(s) in the dictionary/object
Dict.Items(0) is a Collection <items> with 3 element(s)
Dict.Items(1) is a Dictionary <obj> with 2 element(s)
Dict.Items(2) is a String <message>
These are the contents of the dictionary
items
obj
message

Thanks for the info.

Update:
Markk-
I just added your sample code and it produced:

collection: 3 elements
dictionary: 2 entries
object is: String

Key: items Type: Collection
Key: obj Type: Dictionary
Key: message Type: String

Much cleaner than what I had. I will continue tomorrow.

 

[Uncle Gizmo]

I don't know if this is relevant,

http://www.fmsinc.com/microsoftacce...BA_VB6/AbstractDataTypes/Dictionary_class.htm

but when I saw it, I thought of this post...

 

[jdraw]

Thanks Tony. I have bookmarked the page. I was trying to help someone with an Access/JSON issue, but that was a few months ago.
Parsing JSON with an Access class module would make a good youtube or series (but may have limited audience).

 

[Uncle Gizmo]

Not sure this is relevant, but it seems like the same tree to me, or at least a similar one. In other words I don't think I'm barking up the wrong tree, but I'm most probably wrong!

If you go down this page and find item "4" (four) I'm not sure how long it will remain item 4 so it's headed thus:-

With a little work, you can iterate over custom collections like this:

Now that's a collection, and I know your question was about dictionaries...

There's also some very interesting comments on that page, well worth a read.

 

[jdraw]

Thanks Tony. The comments are good. I'm following Colin of the JSON parser stuff. He's into it in more depth than anything I have found via Google/Bing etc.