identity - When are two objects the same in VBA?

Question

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>

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

The active sheet has a method

Public Sub Test()
    MsgBox "Test"
End Sub

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.

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?

Answer 1

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

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

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.

Now to the objects in question.

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.

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.)

---

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".

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".

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.

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.

---

Now the actual question, Why cannot Test() be called on ActiveWindow.ActiveSheet.

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

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 is a dynamic interface that inherits from Worksheet and contains your Test(). It is a superset of 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 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?

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.

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

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 returns IDispatch that is a part of Sheet1, a different interface. This time Test() succeeds.

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.

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.

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