Working with dictionaries in VBA is a bit more unpleasant, than working with dictionaries with almost any other language. Because of the VBA IDE, you only see the keys, but not the values. Thus, if you have experience with Visual Studio (or any other nice IDE), you will be happy never to return back to the ugly Excel macro language with its IDE that looks like being built in 1997. Anyway, enough hating over the old IDE, and let’s see what we can do about it?

Print a dictionary of strings

First thing you can do is probably to see the value of the dictionary, in the immediate window like this – ?dictionary(“key”) . The second thing is to print the whole dictionary in the immediate window. This is what PrintDictionary will do, printing a simple dictionary:

Sub Main()
    
    Dim dict1 As New dictionary
    dict1.Add "Bulgaria", "Sofia"
    dict1.Add "Germany", "Berlin"
    dict1.Add "Russia", "Moscow"
    
    PrintDictionary dict1
    
End Sub

Public Sub PrintDictionary(myDict As dictionary, Optional isCollection = False)
    
    Dim myKey As Variant
    
    For Each myKey In myDict.Keys
        Debug.Print myKey
        If isCollection Then
            Dim myElement As Variant
            For Each myElement In myDict(myKey)
                Debug.Print myElement
            Next
            Debug.Print "----------------"
        Else
            Debug.Print vbTab & myDict(myKey)
        End If
    Next
    
End Sub

Print a dictionary of collections

What if we had a list in our dictionary? E.g., instead of “Sofia”, we have added “Plovdiv” and “Pleven” to “Bulgaria”? Let’s see – then changing the optional parameter isCollection to True will be do quite a nice job:

Sub Main()
    
    Dim dict1 As New dictionary
    dict1.Add "Bulgaria", New Collection
    dict1.Add "Germany", New Collection
    dict1.Add "Russia", New Collection
    
    dict1("Bulgaria").Add "Sofia"
    dict1("Bulgaria").Add "Pleven"
    dict1("Bulgaria").Add "Plovdiv"
    dict1("Russia").Add "Moscow"
    dict1("Russia").Add "Kazan"
    
    PrintDictionary dict1, True
    
End Sub

Print a dictionary of dictionary of dictionary …

Now, let’s go even further. Making a nested dictionary will require some more lines. Printing it as well. As far as I do not want to involve recursion into printing it (although this is probably the best way), I will put 2 flags for the levels of nesting. So, let’s imagine that we have Bulgarian cities, with Bulgarian neighbourhoods in them with places in each neighbourhood, saying something interesting about a place. We can put 3 nested dictionary to display this:

Public Sub PrintNestedDictionary(myDict As Dictionary, Optional isNested1 = False, Optional isNested2 = False)
    
    Dim myKey As Variant
    
    For Each myKey In myDict.Keys
        Debug.Print myKey
        If isNested1 Then
            Dim myElement As Variant
            For Each myElement In myDict(myKey).Keys
                Debug.Print vbTab & myElement
                If isNested2 Then
                    Dim myElement2 As Variant
                    For Each myElement2 In myDict(myKey)(myElement).Keys
                        Debug.Print vbTab & vbTab & myElement2
                        Debug.Print vbTab & vbTab & vbTab & myDict(myKey)(myElement)(myElement2)
                    Next
                End If
                Debug.Print "----------"
            Next
            Debug.Print "----------"
        Else
            Debug.Print myDict(myKey)
        End If
    Next
    
End Sub

Sub Main()
    
    Dim DruzhbaPlaces As New Dictionary
    DruzhbaPlaces.Add "The Chinese Restaurant", "A nice place"
    DruzhbaPlaces.Add "The carwash", "A place to wash your car"
    DruzhbaPlaces.Add "The school", "This is where we studied"
        
    Dim MladostPlaces As New Dictionary
    MladostPlaces.Add "The Business Park", "Where I used to work!"
    MladostPlaces.Add "McDonald's", "Where I was invited to party! :)"
            
    Dim SofiaNeighbourhoods As New Dictionary
    SofiaNeighbourhoods.Add "Druzhba 2", DruzhbaPlaces
    SofiaNeighbourhoods.Add "Mladost 2", MladostPlaces
    
    Dim BulgarianCities As New Dictionary
    BulgarianCities.Add "Sofia", SofiaNeighbourhoods
    BulgarianCities.Add "Plovdiv", New Dictionary
        
    PrintNestedDictionary BulgarianCities, True, True
    
End Sub

As you see, in Sofia, we have “Druzhba” and there we have “The carwash”, which is “A place to wash your car”. Let’s see how this is going to be displayed:

Well, as expected. Enjoy!