与 C# 和 VBA 的并行 COM 互操作

Side-By-Side COM Interop with C# and VBA

提问by tordal

I'm not talking about calling a VBA COM from C#... the other way around!

我不是在谈论从 C# 调用 VBA COM ......相反！

What I would like to do is call a C# library using VBA in MS Access without registering the DLL. I've been playing around with side-by-side interop for a while without success and it has finally occurred to me that a mdb.manifest is probably not an acceptable replacement for an exe.manifest (probably obvious, I know, but I was trying to be optimistic).

我想做的是在 MS Access 中使用 VBA 调用 C# 库，而无需注册 DLL。我已经玩了一段时间的并行互操作，但没有成功，我终于意识到 mdb.manifest 可能不是 exe.manifest 的可接受替代品（可能很明显，我知道，但我试图保持乐观）。

My question: Is it possible to get VBA to load a side-by-side COM component?

我的问题：是否可以让 VBA 加载并排 COM 组件？

Or, is there another way to use an unregistered C# library in Access?

或者，是否有另一种方法可以在 Access 中使用未注册的 C# 库？

(Before you ask, my reasons are: there is absolutely no way I will be granted access to my client's Windows registry -- that's why it was written in Access in the first place. And, I will need to implement the same functionality in a C# application soon and rather not do it twice).

（在你问之前，我的理由是：我绝对没有办法被授予访问我客户的 Windows 注册表的权限——这就是为什么它首先是用 Access 编写的。而且，我需要在一个C# 应用程序很快，而不是做两次）。

采纳答案by Alex Shnayder

You don't have to own the exe to use SxS, SxS is another word for Activation Context. If you can import the relevant win32 calls into vba (and you can), then you can use the activation context apito load your manifest file.

您不必拥有 exe 即可使用 SxS，SxS 是Activation Context 的另一种说法。如果您可以将相关的 win32 调用导入到 vba（并且可以），那么您可以使用激活上下文 api来加载您的清单文件。

More on the subject and some examples can be found here.

可以在此处找到有关该主题的更多信息和一些示例。

回答by transistor1

To add to the already existing answers: with .NET 4.0, it's actually quite simple to consume a C# dll in your VBA project without registering the COM.

添加到已经存在的答案中：使用.NET 4.0，在 VBA 项目中使用 C# dll 实际上非常简单，而无需注册 COM。

EDIT: I just tried this with the mscorlib.tlband mscoree.tlbthat are in C:\windows\Microsoft.NET\Framework\v2.0.50727-- loading an assembly compiled in 3.5-- and it worked just fine. So apparently you don't need .NET 4.0.

编辑：我刚刚尝试了这个mscorlib.tlb和mscoree.tlb那个C:\windows\Microsoft.NET\Framework\v2.0.50727- 加载一个在 3.5 中编译的程序集 - 它工作得很好。所以显然你不需要.NET 4.0。

The below is an example of how to use a C# dll in your VBA project. It is slightly modified from thisanswer.

下面是如何在 VBA 项目中使用 C# dll 的示例。从这个答案稍微修改了一下。

1) Add references to the following type libs your VBA project (Tools->References):

1）添加对以下类型库的引用您的VBA项目（工具->参考）：

C:\windows\Microsoft.NET\Framework\v4.0.30319\mscorlib.tlb
C:\windows\Microsoft.NET\Framework\v4.0.30319\mscoree.tlb

(use Framework64 folder if you are running 64-bit Office)

（如果您运行的是 64 位 Office，请使用 Framework64 文件夹）

2) In your C# project, make sure you add the [ComVisible(true)]attribute to your class:

2) 在您的 C# 项目中，确保将[ComVisible(true)]属性添加到您的类中：

using System.Windows.Forms;
using System.Runtime.InteropServices;
namespace VB6FuncLib
{
    [ComVisible(true)]
    public class VB6FuncLib
    {
        public VB6FuncLib()
        { }
        public void test()
        {
            MessageBox.Show("Test Successful");
        }
    }
}

You don'tneed to check the option "Register for COM Interop". That's only for building a standard COM object. You don't have to check "Make Assembly COM Visible" either, unless you want the whole assembly to be visible (that would also eliminate the need for the COMVisibleattribute).

你并不需要检查选项“注册为COM Interop”。这仅用于构建标准 COM 对象。您也不必选中“使程序集 COM 可见”，除非您希望整个程序集可见（这也将消除对COMVisible属性的需要）。

3) In your VBA code, add a new module with this code:

3) 在您的 VBA 代码中，使用以下代码添加一个新模块：

Sub Test()
    Dim Host As mscoree.CorRuntimeHost
    Set Host = New CorRuntimeHost
    Host.Start
    Dim Unk As IUnknown
    Host.GetDefaultDomain Unk
    Dim AppDomain As AppDomain
    Set AppDomain = Unk
    Dim ObjHandle As ObjectHandle
    Set FS = CreateObject("Scripting.FileSystemObject")
    Path = FS.GetParentFolderName(CurrentDb().Name)
    Set ObjHandle = AppDomain.CreateInstanceFrom(Path & "\VB6 Function Library.dll", "VB6FuncLib.VB6FuncLib")
    Dim ObjInstance As Object
    Set ObjInstance = ObjHandle.Unwrap
    ObjInstance.test
    Host.Stop
End Sub

4) Copy the DLL into the same folder as your Office project and run the Test() sub in VBA.

4) 将 DLL 复制到与 Office 项目相同的文件夹中，然后在 VBA 中运行 Test() 子程序。

Notes:

笔记：

It should be noted that one of the limitations of this technique is that it won't work if the .DLL is stored on a remote network share. One simple solution would be to copy it into the same local folder on each PC where it is being used. Another solution would be to include the binaries in your Access app/VBA project, and have MS-Access export them. One way that could be accomplished would be by storing them in Base64 in a table or spreadsheet, then converting them and exporting them as binary.

应该注意的是，这种技术的局限性之一是，如果 .DLL 存储在远程网络共享上，它将无法工作。一个简单的解决方案是将其复制到每台使用它的 PC 上的同一本地文件夹中。另一种解决方案是将二进制文件包含在您的 Access 应用程序/VBA 项目中，并让 MS-Access 导出它们。可以实现的一种方法是将它们存储在 Base64 中的表格或电子表格中，然后转换它们并将它们导出为二进制文件。

I was able to get early binding (and therefore Microsoft IntelliSense) to work by creating a type library to go with the DLL (by using tlbexp), and adding a reference to the TLB in my VBA project, but it does complicate matters a bit because it requires your VBA app to know where both the DLL and the TLB files are (and also requires someone to make sure they are there).

通过创建与 DLL 一起使用的类型库（通过使用 tlbexp），并在我的 VBA 项目中添加对 TLB 的引用，我能够使早期绑定（因此 Microsoft IntelliSense）工作，但它确实使问题复杂化了一点因为它需要您的 VBA 应用程序知道 DLL 和 TLB 文件的位置（并且还需要有人确保它们在那里）。

回答by nitzmahone

The problem is that to use SxS, you need to own the exe to set up the config to load the SxS assembly. You don't "own" Access, and while you could drop the right config in to cause it to load your .NET COM stuff sans registration, it wouldn't be a "good citizen" move.

问题是要使用 SxS，您需要自己的 exe 来设置配置以加载 SxS 程序集。您不“拥有”Access，虽然您可以放入正确的配置以使其在没有注册的情况下加载您的 .NET COM 内容，但这不会是一个“好公民”的举动。

If you get tricky with shimming, you can set up an unmanaged DLL (or a hacked C# class library with a dllexport, see this, for example) with an export that will load the .NET framework, create an instance of a COMVisible DispInterface managed type and return it (the method should return IDispatch). Then write a VBA declare to your DLL export function (declared as returning Object). If this doesn't make sense, you probably oughtn't try it... :) I've done this before in a similar situation, and it does work, but I don't have a sample to point you at.

如果您对填充很棘手，您可以设置一个非托管的 DLL（或一个带有 dllexport 的被黑的 C# 类库，例如，参见这个），并带有一个将加载 .NET 框架的导出，创建一个托管的 COMVisible DispInterface 实例键入并返回它（该方法应返回 IDispatch）。然后将 VBA 声明写入您的 DLL 导出函数（声明为返回对象）。如果这没有意义，你可能不应该尝试它... :) 我之前在类似的情况下做过这个，它确实有效，但我没有样本可以指给你。

回答by Ioan

C# libraries are not regular DLLs. They're more similar to COM libraries which need to be registered (just like ActiveX controls) before being used; especially when called from non-.NET code.

C# 库不是常规的 DLL。它们更类似于 COM 库，在使用之前需要注册（就像 ActiveX 控件一样）；特别是从非 .NET 代码调用时。

(Unless, of course, things have changed...)

（当然，除非事情发生了变化……）