In order to fully complete this tutorial, you will need:
This tutorial explains how to make a function in a C# class callable from VBA code using COM. The tutorial begins with writing the C# class code to compiling the DLL, creating a TLB interface file and registering the library in the registry, before referencing the TLB file in the Visual Basic editor. Lastly, the final section shows how to test, in VBA code, that the COM interface is working.
Please note that this tutorial should not be used as a guide for developing COM libraries in C# or for COM itself; instead readers are advised to consult relevant MSDN documentation to learn more about things not covered in this tutorial.
COM, or Component Object Model, is an ABI developed by Microsoft in which modules can interact with a module which implements specialised COM interfaces. The definitions of the interfaces are usually stored in a seperate file, and may be stored in the registry by their respective class Ids. Writing COM libraries can be complex in C or C++ code, and the developer has to compile the library with a seperate interface definition file; fortunately, the Microsoft .NET framework allows you to generate COM accessible interfaces for classes, by prefixing them with the "ComVisible" attribute.
As shown in this tutorial, we will be adding a reference to a TLB file which will contain a COM interface for our compiled class.
Firstly, in a text editor of your choice, we will write a class which will contain a method that takes a string argument and returns a string. Type the following code and save it as ‘cslibrary.cs’:
Each of these steps may be combined into a batch script, but for the sake of simplicity, each command shall be run consecutively.
First of all, generate a strong key for the assembly with the following command:
Next, in the Visual Studio command prompt, navigate to the directory where you saved ‘cslibrary.cs’ and compile the DLL:
After successfully compiling the DLL file, we will next export a TLB file for ‘cslibrary.dll’ and register the COM interface in the registry, which will both be completed by the assembly registration tool (requires administrative rights):
If all of the commands produce no error messages, you may continue to use the newly created COM interface in VBA code.
Firstly, open up the Visual Basic editor, add a new module and then add a reference to the TLB file you created earlier by opening the ‘References’ dialog (from the tools menu):
In the new module file, type the following code:
Now, go back to your spreadsheet and type
=SayHelloTo("name") in a cell.