Is operator on range references

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
Can anyone explain to me why the following code returns False when comparing r1 and r2 but True for ws1 and ws2:

Code:
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
 

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

hatman

Well-known Member
Joined
Apr 8, 2005
Messages
2,664
It gets even better... this comes back true:

Code:
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

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,870
Hi all

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

Code:
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
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,870

ADVERTISEMENT

Great, Fairwinds!

Now it makes sense.

Cheers.
PGC
 

hatman

Well-known Member
Joined
Apr 8, 2005
Messages
2,664
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 :cry:
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,870
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
 

Watch MrExcel Video

Forum statistics

Threads
1,113,919
Messages
5,545,028
Members
410,647
Latest member
bernardazar
Top