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

Question

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

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

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

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

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

Answer 1

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.

EDIT: I just tried this with the mscorlib.tlb and mscoree.tlb that are in C:windowsMicrosoft.NETFrameworkv2.0.50727-- loading an assembly compiled in 3.5-- and it worked just fine. So apparently you don't need .NET 4.0.

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

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

C:windowsMicrosoft.NETFrameworkv4.0.30319mscorlib.tlb
C:windowsMicrosoft.NETFrameworkv4.0.30319mscoree.tlb

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

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

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't need 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 COMVisible attribute).

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

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.

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.

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