英文:
"Is" operator gives me False, Can I know the reason, why?
问题
以下是翻译好的部分：
在编写MS Excel VBA代码时，我遇到了一个简单的问题，就像这样。
上面的没有任何问题。
下面的有一个问题。
它给了我“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
我期望它显示“True”。
[编辑]
我尝试了“=”运算符，并发现它运行良好。尽管如此，它对我的问题没有提供任何帮助。
英文:
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.
答案1
得分: 6
请看这里：
> Range 属性总是返回一个新的对象引用，即使您引用的是同一个单元格。（如果它们当前没有被一个变量指向，它会重用先前的引用）- Rory，2015年2月12日，17:01
这可以通过使用 ObjPtr 轻松验证：
Dim my01, my02
Set my01 = Sheets(1)
Set my02 = Sheets(1)
Debug.Print ObjPtr(my01), ObjPtr(my02) ' 相同，例如 1134792280 1134792280
Debug.Print my01 Is my02 ' True
Set my01 = Sheet1.Range("A1")
Set my02 = Sheet1.Range("A1")
Debug.Print ObjPtr(my01), ObjPtr(my02) ' 不同！例如 1091015848 1091023048
Debug.Print my01 Is my02 ' False
链接的帖子建议比较它们的 .Address。您还可以进一步比较它们的 .Parent 或 .Worksheet。
Debug.Print my01.Worksheet Is my02.Worksheet And my01.Address = my02.Address ' True
通过使用 external 参数的快捷解决方案：
Debug.print my01.Address(external:=True) = my02.Address(external:=True)
英文:
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)
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论