This process will allow you to generate a single DLL that contains an embedded type library as well as a COM-exposed .NET library, and be used in Tools->References in VBA. It can be used without registering the COM object with REGASM.
I may not find the time to add detail to this, but I am putting it here as a reminder for myself. I implemented this as a proof-of-concept, but haven’t done anything with it yet. If anyone viewing this needs help with it, feel free to leave a comment with Disqus below.
Process Overview
- Develop a .NET COM-visible DLL. Note that you don’t need to use REGASM to register it for this process.
- COM visibility is needed to interop with Office products. If the DLL isn’t COM-visible, VBA can’t “see” its objects and methods
- Add the UnmanagedExports NuGet to your project
- Add a static method to your class to instantiate your COM object
- Decorate the static method with the [DllExport] attribute (from the UnmanagedExports library)
e.g.... using RGiesecke.DllExport; using System.Runtime.InteropServices; ... [Guid("{AAAAA...AAA}")] [ClassInterface(ClassInterfaceType.None)] public class MyCOMObject : IMyCOMObject { ... //COM Implementation ... [DllExport("CreateMyCOMObject", CallingConvention = CallingConvention.StdCall)] public static IMyCOMObject CreateMyCOMObject() { return new MyCOMObject(); } }
- Compile the DLL (NOTE: must be in x86 or x64 mode; Any CPU will not work with UnmanagedExports!)
- Generate a type library (tlbexp)
- Generate an IDL file by opening the TLB file with the OLE/COM Object Viewer (File->View TypeLib…)
- Note the OLE/COM object viewer (oleview.exe) comes with the Windows SDK, so it must be installed
- Export the type library to a .IDL file (Save As…)
- Edit the IDL file to add a module section, before the closing bracket in the library definition:
e.g.... library MyVBALibrary { ... //Your new entry starts here [dllname("MyVBALibrary.dll")] module MyLibrary { [ helpstring("Static method that instantiates a new instance of your COM object"), entry("CreateMyCOMObject") ] IMyCOMObjectsInterface __stdcall CreateMyCOMObject(); }; //Your entry ends here ... };
- Compile the IDL file into a new tlb file (note this is different than the TLB file already generated!!). Make sure you put it into a subfolder, because the midl tool won’t overwrite the original .tlb.
midl /output .\outputsubfolder\ MyVBALibrary.idl
- Create a new resource file (e.g. MyVBALibrary.rc), and add a single line:
1 typelib "MyVBALibrary.tlb"
- Move the .tlb file from the subdirectory into your current working directory (overwriting the old tlb file)
- Compile the .rc file:
rc MyVBALibrary.rc
- This should create a new .res file, e.g. MyVBALibrary.res
- Assuming your project is a C# project; go to the project properties->Applicaton tab, and under “Resources,” click the “Resource File.” radio button. Click the ellipsis (…), and select the MyVBALibrary.res file you generated in the previous step
- Recompile the project
Now, this dll can be used in your VBA project by adding a reference to it in Tools->References. To instantiate a new .NET object from your COM class, use the CreateMyCOMObject() function you created.
Troubleshooting
The DLL must be in the path that VBA is searching for, or you will get a “File Not Found” exception. You may have to use “ChDrive” and “ChDir” in your VBA code to find the DLL, as it will look in its current folder.