Loop through collection / Dictionary in VBA

[Shloime]

Please can anyone help me with how to loop through a dictionary in VBA some items/keys may have a single value and some have a dictionary/collection object and inside may be another collection and inside that may be more collections/dictionary objects.
when I tried to do

 set G= ....
for each t in g
debug.print t
next

I found that some have no result som how can i refer to it g(t) won't work as t is empty
I copied some code from the Internet Main 1 now is parse JSON how can I know once have an object set if it holds a collection or dictionary may be somehow in the watch window?

 

[DanteAmor]

Please can anyone help me with how to loop through a dictionary in VBA

Check the following example. We add 3 keys to the dictionary, the colors are keys and the numbers are items:

Sub loop_dictionary()
  Dim dic As Object
 
  Set dic = CreateObject("Scripting.Dictionary")
 
  dic("Green") = 1
  dic("Red") = 2
  dic("White") = 3
 
End Sub

---------------------------------
To loop by keys:

Sub loop_dictionary()
  Dim dic As Object
  Dim ky As Variant
 
  Set dic = CreateObject("Scripting.Dictionary")
 
  dic("Green") = 1
  dic("Red") = 2
  dic("White") = 3
 
  For Each ky In dic.keys
    Debug.Print ky
  Next
 
End Sub

--------------------------
To loop by items:

Sub loop_dictionary()
  Dim dic As Object
  Dim itm As Variant
 
  Set dic = CreateObject("Scripting.Dictionary")
 
  dic("Green") = 1
  dic("Red") = 2
  dic("White") = 3
 
  For Each itm In dic.items
    Debug.Print itm
  Next
End Sub

----------------------------------------
To loop by keys to get items:

Sub loop_dictionary_2()
  Dim dic As Object
  Dim ky As Variant
 
  Set dic = CreateObject("Scripting.Dictionary")
 
  dic("Green") = 1
  dic("Red") = 2
  dic("White") = 3
 
  For Each ky In dic.keys
    Debug.Print dic(ky)
  Next
End Sub

-----------------------------
They are very basic examples, they do not contain collections or dictionaries within the dictionary, but it is an advance for you to see how to loop a dictionary.

 

[Fuji]

Are you really using dictionary/collection in such complex way?

    Dim dic As Object, e
    Set dic = CreateObject("Scripting.Dictionary")
    Set dic(1) = CreateObject("Scripting.Dictionary")
    dic(2) = Array(1, CreateObject("Scripting.Dictionary"))
    Set dic(3) = New Collection
    dic(4) = 0.01
    Set dic(5) = [a1:c5]
    For Each e In dic
        Select Case TypeName(dic(e))
            Case "Dictionary", "Collection"
                MsgBox "Dictionary or Collection"
            Case "Variant()"
                MsgBox "Array"
            Case "Range"
                MsgBox "Range"
            Case "String", "Integer", "Long", "Double", "Date"
                MsgBox "Value"
        End Select
    Next

 

[Shloime]

Are you really using dictionary/collection in such complex way?

    Dim dic As Object, e
    Set dic = CreateObject("Scripting.Dictionary")
    Set dic(1) = CreateObject("Scripting.Dictionary")
    dic(2) = Array(1, CreateObject("Scripting.Dictionary"))
    Set dic(3) = New Collection
    dic(4) = 0.01
    Set dic(5) = [a1:c5]
    For Each e In dic
        Select Case TypeName(dic(e))
            Case "Dictionary", "Collection"
                MsgBox "Dictionary or Collection"
            Case "Variant()"
                MsgBox "Array"
            Case "Range"
                MsgBox "Range"
            Case "String", "Integer", "Long", "Double", "Date"
                MsgBox "Value"
        End Select
    Next

I used the following to convert json (from web query)

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

JSON conversion and parsing for VBA. Contribute to VBA-tools/VBA-JSON development by creating an account on GitHub.

github.com

and this converts to a dictionary.

 

[Shloime]

I used the following to convert json (from web query)

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

JSON conversion and parsing for VBA. Contribute to VBA-tools/VBA-JSON development by creating an account on GitHub.

github.com

and this converts to a dictionary.

It looks to me that sometimes

    For Each e In dic

e has no value (a collection without a name) so dic(e) returns an error is there a way to select an item of a dictionary by it's index number?

 

[Fuji]

Do you mean like dic.items()(0)?

 

[Shloime]

Do you mean like dic.items()(0)?

I'm not sure what you asked for an array I can have array(1) or array(1,1) in a range i can select by a number,
in my case I found the e is a collection and by itself so sometimes i'd need to use for each f in dic(e) but here the e doesn't hold a string this worked for each f in e

 

[Fuji]

That was a response to "is there a way to select an item of a dictionary by it's index number?"
I am not familiar with Jason, so I can only suggest you such way...

 

[Shloime]

That was a response to "is there a way to select an item of a dictionary by it's index number?"
I am not familiar with Jason, so I can only suggest you such way...

should dic.items()(0) work?

 

[DanteAmor]

I missed index number:

Sub loop_dictionary_index()
  Dim dic As Object
  Dim i As Long
  
  Set dic = CreateObject("Scripting.Dictionary")
  
  dic("Green") = 1
  dic("Red") = 2
  dic("White") = 3
  
  For i = 0 To dic.Count - 1
    Debug.Print dic.Items()(i)
    Debug.Print dic.Keys()(i)
  Next
End Sub