excel vba: dictionary and array; new entry is by values or by reference?

Question

How exactly does add work in a dictionary? i.e why is there the different behaviors highlighted in the posted code?

I have several questions about the execution speed but first I would like to understand well how adding and changing values in a dictionary works.

When it works, how is the change handled internally? is the content of the pointed memory area changed or is an element deleted and a new one added with the same key?

Regards


Sub test()
    Dim diz As New Dictionary
    Dim vett(1 To 2) As Integer
    Dim val As Integer
    Dim dvett As New Dictionary
    Dim a As Variant
    
    Select Case 3 'change to run a branch
    Case 1
        val = 1
        
        diz.Add "a", val
        Debug.Print diz("a") '1
                
        diz("a") = 2
        Debug.Print diz("a") '2
        Debug.Print val      '1 => copy by val, not by ref
        
        val = 3
        Debug.Print diz("a") '2 => copy by val, not by ref
        
    Case 4
        diz.Add "a", [{"a1","b1","c1"}]
        Debug.Print diz("a")(1) 'a1 - OK
        
        diz.Items(0)(1) = "2"   'NO errore
        Debug.Print diz("a")(1) 'a1 !!!!!!!!!!!!! nothing change
        diz("a")(1) = "2"
        Debug.Print diz("a")(1) 'a1 !!!!!!!!!!!!! nothing change
        
        diz("a") = [{4,5,6}]
        Debug.Print diz("a")(1) '4 - OK
    
    Case 2
        vett(1) = 1
        diz.Add "a", vett
        Debug.Print diz("a")(1) '1
        
        diz("a")(1) = 2         'NO errore
        Debug.Print diz("a")(1) '1 !!!!!! nothing change
        Debug.Print vett(1)     '1
        
        vett(1) = 3
        Debug.Print diz("a")(1) '1
        
        diz("a") = vett
        Debug.Print diz("a")(1) '3
    
    Case 3
        dvett(1) = 1
        diz.Add "a", dvett
        Debug.Print diz("a")(1) '1
        
        diz("a")(1) = 2
        Debug.Print diz("a")(1) '2
        Debug.Print dvett(1)    '2 in this case copy by ref!!!!!
        
        dvett(1) = 3
        Debug.Print diz("a")(1) '3 in this case copy by ref!!!!!
                
    End Select
End Sub```

user_109122 · Answer

So yes, you've discovered one of the most annoying things about VBA (and VB6 and all COM-based VB's): Array-handling is very counter-intuitive and does not work the way that almost everyone expects it to.

The way that your code acts when putting and getting scalar values (numbers, etc.) to/from a dictionary is as you would think: scalars are not objects and are stored as a short contiguous location in memory. Consequently, as we would expect, their value is just copied to/from the dictionary, just as it is everywhere else:

x = 1          ' puts 1 into x's storage location
y = x          ' copies x's value into y's storage location
x = 2          ' puts 1 into x's storage location. Does not change y
dic("a") = x   ' makes an entry in dic and copies x's value into it
dic("a") = y   ' copies y's value into the dic entry. Does not change x

This is all what you would expect since it's the same space as using pointers (4 bytes) and more CPU-efficient (less indirection).

Less obvious perhaps is that even though strings are refs (that is storage locations that are addresses pointing into the memory allocation heap) they are also treated as scalars in VBA and behave in exactly the same way:

x = "k"        ' makes "k" then changes x's ref to point to it
y = x          ' copies x's value then changes y's ref to point to it 
x = "j"        ' makes "j" then points x's ref to it. Does not change y
dic("a") = x   ' makes an entry in dic, copies x's value, then points the entry to it
dic("a") = y   ' copies y's value then points the entry to it. Does not change x

So even though strings can be much longer than 4 bytes and have to be references, it still makes copies of them all over the place. And part of what makes this a bit confusing is that when it comes to parameter-passing, then by default it actually passes all scalars by reference instead.

As it turns out, arrays in VBA are also treated like scalars. That is, even though by their size and structure you might think that they were treated like objects (by reference), they are actually still treated like scalars and copied around:

Dim X() As Integer
Dim Y() As Integer
ReDim X(3)
ReDim Y(4)

X(1) = 1       ' puts 1 into the (1) offset pointed to by X's ref
Y = X          ' copies X's values then changes Y's ref to point to them 
X(2) = 2       ' Does not change Y(2)
Y(3) = 3       ' Does not change X(3)
dic("a") = X   ' copies X's values, then points the entry to it
dic("a") = Y   ' copies Y's value then points the entry to it. Does not change X
X = dic("a")   ' copies the entries values then points X to them

Because arrays can have an extra level of sub-addressing (indexing to individual cells) you get really unexpected results when you try to do that within a dictionary (or almost any other object that can hold an array):

dic("a")(1) = 1 ' makes a *temporary* copy of the array stored in the 
                ' dictionary entry, puts 1 into that arrays (1) offset.
                ' Does not change Y. 
                ' Does NOT change the dic entry EITHER.

So, just like integers, strings and all other VBA "scalars", arrays are always copied (expect when passed by ref as a Sub/Function parameter (the default)). Even though by their size and structure that seems like a bad idea, nonetheless, that's how they are handled.

This is all fixed in VB.Net where they sensibly decided that since arrays were like objects in every other respect and since it was crazy to be making unneeded (and mostly unwanted) copies of these potentially huge structures, they should just formally make them objects and treat them like objects. That of course is no help to those of us who still have to work in VBA, but at least we don't have to deal with it anywhere else.

let me try to explain what happens in the last statement dic("a")(1) = 1:

it finds the dictionary entry for the key-value "a"
In this entry it finds an array which is a copy of the array Y. Let's call this entryY, an array which was previously copied from the array Y into this dictionary entry. If entryY is change, then Y will not change. Likewise, if Y is changed this will have no effect on entryY.
It then makes a temporary copy of entryY. Let's call this copy tempY.
Then it sets the value of the first value in tempY to 1. That is it does tempY(1) = 1. This has no effect on the array Y, nor on entryY.
Now the statement is completed. Because tempY is temporary and no longer needed it is deleted.

I hope this makes it clearer.

user_10788265 · Answer

@RBarryYoung First of all, thank you very much for the long answer

I apologize for my late reply but I had both connection problems and a bit of difficulty in understanding (I hope) the final part of the answer.

In this regard, to be sure I understand, I try to share what I imagined.

Having established that array are seen as scalars and therefore copied by value, I could not understand where a copy operation was needed (be it by value or by reference) in the phase of assigning a constant.

In fact, the copying mechanism concerns the right part of the assignment: in a by value copy, the content of the referenced variable is first copied to a certain area of the memory and then the variable on the left is made to point to that area; in by reference copy, instead, the variable on the left is made to point directly to the memory area pointed to by the variable on the right.

That said, to get back to me your statement of the "temporary copy", I assume that the dictionaries are, or in any case behave, like classes in which the content of the dictionary itself is stored as a private variable; so a get call (i.e a ".items (x)") requires a copy of the private variable to return its contents to the caller. HERE IS THE COPY !!

If we combine the two concepts we have that the 1st part of the instruction dic("a")(1) = 1 specifically dic("a"), also equal to dic.items("a"), copies the content of the corresponding "value" field, which, being a matrix, is carried out by value as above explained. This result is passed to the second part of the command, specifically "... (1) = 1", which offset the pointer to the specified entry position of the matrix and writes 1.

This done ... everything is thrown away i.e. abandoned as it is not pointed to by anyone.

By interrogating the given position of the matrix again, the process is repeated starting again from the unchanged private copy of the diz

Correct???

Thanks again and greetings.

excel vba: dictionary and array; new entry is by values or by reference?

2 Answers