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?

Fun fact: In Office 2016, when I first ran this, the `ActiveSheet.Test` executed fine, but its `MsgBox` did not show. I replaced the MsgBox with `Debug.? "Test"` and it printed, then I put back the MsgBox and it did not show even though the method was clearly called. It only started showing when did Debug > Compile VBAProject. — GSerg, Apr 08 '16 at 10:23

score 4 · Accepted Answer · edited May 23 '17 at 11:45

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

Ok, I understand. So two objects are the "same" if their `ObjPtr` are identical, and `IS` is a more general equivalence, though the [VBA Language Reference](https://msdn.microsoft.com/en-us/library/office/gg264676.aspx) talks about "same". I didn't know that casting can change the object identity and certainly didn't expect `Control.Context.ActiveSheet` to return some strange wrapped object that needs to be cast twice to be useful again. Thank you for your answer. — Martin Roller, Apr 08 '16 at 14:11
@MartinRoller I'm not sure exactly what is going on at this moment and I'm planning to figure it out, so it's not the time to accept the answer. The `Is` operator is supposed to [query `IUnknown` pointers and compare them for equality](http://stackoverflow.com/a/23403849/11683). The `IUnknown` pointers returned by `ActiveSheet` and `Ws` are indeed equal, so they are the "same instance" as far as COM is concerned. Either there is some funny interface trickery going on, or the developers of `Control.Context` are breaking some COM rules. — GSerg, Apr 08 '16 at 14:17
You say The `IUnknown` pointers returned are equal. Does that mean you assign the objects to a variable of type `IUnknown` and then compare their `ObjPtr`s? — Martin Roller, Apr 08 '16 at 14:31
@MartinRoller Yes, that's exactly what I did, and they were equal. — GSerg, Apr 08 '16 at 14:34
It gets weirder. If I define `Dim Ws as IUnknown: Set Ws = Control.Context.ActiveSheet`, then `Ws.Test`gets a Compile error "Method unkown", but `CallByName Ws, "Test", VbMethod` actually calls the method. Casting `Control.Context.ActiveSheet` to `IUknown` allows me to call the method at Runtime but not at Compiletime, casting it to `Object` allows me to call it at Compiletime but not at Runtime. I don't understand COM, but from the VBA language level I'm not getting a clue. — Martin Roller, Apr 08 '16 at 14:43
That part is not weird. To call a method by name you need to use the object's `IDispatch` interface. That is the interface VB's `As Object` uses. If all you have is `IUnknown`, the basic building block of COM, you cannot call anything by name against that. It's a fixed interface with [just three methods](https://msdn.microsoft.com/en-us/library/windows/desktop/ms680509%28v=vs.85%29.aspx), which is why you get a compile-time error on calling `IUnknown.Test`. When you use `CallByName`, first thing it does is querying `IDispatch` from the object you passed, and then it calls "Test" on that. — GSerg, Apr 08 '16 at 14:52
Update: The issue is fully reproducible without any ribbons. Declare two `Object`s, assign one with `Application.ActiveSheet` and the other one with `ActiveWindow.ActiveSheet`. The former is the good one and the latter is the bad one. They pretend to be the same object even though they are very far away in Excel memory. The bad one appears to fail to include user-defined methods in its dispatch table. The tool I was using describes them both as identical `_Worksheet` interfaces, which means it's either a bug in the tool or a bug in Office (two interfaces with same IID). — GSerg, Apr 08 '16 at 19:54
Moreover, `ObjPtr(Control.Context.ActiveSheet) = ObjPtr(ActiveWindow.ActiveSheet)` — Martin Roller, Apr 08 '16 at 22:17
You say: `ActiveWindow.ActiveSheet` returns `IDispatch` that is a part of `Worksheet`, but not `Sheet1`, and that's the reason why the runtime call to `Test` fails. But why does it help to assign `ActiveWindow.ActiveSheet` to a variable of type `Worksheet`first and then to a variable of type `Object`? — Martin Roller, Apr 11 '16 at 16:58
Because these assignments make VBA issue `QueryInterface` calls. VBA knows it needs an `IDispatch` interface, and it will ask for it whenever what it has is not an explicit `IDispatch`. The return type of `ActiveWindow.ActiveSheet` is `IDispatch`, the variable you store it in is also `IDispatch`, so when `ActiveWindow.ActiveSheet` returns, VBA simply stores the pointer without asking for anything else, because it asked for `IDispatch` and it got an `IDispatch` — GSerg, Apr 11 '16 at 17:19
If VBA anyway explicitly asked for `IDispatch` on the returned pointer, it would correctly get back a `Sheet1` - but it does not ask. Casting back and forth is nothing more than the easiest way to trick VBA in calling `QueryInterface(IID_IDispatch)`. — GSerg, Apr 11 '16 at 17:19

When are two objects the same in VBA?

1 Answers1

Linked