"Is" operator gives me False, Can I know the reason, why?

Question

When writing MS Excel VBA code I met a simple question, like this.

The upper one has no problem at all.

The lower one has a problem. It gave me "False".

Dim my01, my02
Set my01 = Sheets(1)
Set my02 = Sheets(1)
MsgBox my01 Is my02

Set my01 = Sheets(1).Range("A1")
Set my02 = Sheets(1).Range("A1")
MsgBox my01 Is my02

I expected that it shows "True".

[EDIT] I have tried "=" operator and found that it works well. Though, it does not give any light into my question.

@BigBen thanks, Yes I have saw it too, some hours ago. And.. I guessed that "the same instance of an object" means in my case, "Set my** = Sheets(1).Range("BulahBulah")". So, it ("Is") has to to emit "True".. Right ? — 오바마, Aug 10 '23 at 17:12
@오바마 - you've used `Range` twice, so you've got two different object references. — BigBen, Aug 10 '23 at 17:13
so.. You are saying, the exact point when the "Instanciation" is not "Set" command but "Range" command. OK.. Have to think about it. Thanks Lot. — 오바마, Aug 10 '23 at 17:19
One option might be to check that the parent of both is the same, and then check that their addresses are the same. — BigBen, Aug 10 '23 at 17:20
If you change each of your `MsgBox` lines to `Debug.Print my01 Is my02, ObjPtr(my01), ObjPtr(my02)` then you will see the object references for the Worksheets are the same but for the Ranges they are not hence the result from `Is` ... — JohnM, Aug 10 '23 at 17:22
@BigBen that sounds strange. Yes the parent(s, both of them are same) and their address are same again. So, that means Could I expect to have "True" ? or not ? — 오바마, Aug 10 '23 at 17:27
Yes, you'll get `True`. See the last line of my edited answer (may need to refresh the page). Note that you're using `Is` at the sheet level (no problem), and `=` for the addresses (again, no problem). — BigBen, Aug 10 '23 at 17:28
Sheets(1) (and all others) have existing references as the codename ie Sheet1 so you are effectively doing `Set my01 = Sheet1 : Set my02 = Sheet1` — CDP1802, Aug 10 '23 at 17:38

BigBen · Accepted Answer · 2023-08-11T10:15:55.847

6

See here:

The Range property always returns a new object reference even if you are referring to the same cell. (well, it will re-use previous references if they are not currently pointed to by a variable) - Rory, Feb 12, 2015 at 17:01

This can easily be verified using ObjPtr:

Dim my01, my02
Set my01 = Sheets(1)
Set my02 = Sheets(1)
    
Debug.Print ObjPtr(my01), ObjPtr(my02) ' Identical, e.g. 1134792280    1134792280 
Debug.Print my01 Is my02 ' True
    
Set my01 = Sheet1.Range("A1")
Set my02 = Sheet1.Range("A1")
    
Debug.Print ObjPtr(my01), ObjPtr(my02) ' Different!, e.g. 1091015848    1091023048 
Debug.Print my01 Is my02 ' False

The linked thread proposes comparing their .Address. You could take it one step further and also compare their .Parent or .Worksheet.

Debug.Print my01.Worksheet Is my02.Worksheet And my01.Address = my02.Address ' True

Short cut solution by using the external-parameter

Debug.print my01.Address(external:=True) = my02.Address(external:=True)

edited Aug 11 '23 at 10:15

answered Aug 10 '23 at 17:11

BigBen

46,229
7
24
40

1

Thanks, I have learned here one thing that "Range" property is quite difficult to use. Your explanations are perfect. Nice !!! – 오바마 Aug 10 '23 at 17:35
2

@오바마 - yes `Range` has a lot of intricacies. We haven't even touched on [default member](https://stackoverflow.com/a/32997154/9245853)! – BigBen Aug 10 '23 at 17:36
No need to compare worksheets, with `external:=True` the address will be distinct per each sheet and workbook: `Debug.print my01.Address(external:=True) = my02.Address(external:=True)` – Ike Aug 10 '23 at 17:42
It does work with unsaved workbook - that's where I tested it. :-) – Ike Aug 10 '23 at 17:58

"Is" operator gives me False, Can I know the reason, why?

1 Answers1

Linked