Generating a .NET DLL for use with VBA

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

  1. Develop a .NET COM-visible DLL.  Note that you don’t need to use REGASM to register it for this process.
    1. COM visibility is needed to interop with Office products.  If the DLL isn’t COM-visible, VBA can’t “see” its objects and methods
  2. Add the UnmanagedExports NuGet to your project
  3. Add a static method to your class to instantiate your COM object
  4. 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();
      }
    }
    
  5. Compile the DLL (NOTE: must be in x86 or x64 mode; Any CPU will not work with UnmanagedExports!)
  6. Generate a type library (tlbexp)
  7. Generate an IDL file by opening the TLB file with the OLE/COM Object Viewer (File->View TypeLib…)
    1. Note the OLE/COM object viewer (oleview.exe) comes with the Windows SDK, so it must be installed
  8. Export the type library to a .IDL file (Save As…)
  9. 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
    ...
    };
    
  10. 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
    
  11. Create a new resource file (e.g. MyVBALibrary.rc), and add a single line:
    1 typelib "MyVBALibrary.tlb"
    
  12. Move the .tlb file from the subdirectory into your current working directory (overwriting the old tlb file)
  13. Compile the .rc file:
    rc MyVBALibrary.rc
    
  14. This should create a new .res file, e.g. MyVBALibrary.res
  15. 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
  16. 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.

References

How To Make C DLL More Accessible to VB with a Type Library

Unmanaged Exports