Excel VBA: nested dictionary issue

Question

I am not able to create a nested dictionary, assign it to a variable, overwrite one of the inner values, and then assign it to another variable without the original variable's value getting changed, which I do not want. For example, see the following code:

Option Explicit

Sub Button1_Click()

  Dim d_outer As Scripting.Dictionary
  Set d_outer = New Scripting.Dictionary

  Dim d_inner As Scripting.Dictionary
  Set d_inner = New Scripting.Dictionary

  Call d_inner.Add("key", "foo")

  Call d_outer.Add("first attempt", d_inner)

  ' Cannot use "Add", since key already exists, must use Item()
  d_inner.Item("key") = "bar"

  Call d_outer.Add("second attempt", d_inner)

  ' Print all values.
  Dim v_outer As Variant
  Dim v_inner As Variant
  For Each v_outer In d_outer.Keys()
    For Each v_inner In d_outer(v_outer).Keys()
      Debug.Print "(" & v_outer & ", " & v_inner & "): '" & d_outer(v_outer)(v_inner) & "'"
    Next v_inner
  Next v_outer
End Sub

This produces the following output:

(first attempt, key): 'bar'
(second attempt, key): 'bar'

The first attempt's value should be foo. Why is it getting changed to bar? How do I fix this? Do I need to create a new dictionary that's an exact copy of d_inner every time I want to change only one of the values? If so, is there an easy way to do that?

Answer 1

In your first collection you have created a reference to an object rather than placing a value in there (for example). So as you change the inner collection it is updated in the initial outer collection.

You need to create a New object to put into the second collection. Like this:

  ' Cannot use "Add", since key already exists, must use Item()      
   Set d_inner = New Scripting.Dictionary
   Call d_inner.Add("key", "bar")

Gives:

(first attempt, key): 'foo'
(second attempt, key): 'bar'

Depending on what you are trying to achieve here, you might find that classes are more flexible with these kinds of tasks