Excel VBA: nested dictionary issue -

i not able create nested dictionary, assign variable, overwrite 1 of inner values, , assign variable without original variable's value getting changed, not want. example, see following code:

option explicit  sub button1_click()    dim d_outer scripting.dictionary   set d_outer = new scripting.dictionary    dim d_inner 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 exists, must use item()   d_inner.item("key") = "bar"    call d_outer.add("second attempt", d_inner)    ' print values.   dim v_outer variant   dim v_inner variant   each v_outer in d_outer.keys()     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 following output:

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

the first attempt's value should foo. why getting changed bar? how fix this? need create new dictionary that's exact copy of d_inner every time want change 1 of values? if so, there easy way that?

in first collection have created reference object rather placing value in there (for example). change inner collection updated in initial outer collection.

you need create new object put second collection. this:

  ' cannot use "add", since key 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 trying achieve here, might find classes more flexible these kinds of tasks