Traversing and printing complex Dictionary types (Scripting.Dictionary) - Dictionary inside of a Dictionary Based on this SO post - also reposted on vba4all.com with a few more details and explanations.

Please notice there currently is no error handling whatsoever as I didn't analyse and consider any traps yet. Although feel free to supply details/code based on your assumptions. For Example - the code fails when you try to load duplicate keys into the dictionary... this can easily be handled with the OERN approach...

So the tree would look like this for example



I have created a simple procedure TraverseDictionary() which traverses any structure of dictionaries. 

The simplest version that prints somehow a logical structure to the Immediate Window:

Sub Main()

    Dim dict As New Dictionary
    Dim subDict As New Dictionary
    Dim lvlDict As New Dictionary

    lvlDict.Add "LVL KEY", "LVL ITEM"

    subDict.Add "HELLO", ":)"
    subDict.Add "WORLD", ":("
    subDict.Add "OTHER", lvlDict

    dict.Add "FOO", "BAR"
    dict.Add "BOO", subDict

    TraverseDictionary dict

End Sub

Private Sub TraverseDictionary(d As Dictionary)

    For Each Key In d.Keys
        Debug.Print "KEY: " &amp; Key
        If VarType(d(Key)) = 9 Then
            TraverseDictionary d(Key)
        Else
            Debug.Print "ITEM: " &amp; d(Key)
        End If
    Next
End Sub


with a result:



and w/ a print out to sheet with somehow even more logical view

Private i As Long
Private depth As Long

Sub Main()
Cells.ClearContents

    Dim dict As New Dictionary
    Dim subDict As New Dictionary
    Dim lvlDict As New Dictionary

    lvlDict.Add "LVL KEY", "LVL ITEM"

    subDict.Add "HELLO", ":)"
    subDict.Add "WORLD", ":("
    subDict.Add "OTHER", lvlDict

    dict.Add "FOO", "BAR"
    dict.Add "BOO", subDict

    i = 1
    depth = 0
    TraverseDictionary dict

    Columns.AutoFit

End Sub

Private Sub TraverseDictionary(d As Dictionary)

    For Each Key In d.Keys
        Range("A" &amp; i).Offset(0, depth) = "KEY: " &amp; Key
        If VarType(d(Key)) = 9 Then
            depth = depth + 1
            TraverseDictionary d(Key)
        Else
            Range("B" &amp; i).Offset(0, depth) = "ITEM: " &amp; d(Key)
        End If
        i = i + 1
    Next
End Sub




As mr. Ioannis noticed


  There is a small risk, if an Item of a dictionary is itself an object
  but not a dictionary. Specifically, VarType(d(Key)) = 9 holds true for
  Objects of any kind (I tried it with custom classes and it returned a
  type mismatch error). I would suggest after checking for VarType, also
  checking for dictionary specifically (eg, try to access a property and
  trap the error)


Ok, so currently the code works with the assumption that the Dictionary is actually of VarType = 9 but that breaks because of Objects which also return 9.

If we check TypeName(d(key)) = "Dictionary"  then in theory it should only work with Dictionaries but can you think of any other approaches to actually check &amp; possibly validate the Dictionary. 

Side note: In the future I am most likely to create a class wrapper for this and accumulate all the checks in there along with printing at different locations and to different views.
A couple of things, which you've really more or less pointed out already. 


You've shown us two different implementations of the same routine that outputs two two different places. I think what you really need is a class that gets initialized with an IOuput member. Then you can have different implementations of the IOuput interface, but TraverseDictionary only has to deal with IOutput and one routine can print out to anywhere. 
You're correct that VarType = 9 is unsafe and TypeName = "Dictionary" is safer, but I'm not sure it's entirely safe. What if there's a namespace conflict because I implemented a VBAProject.Dictionary? TypeName of my custom dict would also be "Dictionary" and who knows what would happen then. TypeOf is another option though. I've not tested it, but I believe you can do this. 

If TypeOf d(Key) Is Scripting.Dictionary Then


But that means you'll also have to check IsObject(d(key)) prior to checking its actual type as well. 


Update:

I finally had time to test this. I created an empty Dictionary class and ran the code below. It works.

Sub test()
    Dim dict As New Scripting.Dictionary
    Dim myDict As New VBAProject.Dictionary

    Debug.Print TypeOf dict Is Scripting.Dictionary
    'true
    Debug.Print TypeOf myDict Is Scripting.Dictionary
    'false
    Debug.Print TypeOf dict Is VBAProject.Dictionary
    'false
    Debug.Print TypeOf myDict Is VBAProject.Dictionary
    'true
End Sub