VBA Arrays as Dictionary values : Unexpected error when changing values [duplicate]

Question

I wanted to use an Array as set of values in a Dictionary.
Why is it so, that I cannot change the values in those arrays the same way as I would change them if they were "simple" Arrays?
Let see some examples:
If I create an array, and change the value of n-th element it happens OK.

Sub arrTest()
    Dim a As Variant
    a = Array(1, 2, 3)
    Debug.Print a(0), a(1), a(2)
    a(1) = 8
    Debug.Print a(0), a(1), a(2)
End Sub
'Output:
' 1             2             3 
' 1             8             3

If I try to do the same with my arrays inside a Dictionary, they simply does NOT change, but also don't give an error message. See:

Sub tess()
    Dim Dic As Object, Coll As Collection
    Set Dic = CreateObject("scripting.dictionary")

    Dic.Add "A", Array(1, 2)
    Dic.Add "B", 5

    Debug.Print Dic.keys()(0), Dic(Dic.keys()(0))(0), Dic(Dic.keys()(0))(1)
    Debug.Print Dic.keys()(1), Dic(Dic.keys()(1))
    Dic("A")(1) = 8
    Dic("B") = 8
    Debug.Print Dic.keys()(0), Dic(Dic.keys()(0))(0), Dic(Dic.keys()(0))(1)
    Debug.Print Dic.keys()(1), Dic(Dic.keys()(1))
End Sub
'Output:
'A              1             2 
'B              5 
'
'A              1             2 
'B              8

In above example, my non-array value changed to 8 properly, while the value in array remained 2
Why Is it SO , Where is the mistake... etc.?

EDIT
As @FloLie suggested correctly, unfortunately upon interacting with Arrays inside Dictionary, an invisible copy is created, that makes the mess.
However I can't stand multiline clutter for simple operations in my code, so here is my
final solution:

Private Sub mReplaceDicArray(Dic As Object, kEy As Variant, Element As Integer, NewValue)
    Dim tempArray As Variant
    tempArray = Dic(kEy)
    tempArray(Element) = NewValue
    Dic(kEy) = tempArray
End Sub
' call as:
' Call mReplaceDicArray(Dic, "A", 1, 8)

@Vityata This seems to be actually the same. I haven't find it before when searched. However: That is NOT yet marked as having solution, while mine is + I have provided here an extra solution, that is not visible there. Can you MERGE the two threads? — Ádám Bukovinszki
– Ádám Bukovinszki, Commented Apr 16, 2018 at 10:45

FloLie · Accepted Answer · 2018-04-16 09:52:28Z

2

The problem is that when you get the array out of the dictionary with Dic("A"), you get a copy and not a reference. See:here

Solution is to temporary store the copy in a variable, manipulate and replace it in the dict:

mArray = Dic("A")
mArray(1) = 8
Dic("A") = mArray

answered Apr 16, 2018 at 9:52

FloLie

1,8511 gold badge10 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ádám Bukovinszki Over a year ago

Thaks, that helped. I have created a Method now to automate these steps (see in my edit). Cheers

Collectives™ on Stack Overflow

VBA Arrays as Dictionary values : Unexpected error when changing values [duplicate]

1 Answer 1

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Linked

Related