Excel analysts and developers who create useful functions often wish to share their work with coworkers and others. For many applications, creating an Excel macro workbook (xlam file) is sufficient. But for some code, the need for more power requires the development of libraries of compiled code. There are several techniques for accomplishing this.
The creation of a dynamic link library (dll) using VB6 is one of the oldest method, and even today remains a viable technique. The new millenium, however, saw the dawn of the .Net Framework which addressed many of the shortcomings of the last millenium’s programming tools. Unfortunately, even in 2015 the native interface that Excel uses to communicate with libraries of compiled code remains Microsoft’s Component Object Model (COM). While there are some third party tools that can load C# code directly into Excel, including the excellent Excel DNA project, we will examine how to use the Visual Studio to create a C# code library that communicates with Excel using the classic and still standard COM interface.
First and foremost, we must note that a COM interface is completely native code; today we call such ode unmanaged to contrast it with managed .NET code. This immediately places a serious constraint on the developer. Since a purely .NET dll can be compiled at runtime for either a 32-bit or a 64-bit architecture, a .NET dll with a COM interface can target only one platform or the other. Only a 32-bit dll can be loaded into 32-bit Excel, and only a 64-bit dll can be loaded into 64-bit Excel. If you are running 32-bit Excel on a 64-bit operating system, it is only the bitness of Excel that matters; you must use a 32-bit library. The bottom line is that, if you are supporting a number of different users, you will likely have to create two compiled dlls. Both can be created from the same source code, but to achieve successful installation for everyone you must maintain both a 32-bit and a 64-bit version of your compiled dll.
To illustrate the key aspects of creating a COM dll in the Visual Studio, we will create a very simple project with a simple method that returns random numbers following a Gaussian distribution. We are not conerned with the algorigthm here, only with the Visual Studio techniques required for turning our C# code into a method that can be called from Excel. We’ll create a new project called ”MathStuff” and a public class called ”RandomStuff”. Whether you let the compiler supply it, or whether you create it in code explicitly, any public class that you intend to use via COM must have a default constructor.
In the Visual Studio, the easiest way to manage two different dlls as possible output is by using the Configuration Manager. In this example, we define two new configurations, named Debug32 and Debug64. After making Debug32 the active configuration we go to the ”Build” tab of the project properties and select x86 from the ”Platform Target” dropdown list. Then we make the Debug64 configuration the active configuration and set the Platform Target to x64.
32-bit COM servers do not need to be signed. In contrast, 64-bit COM servers must be signed. You must therefore create a key and sign your assembly. If you create both 32- and 64-bit version of your COM server, they will both be signed.
Every time you create a class library project in the Visual Studio, a guid will be generated even if the library is not intended to be used via COM. If you do wish to access the library via COM, as we do in this example, the easiest way to add a COM interface to our library is to edit the AssemblyInfo.cs file, identify the default ComVisible(false) attribute, and change the false to true. This method will result in all public classes being accessible via COM. If you do not want this, you will need to specify indiviual attributes for appropriate classes.
// The following GUID is for the ID of the typelib if this project is exposed to COM
There seems to be some confusion about the checkbox in the project properties labelled ”Register for COM Interop”. Selecting this checkbox is not necessary for COM to work. However, if we choose not to select it, our library will be usable only via late binding. This means that in Excel we would be able to create an object using the CreateObject() function. The object variable would have to be declared as type ”Object”, and there would be no way for Excel to check method calls in code before the code is actually run. If we do seelct the ”Register for COM Interop” checkbox the Visual Studio will create during compilation a type library file along with the dll. This type library has the extension ”tlb”. This type library can be read by Excel to provide support for early binding. We will explicitly see the difference between late and early binding when our library is finished and the time has come to use it in Excel VBA.
Once we have successfully compiled our code, we copy the library to the target machine, being careful to copy the 32-bit version to machines running 32-bit Excel and similarly copying the 64-bit library for 64-bit machines.
Once the files have been installed on the target machine, the dll must be registsered as a COM server. For a classic COM server written in VB6 or C++, this is done with the appropriate regsvr32.exe (Yes, Virgina, there is a 64-bit regsvr32). However, this tool will not work with a .NET library wrapped for COM. In your windows system folder there will be a folder ”Microsoft.NET”. Within that folder there you will either look for ”Framework” of ”Framework64” depending on which bitness you are targeting. Within the appropriate Framework folder, open the folder for your most recent version of .NET. This folder will contain the desired utility, called RegAsm.exe. Be aware that there are two RegAsm.exe files, one within the Framework folders and one within the Framework64 folders. They are not the same.
Once you have found the RegAsm file you need, you can register your COM server. A copy of a batch file to register an assembly is included with the download. Of course, you will have to modify it for your file locations. Whether you run a batch files, or simply type the commands at the prompt, you will need to do this with adminitrator privilege.
After your assembly is registered, it is ready to be run, but only with late binding. It is virtually always better to use early binding when practical. If we are going to use our COM library in Excel VBA macro code we must reference our library to use early binding. To do this, we click the browse button in the Add Reference dialog box and navigate our way to the tlb file that the Viual Studio has created for us.
We are now in a position to use our server to create normally distributed random numbers in an Excel macro.
If we are satisfied to use late binding, we are ready to go. If we wish to use early binding we must go to the Tools | References menu choice in the Excel VBA editor and add a reference to our new COM library. The very first time you will need to choose Browse and navigate to the tlb file created for you by the Visual Studio. After that, the name of your library will appear with all the other libraries installed on your system.
Using the library in VBA code is no different from using any other library. If we merely wish to test a function, we can write the output to the VBA editor’s immediate window using Debug.Print. If we want to use a library function to provide a value for a worksheet cell, we create a VBA function that calls the library function.
Briefly, the steps we followed were these:
1) Create a library project in the Visual Studio
2) Ensure that each class to be accessed via COM has a default constructor
3) Set the ComVisible attribute to true in the AssembyInfo.cs file
4) Create two project configurations, one for 32-bit and one for 64-bit
5) Set the Platform target to x86 and x64 for the corresponding configurations
6) Sign the assembly (if you are including a 64-bit configuration)
7) Check Register for COM interop if you wish to use early binding
8) Register the compiled assembly using the correct RegAsm.exe using administrative privilege
9) Add an Excel VBA reference if you wish to use early binding
There are lots of details that demand your attention when you are creating a COM server. A bit of care in the organization of the Visual Studio project will likely result in a successful and functional library.