When is a Range not a Range

I’m too lazy to retype everything so here is a link to the original thread at VBA Express.

I had always been under the impression that the Range property returns a new object instance every time it is called, even if you are referring to the same range. So, unlike Sheets for instance, where you can use the Is operator (or ObjPtr if you prefer) to test if one variable refers to the same sheet as another, like this:

Sub bar()
Dim ws1 As Worksheet
Dim ws2 As Worksheet

Set ws1 = Sheets(1)
Set ws2 = Sheets(1)
' this returns True
MsgBox ws1 Is ws2
End Sub

you cannot do the same with Range:

Sub foo()
Dim r1 As Range
Dim r2 As Range

Set r1 = Range("A1")
Set r2 = Range("A1")
' this will always return False
MsgBox r1 Is r2
End Sub

 

However, it seems that it is not the case that Range always returns a new object. It appears that there is some sort of internal heap that can be reused if necessary:

Sub foobar()
Dim w1 As Object
Dim w2 As Object
Dim w3 As Object

Set w1 = Range("A1")
Set w2 = Range("A1")
Set w3 = Range("A1")

Debug.Print "w1=" & ObjPtr(w1)
Debug.Print "w2=" & ObjPtr(w2)
Debug.Print "w3=" & ObjPtr(w3)

' clear them all
Set w1 = Nothing
Set w2 = Nothing
Set w3 = Nothing

' these should all be (and are!) 0
Debug.Print "w1=" & ObjPtr(w1)
Debug.Print "w2=" & ObjPtr(w2)
Debug.Print "w3=" & ObjPtr(w3)

' now reinitialize the variables
Set w1 = Range("A1")
Set w2 = Range("A1")
Set w3 = Range("A1")

' note that the pointers are the same but in a different order
' it appears that they are reclaimed from the top of an internal heap
Debug.Print "w1=" & ObjPtr(w1)
Debug.Print "w2=" & ObjPtr(w2)
Debug.Print "w3=" & ObjPtr(w3)

' and finally, just for kicks
Debug.Print Range("A1") Is Range("A1")
End Sub

 

I realise that this is probably of use / relevance / interest to very few people, but then that is kind of the leitmotif of this site. 😉

Leave a Reply

Your email address will not be published. Required fields are marked *