我怎么知道`ThisWorkbook`是`Workbook`? [英] How do I know that `ThisWorkbook` is a `Workbook`?

问题描述

我正在使用VBIDE API，并且不能假定宿主应用程序是Excel或任何Office应用程序.

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

所以我所知道的是，我正在查看VBComponent，并且它的Type是vbext_ct_document.

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

在VBE的立即窗格中，我可以得到以下输出:

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

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

但是Sheet1对象仅存在于运行时环境中，因此，如果我是C#加载项，我什至看不到它.

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

唯一可以实现我需要的地方就是通过组件的Parent和Next属性:

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

这使我得到了我想要的类型名称...但是在错误的组件上！对于顶级文档对象ThisWorkbook，我将Application对象作为Parent:

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

该方法可能是有用的，但是仅当我对特定于主机的逻辑进行硬编码时，该逻辑知道哪个组件都具有"Application"类型的"Parent"属性是宿主应用程序为Excel时的Workbook实例...并且无法保证其他宿主中的其他文档模块甚至都具有"Parent"属性，因此我很困惑.

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.

我愿意接受一切-从p/调用调用和低级COM反射"魔术(ITypeInfo类魔术)到...到...我不知道-带有时髦指针的unsafe代码将需要// here be dragons注释-欢迎任何可能导致有效解决方案的 lead .

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 VBE加载项与主机的运行过程相同，因此某处有一个指向ThisWorkbook和Sheet1以及VBA中任何其他文档类型VBComponent的指针项目.

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.

推荐答案

不幸的是，vbComponent Properties集合的值/对象只是CoClass实例值的反映，因此它们在所有VBA主机上都不可靠.例如，您不能知道 Parent属性将存在于Properties集合中.

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.

当主机支持文档类型组件时，由主机决定文档所支持的接口的GUID.主机通常还负责创建/删除实际文档，就像只有Excel对象模型可以将工作表添加到工作簿中一样，而VBIDE不能.

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

不幸的是，VBIDE隐藏了一些有关文档类型组件的细节，并且在导出模块时故意忽略了这些细节，甚至将导出的文档类型模块转换为类模块文本，例如称为Worksheet >，这样它不能重新导入为文档类型的模块:

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

将以上内容与Sheet1模块中实际存储(以压缩格式)的文档模块文本进行比较:

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

请注意 real 模块文本中存在的3个其他属性:

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

根据OleViewer，GUID 0 {00020820-0000-0000-C000-000000000046}与CoClass Worksheet完全匹配:

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;
};

工作簿模块也会发生相同的行为.这是VBIDE导出的文本:

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

以及来自VBA二进制文件中IStream的原始文本:

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

这次，正如预期的那样，GUID 0{00020819-0000-0000-C000-000000000046}是一个工作簿CoClass:

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;
};

上面的所有知识都很清楚，但是它不能解决您的问题，除非您可以获取这些组件的内存中IStream的句柄，但我认为您无法做到.如果您可以从上次保存的宿主文档版本中加载详细信息，那么可以从基础文档中加载详细信息，但我认为您不希望这样做，和最终是特定于主机的(考虑Access在表中存储VBA的方式.)

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

但是，VBIDE 确实为您提供了有关CoClass的线索. vbComponent的属性集合返回CoClass中存在的 exact 个属性，如果您检查这些属性的名称，参数和类型，您会发现它们 exactly 匹配相应CoClass的成员，直到它们在CoClass定义中出现的顺序.

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.

例如，工作表vbComponent的前10个属性是:

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

Application
Creator
Parent
CodeName
_CodeName
Index
Name
Next
OnDoubleClick
OnSheetActivate

以及来自CoClass Worksheet(已删除方法)中dispinterface _Worksheet的相应propget(和propput)条目:

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();

如果您可以对主机类型库的CoClasses进行反思并对属性 names 进行哈希处理(也许只使用属性名称)，则可以将哈希与VBIDE组件中的名称进行比较.集合.

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.

这是获取类型的一种方法，但是如果没有访问IStream的权限，我 这将是您唯一的方法.

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.

这篇关于我怎么知道`ThisWorkbook`是`Workbook`?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！