VBA Array function converts variable with a simple type to a value and does not store variable reference

Question

VBA Array function converts variable with a simple type to a value and does not store variable reference. But if I pass a class variable it works as intended. Is it supposed to be like that or I'm missing something?

main module

Sub test()

Dim collection As collection
Set collection = New collection

Dim i As Long
Dim cl As New tst

i = 25
cl.i = 25

Debug.Print "Original i = " & i
Debug.Print "Original cl.i = " & cl.i
Debug.Print

collection.Add Item:=Array(i)
collection.Add Item:=Array(cl)

Debug.Print "i in array in collection: collection(1)(0) = " & collection(1)(0)
Debug.Print "i from class in array in collection: collection(2)(0).i = " & collection(2)(0).i
Debug.Print

i = i + 1
cl.i = cl.i + 1

Debug.Print "Adding: i = i + 1"
Debug.Print

Debug.Print "Original i = " & i ' 26
Debug.Print "Original cl.i = " & cl.i '26
Debug.Print

Debug.Print "i in array in collection: collection(1)(0) = " & collection(1)(0) '25, but I expect 26
Debug.Print "i from class in array in collection: collection(2)(0).i = " & collection(2)(0).i '26 correct
Debug.Print

End Sub

class tst

Public i As Long

Output

Original i = 25
Original cl.i = 25

i in array in collection: collection(1)(0) = 25
i from class in array in collection: collection(2)(0).i = 25

Adding: i = i + 1

Original i = 26
Original cl.i = 26

i in array in collection: collection(1)(0) = 25
i from class in array in collection: collection(2)(0).i = 26

Yes that's how it works. Non-object variables are assigned by value, object variables are assigned by reference. — Tim Williams, Dec 05 '19 at 19:51
@TimWilliams I find this way of explaining things extremely confusing. `Collection.Add` does not care whether you're giving it an object or a value, it's receiving it `ByVal` either way. — Mathieu Guindon, Dec 05 '19 at 20:04
@MathieuGuindon - I left my comment knowing you'd have a better explanation... I do think it's worth distinguishing between the act of *assigning* and *passing* though... — Tim Williams, Dec 05 '19 at 20:11

Mathieu Guindon · Accepted Answer · 2019-12-05T20:17:08.157

Is it supposed to be like that

Definitely. And the behavior is exactly the same regardless of whether you're passing a value or an object reference.

When you add i to the collection, a copy of the value (ByVal) of i is added to the collection; incrementing i will not increment the copy.

When you add cl to the collection, a copy of a pointer (also ByVal) to that object is added to the collection; modifying that pointer will not modify the copy in the collection either:

Public Sub test()
    Dim c As Collection
    Set c = New Collection
    Dim o As Class1
    Set o = New Class1
    Debug.Print VarPtr(o), ObjPtr(o)
    c.Add o
    Debug.Print VarPtr(c(1)), ObjPtr(c(1))
End Sub

This code outputs something like this:

 725937672     221317072 
 725937528     221317072

Note that the two pointers are different, but both are pointing to the same object reference.

If we affect the local copy of that object pointer locally...

Debug.Print VarPtr(o), ObjPtr(o)
c.Add o
Set o = Nothing
Debug.Print VarPtr(c(1)), ObjPtr(c(1))
Debug.Print VarPtr(o), ObjPtr(o)

The output is now as follows:

 725937672     221317072 
 725937528     221317072 
 725937672     0

We re-assigned o to Nothing, and yet the collection c still has a reference to the object! We didn't affect the object at all!

The Public i member of your test class was never copied anywhere, the collection doesn't even know/care about it: all the collection knows, is that it's holding a reference to an object.

So when you do cl.i = 26, you're not assigning anything the collection knows about. You're only saying "take the object at address X (the same ObjPtr held in the collection!), get its i property, and assign it the value 26": the collection's copy of the pointer was never updated.

When you do i = 26, you're not assigning anything the collection knows about either. You're saying "take the variable at address X (the local variable), assign it the value 26": the collection's copy of the value was never updated.

Upvoted. I swear I've seen you lay out the copy of the *value* vs. copy of the *pointer* somewhere before. I suspected as much, but definitely could not put an answer like this together. Thanks for the detail. I should be using `VarPtr` and `ObjPtr` more :-) — BigBen, Dec 05 '19 at 20:06
@BigBen [here, perhaps](https://stackoverflow.com/a/55405418/1188513) — Mathieu Guindon, Dec 05 '19 at 23:11

VBA Array function converts variable with a simple type to a value and does not store variable reference

1 Answers1