2

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?

Improve this question
5
  • why dictionary --> dictionary --> array, why not dictionary --> dictionary --> collection? Commented Feb 21, 2014 at 16:46
  • I believed that a collection was a keyed data structure? Could I use it instead of an array? Commented Feb 21, 2014 at 16:59
  • sure, collection object support .Add(Item, [Key]) method, but key is optional. so, you could omit it. In your case it would be much simplier to use collection instead array Commented Feb 21, 2014 at 17:02
  • 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. Commented Feb 21, 2014 at 17:12
  • @TimWilliams when you say "pull it out" re: an array, what method are you talking about? I tried copying the array to a temp. array, and then inserting the temp. array, but had the same end result. Is there a method to set the array to NULL or similar? Commented Feb 21, 2014 at 17:16

2 Answers 2

Reset to default
6

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
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Okay, I think I follow what you've done. But what happens if you want to extend the arr array? That is, it is supposed to be a dynamic array, that grows/contracts depending on the number of entries in the purchased column.
Once it's outside of the dictionary you can use it like a regular array - if you want to extend it then that shouldn't be a problem.
0

Dictionary contains sets of pairs. Also, the keys list in Dictionary has to be unique. Within the top-level dictionary containing the relationship between Country & Customer, neither country nor customer is unique by that standard. So, I don't think dictionary is appropriate. Maybe you can try Dictionary -> Array -> Array.

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.