VBA Dictionary of dictionaries

Aenei

New Member
Joined
Jan 20, 2009
Messages
2
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:
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.
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
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.:)
 
Upvote 0
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?
 
Upvote 0
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:
Code:
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
 
Upvote 0
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:
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.
You've probably completed your project by now, but all you really needed to do was change
VBA Code:
e.RemoveAll
to
VBA Code:
Set e= New Dictionary
 
Upvote 0
You've probably completed your project by now, but all you really needed to do was change
VBA Code:
e.RemoveAll
to
VBA Code:
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.
 
Upvote 0

Forum statistics

Threads
1,223,604
Messages
6,173,319
Members
452,510
Latest member
RCan29

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top