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

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

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:\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)

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

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 api to load your manifest file.

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

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.

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.