VBA Dictionary of dictionaries

[Aenei]

Hi all, apologies in advance if there is a solution to this somewhere - I have been unable to find it either on these boards or the internet at large.

I am trying to create a dictionary (Dictionary object in reference Microsoft Scripting Runtime) where the values to the keys are themselves dictionaries. I have a loop which populates the sub-dictionary, writes the sub-dictionary to the main dictionary, then goes back generates a new sub-dictionary writes it back, etcetera. What I get is a dictionary with the correct keys, but all the values are identical dictionaries (the last one to be added). I guess that the sub-dictionary is being added to the main dictionary by reference, so when the loop goes to create the next one it's overwriting all previous keys too.

This code replicates my problem:

' output:
'First dictionary,First dictionary,1
'-----
'First dictionary,Second dictionary,2
'Second dictionary,Second dictionary,2
Function test()
    Dim d As New Dictionary
    Dim e As New Dictionary
 
    e.Add "Key1", "First dictionary"
    e.Add "Key2", "1"
    d.Add e("Key1"), e
    For i = 0 To d.Count - 1
        Debug.Print d.keys(i) & "," & d.Items(i).Item("Key1") & "," & d.Items(i).Item("Key2")
    Next
    Debug.Print "-----"
 
    e.RemoveAll
    e.Add "Key1", "Second dictionary"
    e.Add "Key2", "2"
    d.Add e("Key1"), e
    For i = 0 To d.Count - 1
        Debug.Print d.keys(i) & "," & d.Items(i).Item("Key1") & "," & d.Items(i).Item("Key2")
    Next
End Function

...and this seems to overcome the problem but perhaps there's a better solution?

Function addict(dict As Dictionary, subdict As Dictionary, k As String) As Dictionary
    dict.Add k, New Dictionary
    For i = 0 To subdict.Count - 1
        dict.Item(k).Add subdict.keys(i), subdict.Items(i)
    Next
    Set addict = dict
End Function
' output:
'First dictionary,First dictionary,1
'-----
'First dictionary,First dictionary,1
'Second dictionary,Second dictionary,2
Function test2()
    Dim d As New Dictionary
    Dim e As New Dictionary
 
    e.Add "Key1", "First dictionary"
    e.Add "Key2", "1"
    Set d = addict(d, e, e("Key1"))
    For i = 0 To d.Count - 1
        Debug.Print d.keys(i) & "," & d.Items(i).Item("Key1") & "," & d.Items(i).Item("Key2")
    Next
    Debug.Print "-----"
 
    e.RemoveAll
    e.Add "Key1", "Second dictionary"
    e.Add "Key2", "2"
    Set d = addict(d, e, e("Key1"))
    For i = 0 To d.Count - 1
        Debug.Print d.keys(i) & "," & d.Items(i).Item("Key1") & "," & d.Items(i).Item("Key2")
    Next
End Function

Perhaps this is the wrong approach entirely - it grew out of a dictionary of collections, and I decided to recode the collections in favour of dictionaries for ease of use.

 

[Norie]

Perhaps if you explain, in words, what you are trying to achieve somebody might be able to tell you if this is the right/wrong approach.

And if it is the wrong approach then perhaps another one can be suggested.

PS Why are you using Function?

Function is normally used when you want to return a value, and the code you've posted isn't doing that.

 

[Aenei]

Essentially I'm parsing a table from an external source and I want to be able to access the data in it in a relatively straightforward fashion. The table contains unique field which I use to generate the keys of the main dictionary, and then using a sub-dictionary lets me access a property of the record knowing its name. I don't just want to put it in a 2D array because finding property( record ) is cumbersome.

Is there a better way?

 

[northwolves]

The items of dictionary could be string,numbers,array or object.
Use dictionary+array to solve your question,have a glance at the following code,maybe it helps you:

Sub macro1()
Dim arr, i As Long, j As Long, dic As Object
Set dic = CreateObject("scripting.dictionary")
For i = 2 To 100
ReDim arr(1 To i)
For j = 1 To i
arr(j) = j
Next
dic("d" & i) = arr
Next
i = Int(Rnd * 99 + 1)
MsgBox Join(dic("d" & i), ","), , "dic(""d" & i & """)"
End Sub

Regards
Northwolves

 

[MattJK]

Hi all, apologies in advance if there is a solution to this somewhere - I have been unable to find it either on these boards or the internet at large.

I am trying to create a dictionary (Dictionary object in reference Microsoft Scripting Runtime) where the values to the keys are themselves dictionaries. I have a loop which populates the sub-dictionary, writes the sub-dictionary to the main dictionary, then goes back generates a new sub-dictionary writes it back, etcetera. What I get is a dictionary with the correct keys, but all the values are identical dictionaries (the last one to be added). I guess that the sub-dictionary is being added to the main dictionary by reference, so when the loop goes to create the next one it's overwriting all previous keys too.

This code replicates my problem:

' output:
'First dictionary,First dictionary,1
'-----
'First dictionary,Second dictionary,2
'Second dictionary,Second dictionary,2
Function test()
    Dim d As New Dictionary
    Dim e As New Dictionary
 
    e.Add "Key1", "First dictionary"
    e.Add "Key2", "1"
    d.Add e("Key1"), e
    For i = 0 To d.Count - 1
        Debug.Print d.keys(i) & "," & d.Items(i).Item("Key1") & "," & d.Items(i).Item("Key2")
    Next
    Debug.Print "-----"
 
    e.RemoveAll
    e.Add "Key1", "Second dictionary"
    e.Add "Key2", "2"
    d.Add e("Key1"), e
    For i = 0 To d.Count - 1
        Debug.Print d.keys(i) & "," & d.Items(i).Item("Key1") & "," & d.Items(i).Item("Key2")
    Next
End Function

...and this seems to overcome the problem but perhaps there's a better solution?

Function addict(dict As Dictionary, subdict As Dictionary, k As String) As Dictionary
    dict.Add k, New Dictionary
    For i = 0 To subdict.Count - 1
        dict.Item(k).Add subdict.keys(i), subdict.Items(i)
    Next
    Set addict = dict
End Function
' output:
'First dictionary,First dictionary,1
'-----
'First dictionary,First dictionary,1
'Second dictionary,Second dictionary,2
Function test2()
    Dim d As New Dictionary
    Dim e As New Dictionary
 
    e.Add "Key1", "First dictionary"
    e.Add "Key2", "1"
    Set d = addict(d, e, e("Key1"))
    For i = 0 To d.Count - 1
        Debug.Print d.keys(i) & "," & d.Items(i).Item("Key1") & "," & d.Items(i).Item("Key2")
    Next
    Debug.Print "-----"
 
    e.RemoveAll
    e.Add "Key1", "Second dictionary"
    e.Add "Key2", "2"
    Set d = addict(d, e, e("Key1"))
    For i = 0 To d.Count - 1
        Debug.Print d.keys(i) & "," & d.Items(i).Item("Key1") & "," & d.Items(i).Item("Key2")
    Next
End Function

Perhaps this is the wrong approach entirely - it grew out of a dictionary of collections, and I decided to recode the collections in favour of dictionaries for ease of use.

You've probably completed your project by now, but all you really needed to do was change

e.RemoveAll

to

Set e= New Dictionary

 

[Faragoz]

You've probably completed your project by now, but all you really needed to do was change

e.RemoveAll

to

Set e= New Dictionary

Hello MattJK,

I'm so thankful for that moment when you decided to post this even if probably the author has already solved it because you helped me to solve this same issue that I was facing for days... I knew that dictionaries store information differently than arrays, but I didn't figure out how to use them as arrays and preserve the dynamics of the dictionaries until I found your answer.

I signed in to the forum just to let you know that and to thank you so much.

Probably I will come back to post my code and see if someone can suggest me a way to optimize it.
Have a nice day, mate.