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:
...and this seems to overcome the problem but perhaps there's a better solution?
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.
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:
Code:
' 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?
Code:
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.