Variable Ref in collection

Question

This is my problem. i've just recongnized that collection are not a ref table of other variables. it look like an item added to a collection is not a ref in the collection but it has been "doubled" in a way.

Sub TestCollection()
'--------------------------------------------------------
' definition
Dim COLL As New Collection
Dim x As Double
Dim xr As Range
'--------------------------------------------------------
' Give a value to x and xr
Set xr = Range("A1")
x = 1
xr = 1
'--------------------------------------------------------
' Add to the collection
COLL.Add x, "x"
COLL.Add xr, "xr"
'--------------------------------------------------------
' First debug
Debug.Print "Original value of x and xr (range)"
Debug.Print COLL(1)
Debug.Print COLL(2).Value
'--------------------------------------------------------
' Change the value
x = 2
xr = 2
'--------------------------------------------------------
' Second debug
Debug.Print "Now vba will change x and xr (range)"
Debug.Print COLL(1)
Debug.Print COLL(2).Value
'--------------------------------------------------------
' Change the Ref on xr
x = 3
Set xr = Range("A2")
xr = 3
'--------------------------------------------------------
' Third debug
Debug.Print "Now vba will change x and xr (ref)"
Debug.Print COLL(1)
Debug.Print COLL(2).Value
'--------------------------------------------------------
End Sub

Debug-print values:

Original value of x and xr (range)
 1 
 1 
Now vba will change x and xr (range)
 1 
 2 
Now vba will change x and xr (ref)
 1 
 2 

x an xr are not a ref in the collection but they are different object.

There is a possibility for having a collection of ref object as i wanted?

Answer 1

Your wording is strange, but I think I see what you're trying to do, and your answer is "nope". x isn't an object in the first place, so that one's out of the equation.

xr is an object reference, but when you added it to the collection what you've added is a copy of the pointer to that object.

So you have xr pointing to [A1], and a collection item pointing to [A1] as well. If you do this:

Range("A1").Value = 2

Then I'd expect both xr.Value and COLL("xr").Value to output 2, because both are pointing to that same object.

Except then you go and do this:

Set xr = Range("A2")

You've just discarded a copy of the object pointer, named xr, and now you have xr pointing to [A2], and a collection item still pointing to [A1]. So when you do this:

Range("A2").Value = 3

You can't expect COLL("xr").Value to be 3, because it's not a pointer to the same object anymore.

There's no way the collection could have known that the value it's holding at index/key "xr" needed to automagically start pointing to [A2] after you've re-assigned xr: that's just not how object references work.