confustion about what the meaning of "is" is 1

[electricpete]

Help tell me that "is" allows us to test whether two object reference variables point to the same object.

I tried to test it with the following code and get some results I don't understand.

Sub test1()
Sheet1.Select
Names.Add Name:="cellA3", RefersTo:=Range("A3")
Debug.Print "range =-comparison gives " & CStr(Range("cellA3") = Range("a3"))
Debug.Print "range is-comparison gives " & CStr(Range("cellA3") Is Range("a3"))

Dim r1 As Range, r2 As Range
Set r1 = Sheet1.Range("A3")
Set r2 = Sheet1.Range("cellA3")
Debug.Print "object var =-comparison gives " & CStr(r1 = r2)
Debug.Print "object var is-comparison gives " & CStr(r1 Is r2)
End Sub

The results in the immediate window are:

range =-comparison gives True
range is-comparison gives False
object var =-comparison gives True
object var is-comparison gives False

I can understand why the "=-comparisons" (equals comparisons) are true. It is comparing the default property of range (value) for two ranges that are the same (A3) and coming up true.

I don't understand why the "is-comparisons" (is comparisons) are false. Don't r1 and r2 point to the same object?

Is there some stupid error I made, or some misunderstanding displayed? I'm confused.

 

[xlbo]

To aid understanding

I am human.
You are human

Therefore GeoffType = PeteType

However

We are different objects. I am not you therefore

Geoff Is Not Pete

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[electricpete]

Thanks Geoff. I agree with your statement about humans, but doesn't clarify the situation that I can see.

The situation I see is one person with two different names. It is still the person.

I have one object - cell A3. It goes by two different names: r1 and r2. I would have though "r1 is r2" would be true.

 

[electricpete]

In fact, get rid of the named range confusion. More straightforward:

Sub test1()
Set r1 = Sheet1.Range("A3")
Set r2 = Sheet1.Range("A3")
Debug.Print "object var =-comparison gives " & CStr(r1 = r2)
Debug.Print "object var is-comparison gives " & CStr(r1 Is r2)
End Sub

Still returns:
object var =-comparison gives True
object var is-comparison gives False

 

[combo]

Guess that the reason is that 'Range' object/collection is specific in excel. Even when you refer to the same address, excel instantiate another object:

Dim r1 As Range, r2 As Range, r3 As Range
Set r1 = ThisWorkbook.Worksheets(1).Range("A1")
Set r2 = ThisWorkbook.Worksheets(1).Range("A1")
Set r3 = r1
' this terurns False
MsgBox (r1 Is r2) & vbLf & "Pointers (r1, r2):" & vbLf & ObjPtr(r1) & "," & ObjPtr(r2)
' this returns true
MsgBox (r1 Is r3) & vbLf & "Pointers (r1, r3):" & vbLf & ObjPtr(r1) & "," & ObjPtr(r3)

combo

 

[TonyJollans]

To expand a little on what combo says...

When you declare a Range variable you are actually declaring a pointer, not a Range object itself.

When you set a pointer to an existing object (as in [blue][tt]Set r3 = r1[/tt][/blue]), that is all you are doing; you are not instantiating another object (you could if you chose to, by using r1's Range property, but the code isn't doing it). Change the object, using either pointer, and both pointers will reflect the change.

When you use the Worksheet Range property, it instantiates a new range and returns you a pointer to it. It doesn't go checking every other Range variable you've got to see if there's one that happens to span the same cells at that point in time and then return you a pointer to it - and nor would you want it to, it would be an absolute nightmare to work with. So r1 and r2 (in the example) will always point to different objects.



Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

I'm working (slowly) on my own website

 

[electricpete]

Thanks. So "is" doesn't tell you anything about what the object variables point to (do they point to the same object?)... it just tells you about how the object variables were created (was one created from the other?).

Seems pretty useless to me. And excel should fix their help which states:

Is Operator
Used to compare two object reference variables.

Syntax result = object1 Is object2

The Is operator syntax has these parts:
Part Description
result Required; any numeric variable.
object1 Required; any object name.
object2 Required; any object name.

Remarks
If object1 and object2 both refer to the same object, result is True; if they do not, result is False.

The bolded sentence sounds like it has been contradicted by our example.

 

[TonyJollans]

No! You are confusing objects with what the objects reference.

I have this thread in Tek-Tips displayed in my Browser and you have this thread in Tek-Tips displayed in your Browser. They are both the same, they were source in the same way, they refer to the same database records on the Tek-Tips server, but ... they are not the same object; if I close my browser window, yours stays open.

The action of instantiating a Range object and the act of copying a pointer to a Range object are fundamentally different. The Range property instantiates a Range object; each time you use it, a new Range object is created, and remains until all references to it have been removed. You may well have many Range objects and, at various times, one may actually reference the same collection of cells as another, but that does not make t hem the same object.

I do tend to agree with you that the [blue][tt]Is[/tt][/blue] operator is of limited use in this context.

Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

I'm working (slowly) on my own website