How do I know that `ThisWorkbook` is a `Workbook`?

I'm working with the VBIDE API, and can't assume that the host application is Excel, or any Office app either.

So all I know is that I'm looking at a VBComponent, and that its Type is vbext_ct_document.

In the VBE's immediate pane I can get this output:

?TypeName(Application.VBE.ActiveVBProject.VBComponents("Sheet1"))
VBComponent
?TypeName(Sheet1)
Worksheet

But the Sheet1 object only exists in the runtime environment, so if I'm a C# add-in I don't even see it.

The only thing that gets anywhere close to what I need, is via the Parent and Next properties of the component:

?TypeName(Application.VBE.ActiveVBProject.VBComponents("Sheet1").Properties("Parent").Object)
Workbook
?TypeName(Application.VBE.ActiveVBProject.VBComponents("Sheet1").Properties("Next").Object)
Worksheet

This gets me the type names I'm after... but on the wrong component! And for ThisWorkbook, which is the top-level document object, I get the Application object as the Parent:

?TypeName(Application.VBE.ActiveVBProject.VBComponents("ThisWorkbook").Properties("Parent").Object)
Application

The approach is potentially useful, but only if I hard-code host-specific logic that knows that whichever component has a "Parent" property of type "Application" is a Workbook instance when the host application is Excel... and there's no guarantee that other document modules in other hosts will even have that "Parent" property, so I'm pretty much stumped.

I'm open to literally anything - from p/invoke calls and low-level COM "reflection" magic (the ITypeInfo kind of magic) to ... to... I don't know - unsafe code with funky pointers that will require // here be dragons comments - any lead that can potentially end up a working solution is welcome.

AFAIK the VBE add-in lives in the same process as the host, so somewhere there's a pointer to ThisWorkbook and Sheet1 and whatever other document-type VBComponent in the VBA project.

?ObjPtr(ThisWorkbook)
 161150920

I think I just need to grab that pointer somehow and I'll be where I need to be.

Unfortunately the values/objects of the vbComponent Properties collection are only a reflection of the CoClass's instance values, so they're not reliable across all VBA hosts. For example, you can't know that the Parent property will exist in the Properties collection.

When a host supports document-type-components, it is up to the host to define the GUID of the Interface that the document is supporting. The host will usually also be responsible for creating/removing the actual document, just as only the Excel object model can add a sheet to a workbook, while VBIDE cannot.

You've talked about Workbooks and Worksheets, so I'll include both....

Unfortunately, VBIDE conceals some of the details about document-type components, and it deliberately omits these details when exporting a module, and even converts the exported document-type module into class module text, like this Worksheet called Sheet1, so that it can't be reimported as a document-type module:

VERSION 1.0 CLASS
BEGIN
  MultiUse = -1  'True
END
Attribute VB_Name = "Sheet1"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = True
Sub Foo()

End Sub

Compare the above, to the document-module text that is actually stored (in compressed format) inside the Sheet1 module:

Attribute VB_Name = "Sheet1"
Attribute VB_Base = "0{00020820-0000-0000-C000-000000000046}"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = True
Attribute VB_TemplateDerived = False
Attribute VB_Customizable = True
Sub Foo()

End Sub

Note the 3 additional attributes that exist in the real module text:

Attribute VB_Base = "0{00020820-0000-0000-C000-000000000046}"
Attribute VB_TemplateDerived = False
Attribute VB_Customizable = True

The GUID 0{00020820-0000-0000-C000-000000000046} is an exact match for CoClass Worksheet, as per OleViewer:

[
  uuid(00020820-0000-0000-C000-000000000046),
  helpcontext(0x0002a410)
]
coclass Worksheet {
    [default] interface _Worksheet;
    [default, source] dispinterface DocEvents;
};

The same behaviour occurs with the Workbook module. Here's the VBIDE exported text:

VERSION 1.0 CLASS
BEGIN
  MultiUse = -1  'True
END
Attribute VB_Name = "ThisWorkbook"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = True

And the raw text from the IStream in the VBA binary:

Attribute VB_Name = "ThisWorkbook"
Attribute VB_Base = "0{00020819-0000-0000-C000-000000000046}"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = True
Attribute VB_TemplateDerived = False
Attribute VB_Customizable = True

This time, as expected, the GUID 0{00020819-0000-0000-C000-000000000046} is a Workbook CoClass:

[
  uuid(00020819-0000-0000-C000-000000000046),
  helpcontext(0x000305b8)
]
coclass Workbook {
    [default] interface _Workbook;
    [default, source] dispinterface WorkbookEvents;
};

The above is all good to know, but it doesn't solve your problem, unless you can get a handle to the in-memory IStreams for the components, which I don't think you can. If you can make do with loading the details from the last saved version of the host document, then you could load the details from the underlying document, but I don't think you want that, and it could end up being host-specific (consider the way that Access stores VBA in a table.)

However, the VBIDE does give you a clue about the CoClass. The properties collection for the vbComponent returns the exact number of properties that exist in the CoClass, and if you inspect the names, parameters and types of those properties, you'll find that they exactly match the members of the corresponding CoClass, right down to the order in which they occur in the CoClass defintion.

For example, the first 10 properties of a Worksheet vbComponent are:

Application
Creator
Parent
CodeName
_CodeName
Index
Name
Next
OnDoubleClick
OnSheetActivate

And the corresponding propget (and propput) entries from the dispinterface _Worksheet within CoClass Worksheet (with methods removed):

    [id(0x00000094), propget, helpcontext(0x0002a411)]
    Application* Application();
    [id(0x00000095), propget, helpcontext(0x0002a412)]
    XlCreator Creator();
    [id(0x00000096), propget, helpcontext(0x0002a413)]
    IDispatch* Parent();
    [id(0x0000055d), propget, helpcontext(0x0002a7fc)]
    BSTR CodeName();
    [id(0x80010000), propget, helpcontext(0x0002a7fd)]
    BSTR _CodeName();
    [id(0x80010000), propput, helpcontext(0x0002a7fd)]
    void _CodeName([in] BSTR rhs);
    [id(0x000001e6), propget, helpcontext(0x0002a7fe)]
    long Index();
    [id(0x0000006e), propget, helpcontext(0x0002a800)]
    BSTR Name();
    [id(0x0000006e), propput, helpcontext(0x0002a800)]
    void Name([in] BSTR rhs);
    [id(0x000001f6), propget, helpcontext(0x0002a801)]
    IDispatch* Next();
    [id(0x00000274), propget, hidden, helpcontext(0x0002a802)]
    BSTR OnDoubleClick();
    [id(0x00000274), propput, hidden, helpcontext(0x0002a802)]
    void OnDoubleClick([in] BSTR rhs);
    [id(0x00000407), propget, hidden, helpcontext(0x0002a803)]
    BSTR OnSheetActivate();

If you can reflect over the host Type Library's CoClasses and hash the property names (maybe just use the propget names), then you can compare the hash to that of the names in VBIDE's component.Properties collection.

It's a round-about way of getting the type, but without access to the IStream, I think it's going to be your only way.

Recommended topics

Hot tags