excel - Updating an array stored in a VBA dictionary

Question

I'm creating a data structure that uses nested dictionaries and a list at the lowest level. Here's a sample of my data:

Country, Customer, Purchased
US, Alan, Lawnmower
US, Alan, Hammer
US, Karen, Donkey
US, Simon, Mustang
MX, Carl, Lawnmower
MX, Alan, Donkey
...

The data structure I have in mind looks like dictionary --> dictionary --> array -- that is, country --> customer --> purchased. The plan is for there to be a new array per dictionary --> dictionary combination.

However, when I try to update the array, it seems that it is linked to all lower levels of the dictionary --> dictionary structure. That is, after the third row has been processed, have the following situation:

US --> Alan --> [Lawnmower, Hammer, Donkey]
US --> Karen --> [Lawnmower, Hammer, Donkey]

... whereas what I'm expecting to see is:

US --> Alan --> [Lawnmower, Hammer]
US --> Karen --> [Donkey]

Here's the code I'm attempting to use:

i_p = UBound(purchased_array)

Redim Preserve purchased_array(i_p + 1)

purchased_array(i+p + 1) = item ' new item to add to the array

dataset(country)(customer) = purchased_array

However, this results in basically the same array being referenced by each lowest level of the dictionary --> dictionary structure.

Any thoughts on what I'm doing wrong?

Answer 1

If you have an array in a dictionary you must pull it out of the dictionary before you can modifiy it. Then put it back in.

Sub Tester()

Dim x As Long, y As Long
Dim dict As New Scripting.Dictionary
Dim d As Scripting.Dictionary
Dim arr

    For x = 1 To 3
        Set d = New Scripting.Dictionary
        For y = 1 To 3
            d.Add "nextkey" & y, Array("A_" & x & "_" & y, _
                                       "B_" & x & "_" & y)
        Next y
        dict.Add "key" & x, d
    Next x

    Debug.Print Join(dict("key1")("nextkey1"), ", ") '>> A_1_1, B_1_1

    'try to modify array while stored in dictionary...
    dict("key1")("nextkey1")(1) = "newValue1" '<<< doesn't work!

    Debug.Print Join(dict("key1")("nextkey1"), ", ") '>> A_1_1, B_1_1

    'have to pull it out of the dictionary if you want to change it...
    arr = dict("key1")("nextkey1")
    arr(1) = "newValue2"
    dict("key1")("nextkey1") = arr

    Debug.Print Join(dict("key1")("nextkey1"), ", ") '>> A_1_1, newValue2

End Sub