Writing a COM Library in C#

Prerequisites

In order to fully complete this tutorial, you will need:

  • A C# Compiler
  • Microsoft Excel
  • Administrative rights
  • A Windows Host

Introduction

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.

A Brief Introduction to COM

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.

COM Interface Acessibility in VBA

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.

Writing the C# Code

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’:

using System; 
using System.Runtime.InteropServices; 
using System.Runtime.CompilerServices; 
using System.Reflection; 
namespace CSLibrary 
{ 
	public interface ILibrary { string sayHelloTo(String name); } 
	[ComVisible(true)] 
	[ClassInterface(ClassInterfaceType.None)] 
	public class Library : ILibrary 
	{ 
		[ComVisible(true)] 
		public string sayHelloTo(string name) { return "Hello " + name; } 
	} 
}

Compiling the DLL and registering a type library

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:

sn –k CSLibrary.snk


Next, in the Visual Studio command prompt, navigate to the directory where you saved ‘cslibrary.cs’ and compile the DLL:

csc /out:cslibrary.dll /target:library /keyfile:CSLibrary.snk cslibrary.cs


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

regasm cslibrary.dll /codebase /tlb:cslib.tlb

If all of the commands produce no error messages, you may continue to use the newly created COM interface in VBA code.

Testing the COM Library

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

1. 2.

In the new module file, type the following code:

Now, go back to your spreadsheet and type =SayHelloTo("name") in a cell.