当前位置：文江博客话题详情

将 VSTO 功能公开给 VBA，无需本地管理

发布于 2024-07-06 02:55:49 字数 252 浏览 9 评论 0原文

将 Dotnet VSTO Excel 加载项中的某些功能公开到 VBA 的最佳方法是什么，而不要求用户成为本地管理员（即无需 COM 注册，没有 HttpListener）？是否可以通过 VBA 使用 Microsoft 消息队列？

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

つ低調成傷 2024-07-13 02:55:49

如果我可以将您的问题解释为“如何在不注册 COM 的情况下将 .Net 程序集中的功能公开给 Excel”，那么一个很好的解决方案是使用 Excel 的 XLL 接口。
基本上，部署一个 xll shim 和一个关联的 .Net dll。加载 xll 时，它会反映 dll 并将其中的函数公开给 Excel。

可以在此处找到开源实现 http://exceldna.typepad.com/ blog/2006/01/introducing_exc.html

这是一个商业的、封闭源代码的，但功能更丰富的一个
http://www.managementxll.com/

回复收藏 0 原文

离不开的别离 2024-07-13 02:55:49

您不能简单地将它们实例化为 COM 对象，因为 VSTO 将不会在默认应用程序域中运行。

这是我的做法，诚然有点复杂。这是将 VSTO 工作簿另存为 XLA 文件，在某些方面比纯 VSTO 外接程序更灵活。

您需要使用 regasm.exe 生成将由您的 VBA 代码引用的类型库。
在 .NET 对象模型中创建一个根工厂类，它能够实例化您想要在 VBA 中使用的任何类（类似于 Office 对象模型中的“Application”类）。
然后您需要找到一种方法将此工厂类实例的引用传递给 VBA。一旦 VBA 拥有对此工厂类实例的引用，它就可以调用其方法来实例化 .NET 对象模型中的任何其他对象。
要将实例传递给 VBA，请在 VBA 代码中定义一个宏，如下所示

示例代码：

Private m_objMyFactory As Object

Public Sub RegisterFactory(MyFactory As Object)

    On Error GoTo ErrHandler
    Set m_objMyFactory = MyFactory
    Exit Sub
ErrHandler:
    MsgBox "An unexpected error occurred when registering the Factory component: " & Err.Description
    Exit Sub
End Sub

现在将代码添加到 VSTO ThisWorkbook_Open 事件handler，它实例化您的工厂对象并调用上面的宏，传递对工厂对象的引用。

示例代码：

void ThisWorkbook_Open()
{
     try
     {
         ThisApplication.Run("RegisterFactory",
             new MyNamespace.MyFactory(),
             Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
             Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
             Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
             Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
             Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
             Type.Missing, Type.Missing, Type.Missing, Type.Missing);
     }
     catch (Exception ex)
     {
         MessageBox.Show("Load error: " + ex.ToString());
     }
}

为了使其稳健地工作，还有一些问题需要考虑 - 如果您有兴趣跟进此问题，请告诉我，我将发布更多详细信息。

You can't simply instantiate them as COM objects, as VSTO will not be running in the default application domain.

Here is how I've done it, which is admittedly a bit convoluted. This was with a VSTO workbook saved as an XLA file, which in some ways is more flexible than a pure VSTO add-in.

You need to generate a type library using regasm.exe that will be referenced by your VBA code.
Create a root factory class in your .NET object model, which is capable of instantiating any of the classes you want to consume in VBA (something like the "Application" class in the Office object models).
You then need to find a way to pass a reference to an instance of this factory class to VBA. Once VBA has a reference to an instance of this factory class, it can call its methods to instantiate any other objects in your .NET object model.
To pass an instance to VBA, define a macro in your VBA code as follows

Example code:

Private m_objMyFactory As Object

Public Sub RegisterFactory(MyFactory As Object)

    On Error GoTo ErrHandler
    Set m_objMyFactory = MyFactory
    Exit Sub
ErrHandler:
    MsgBox "An unexpected error occurred when registering the Factory component: " & Err.Description
    Exit Sub
End Sub

Now add code to the VSTO ThisWorkbook_Open event handler, which instantiates your factory object and calls the above macro passing a reference to the factory object.

Example code:

void ThisWorkbook_Open()
{
     try
     {
         ThisApplication.Run("RegisterFactory",
             new MyNamespace.MyFactory(),
             Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
             Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
             Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
             Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
             Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
             Type.Missing, Type.Missing, Type.Missing, Type.Missing);
     }
     catch (Exception ex)
     {
         MessageBox.Show("Load error: " + ex.ToString());
     }
}

There are a few more issues to consider to get this working robustly - if you're interested in following this up let me know and I'll post more details.

回复收藏 0 原文