什么时候VBA中的两个对象相同? [英] When are two objects the same in VBA?

问题描述

我在Excel 2010中使用一个功能区，其中包含一个按钮:

I use a ribbon in Excel 2010, which contains a single button:

<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui" onLoad="rx_onLoad">
    <ribbon>
        <tabs>
            <tab id="TestTab" label="Test Tab" insertAfterMso="TabHome">
                <group id="TestGroup" label="TestGroup">
                    <button id="TestButton" label="TestButton" size="normal" onAction="OnTestButton"  tag="TestButton" imageMso="Coffee" /> 
                </group>        
            </tab>
        </tabs>
    </ribbon>
</customUI>

OnTestButton方法是在模块中实现的

The method OnTestButton is implemented in a module

Sub OnTestButton(Control As IRibbonControl)

    Dim Ws As Object
    Set Ws = Control.Context.ActiveSheet
    MsgBox ActiveSheet Is Ws  ' Shows True

    Debug.Print ActiveSheet.Name 'OK
    Debug.Print Ws.Name ' OK

    ActiveSheet.Test ' OK
    Debug.Print Ws.Test ' Runtime Error

End Sub

活动工作表具有方法

Public Sub Test()
    MsgBox "Test"
End Sub

如果单击测试按钮，则会调用方法OnTestButton. 在IS运算符下，对象Control.Context.ActiveSheet和ActiveSheet相同.当我在WorkSheet的接口中使用类似Name的属性时，它们的行为相同. 但是，当我调用方法Test时(不在接口中)，我在Control.Context.ActiveSheet而不是ActiveSheet上收到运行时错误458对象不支持此属性或方法".

If I click on the test button, the method OnTestButton is called. The objects Control.Context.ActiveSheet and ActiveSheet are the same under the IS-operator. When I use a property like Name, that is in the interface of WorkSheet, they behave the same. However, when I call the method Test, that is not in the interface, I get runtime error 458 "Object does not support this property or method" on Control.Context.ActiveSheet but not on ActiveSheet.

那么为什么两个引用Control.Context.ActiveSheet和ActiveSheet在应该引用相同"对象时在运行时表现不同?

So why do the two references Control.Context.ActiveSheet and ActiveSheet behave differently at runtime when they are supposed to refer to the "same" object?

推荐答案

我有合理的理由(> 90％)确定以下内容是正确的.

I am reasonably (>90%) sure the following is true enough.

首先回答标题中的问题:两个对象在VBA中何时相同?

First to answer the question from the title: When are two objects the same in VBA?

当COM说它们相同时，两个对象在VBA中是相同的；当您请求IUnknown接口和指针都相等.

Two objects are the same in VBA when COM says they are the same, and COM says they are the same when you request the IUnknown interface from both and the pointers come out equal.

现在有问题的物体.

功能区的Control.Context只不过是Excel的Application.ActiveWindow，因此问题变成了 ActiveWindow.ActiveSheet和Application.ActiveSheet是否相同.

The ribbon's Control.Context is nothing more than Excel's Application.ActiveWindow, so the question becomes, Are ActiveWindow.ActiveSheet and Application.ActiveSheet the same.

是的，他们是-就COM而言.
它们可能不是在内部实现的(单个对象)，因为它们的指针彼此相距很远，但是当您从中请求IUnknown时，它们将返回相同的指针值. (您通过声明类型为IUnknown的变量并Set将对象设置为此类型来请求IUnknown.)

Yes, they are - as far as COM is concerned.
They are probably not internally implemented as a single object because their pointers are very far away from each other, but when you request IUnknown out of them, they return the same pointer value. (You request IUnknown by declaring a variable of type IUnknown and Setting the object to that.)

旁注.
Excel对于一个真实对象"的单个实际"内部"实例具有多个外部"实例"对象是很自然的.

A side note.
It is sort of natural for Excel to have multiple "external" "instances" of objects for a single "actual" "internal" instance of a "true object".

例如您可以创建Range对象的多个实例，所有这些实例都是不同的实例(Is = False)，它们引用实际图纸上完全相同的实际范围.因此，每个人仅是实际事物"的视口".

E.g. you can create several instances of the Range object, all of which will be different instances (Is = False) that refer to the exactly same actual range on an actual sheet. Each one is therefore a mere "viewport" for the "actual thing".

我推测Window和Sheet会发生类似的事情(每个实际的事物可能有多个视口")，但是Excel开发人员是为了避免造成混淆/简化VBA编码，决定让Sheet包装器通过返回相同的IUnknown指针报告它们是同一对象.

I would speculate that a similar thing is happening for Windows and Sheets (there can be multiple "viewports" for each actual thing), but Excel developers, in order to avoid confusion/to ease VBA coding, decided to have Sheets wrappers report that they are the same object by returning the same IUnknown pointer.

就COM而言，这很好:只要遵循所有COM规则，那么在内部将对象实现为多个对象就没有关系，因为只要遵循所有COM规则，就没有反正他们的方式.

And this is fine as far as COM is concerned: as long as all COM rules are followed, it does not matter if the object is internally implemented as several objects, because as long as all COM rules are followed, there is no way to tell them apart anyway.

现在是实际问题，为什么不能在ActiveWindow.ActiveSheet 上调用Test().

因为ActiveWindow.ActiveSheet返回Worksheet接口，该接口没有Test()方法，并且不可扩展.就这么简单.

Because ActiveWindow.ActiveSheet returns the Worksheet interface, which does not have a Test() method and is non-extensible. As simple as that.

那为什么为什么要在Application.ActiveSheet上调用Test()?
因为Application.ActiveSheet不返回Worksheet.它返回Sheet1(或您的工作表名称).

Then why can Test() be called on Application.ActiveSheet?
Because Application.ActiveSheet does not return a Worksheet. It returns Sheet1 (or whatever your sheet is named).

Sheet1是从Worksheet继承并包含您的Test()的动态接口.它是Worksheet的超集.

Sheet1 is a dynamic interface that inherits from Worksheet and contains your Test(). It is a superset of Worksheet.

您可能想知道为什么当用户尝试在Worksheet上调用Test()时，VBA为什么不请求更好的Worksheet父集.答案是，它既不应该也不可以！

You may be wondering why then does VBA not request the better superset of Worksheet when the user tries to call Test() on a Worksheet. The answer is, it neither should, nor can!

VBA应该也不需要了解托管它的应用程序的内部实现细节.它不知道存在一个更好"的接口，可以从当前接口中查询该接口.并且如果它确实怀疑存在一个更好的"接口，那么鉴于每个对象可以具有数百个接口，它将确切地尝试查询哪个接口?

VBA should not and does not need to have knowledge of internal implementation details of the application in which it is hosted. It cannot know that there exists a "better" interface that can be queried from the interface it currently has. And if it did suspect that there was a "better" interface, exactly which one would it try to query, given that each object can have hundreds of interfaces?

VBA唯一需要执行后绑定调用的是IDispatch接口.
所有Excel接口都从IDispatch继承. 也就是说，Excel Is IDispatch中的每个类.

The only thing VBA needs to execute a late-binding call is the IDispatch interface.
All Excel interfaces inherit from IDispatch. That is, each class in Excel Is IDispatch.

类型为As Object的变量也表示为As IDispatch.
在Object变量中设置内容意味着从该对象中查询IDispatch.

Variable of type As Object also means As IDispatch.
Setting something to an Object variable means querying IDispatch out of that object.

ActiveWindow.ActiveSheet返回作为Worksheet一部分的IDispatch.它是有效且完整的IDispatch，存储在类型为IDispatch的变量中，因此无需VBA要求更好的IDispatch" .它使用它已经拥有的一个，调用失败.

ActiveWindow.ActiveSheet returns IDispatch that is a part of Worksheet. It is valid, full-fledged IDispatch stored in a variable of type IDispatch, so there is no need for VBA to ask for a "better IDispatch". It uses the one it already has, and the call fails.

Application.ActiveSheet返回作为另一个接口Sheet1一部分的IDispatch.这次Test()成功.

Application.ActiveSheet returns IDispatch that is a part of Sheet1, a different interface. This time Test() succeeds.

ActiveWindow.ActiveSheet从Worksheet返回IDispatch是否是错误尚有争议.
从技术上讲，这不是错误，因为Worksheet 是 IDispatch，因此该方法有权返回该错误.
但是可以说，返回更好的IDispatch"是我们在Excel中的显示方式，实际上他们应该这样做. 我个人倾向于将其声明为一个小错误.

It is debatable whether or not ActiveWindow.ActiveSheet returning IDispatch from Worksheet is a bug or not.
Technically it's not a bug, because a Worksheet is IDispatch so the method is in its right to return that.
However it can be said that returning the "better IDispatch" is how we roll here in Excel, and really they should have done that.
I am personally inclined to declare it a minor bug.

但是您可以自己请求更好的IDispatch".
如果仅声明另一个Object变量，并在现有工作表中引用该变量，则将不起作用-VBA将观察到两个变量的类型相同，即Object，并且将直接复制指针而不尝试查询另一个界面.

But you can request the "better IDispatch" yourself.
It won't work if you simply declare another Object variable and Set your existing worksheet reference to that - VBA will observe that both variables are of the same type, Object, and will directly copy the pointer not trying to query another interface.

要让VBA实际上请求其他接口，您需要首先查询不同于IDispatch的另一个接口，然后从该接口请求IDispatch:

To have VBA actually ask for another interface, you need first to query another interface, different from IDispatch, and then request IDispatch from that:

Dim BadIDispatch As Object
Set BadIDispatch = Control.Context.ActiveSheet  'ActiveWindow.ActiveSheet

Dim Ws As Worksheet
Set Ws = BadIDispatch  'Querying another interface

Dim WsAsObject As Object
Set WsAsObject = Ws    'Querying IDispatch - this time going to get a good one

这篇关于什么时候VBA中的两个对象相同?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！