Is operator on range references

[Richard Schollar]

Can anyone explain to me why the following code returns False when comparing r1 and r2 but True for ws1 and ws2:

Sub test()
Dim r1 As Range, r2 As Range, ws1 As Worksheet, ws2 As Worksheet
Set r1 = ActiveSheet.Range("A1")
Set r2 = ActiveSheet.Range("A1")
MsgBox r1 Is r2   'Expect to return True, but actually returns False

Set ws1 = ActiveSheet
Set ws2 = ActiveSheet
MsgBox ws1 Is ws2   'returns True
End Sub

Is it because range references aren't truly objects?

Thanks

Richard

 

[hatman]

It gets even better... this comes back true:

Sub stuff()

    Dim rnge As Range
    Dim rnge2 As Range
    
    Set rnge = Range("a1:a2")
    Set rnge2 = rnge
    
    
    MsgBox rnge Is rnge2

End Sub

 

[pgc01]

Hi all

This is strange. Even without setting the objects to variables, you still get a bad result for the range object (?).

Sub test()

MsgBox Range("A1") Is Range("A1")                     'Expect to return True, but actually returns False

MsgBox Worksheets("Sheet1") Is Worksheets("Sheet1")   'Expect to return True, and returns true

End Sub

Hope someone explains.
PGC

 

[fairwinds]

Hi,

http://support.microsoft.com/default.aspx?scid=kb;en-us;111493

 

[pgc01]

Great, Fairwinds!

Now it makes sense.

Cheers.
PGC

 

[hatman]

If I understand the Is operator properly, it is examining the memory address of each object to determine if they are the same. Is it possible that a Range Object has no memory address because it is not discreet? Here me out: the actual object is dependent upon the argument, therefore it has no dimension until you instantiate it by calling it. As opposed to a worksheet which is a dicreet object (Activesheet simply maps to the memory address of the appropriate worksheet object) If that were the case, I would speculate that each time you create a reference to a Range, VBA is doing some hand-waving... it is actually creating a new memory address in each instance, but is LINKING with the orginal object so it has the appearance (read functionality) of a normal object manipulation. The exception is my example from above, where rnge gets a new memory address, which is dicreet, so when I say set rnge2=rnge, I have mapped into the same memory address.

EDIT: I leave for a fire drill, and by the time I get back, I'm a day late and a dollar short

 

[Richard Schollar]

Thanks Fairwinds

Richard

 

[pgc01]

Now, after reading the article, it is clear that it could not be any other way. When we use the Set Rng=Range(SomeRange) we can create an immense number of different ranges. It would not be possible for excel to have created all the different possibilities at start-up, both because of lack of resources and for the time it would take. So each time we invoke Range, excel creates a new instance of the range object and initialises it accordingly. Each time we get a new object.

Thanks Richard for bringing this up.
PGC