Interfacing C# and VBA with Excel-DNA (with intellisense support)

In my previous posting, I was presenting how to create C# COM Server class and use that class in VBA without intellisense support. If this topic is completely new for you, I suggest that you check out first that posting before starting to work with this one. This posting will present a bit more complex way to do the same thing, but also including full intellisense support for your COM server class to be used in VBA.

PROJECT RESULT

The end result of this small example project will be a C# COM Server class, which can be used in VBA with full intellisense support. For this project, I have been using Visual Studio 2010 Express with Framework 4.0.

PREPARATORY TASKS

Download and unzip Excel-DNA Version 0.30 zip file to be ready when needed. There is also a step-by-step word documentation file available within the distribution folder. In this project, we are going to follow these instructions.

STEP ONE: C# program

Create a new class project and name it to be XLServer. Also, change the class name to be XLServer. CopyPaste the following code into your XLServer.cs and save the project. At this point, do not mind about all those error messages. As we add reference to ExcelDnaIntegration.dll file, those errors will disappear.

using System;
using ExcelDna.Integration;
using ExcelDna.ComInterop;
using System.Runtime.InteropServices;
//
namespace XLServer
{
    [ComVisible(true)]
    [ClassInterface(ClassInterfaceType.AutoDual)]
    public class COMLibrary
    {
        public double add(double x, double y)
        {
            return x + y;
        }
    }
    //
    [ComVisible(false)]
    class ExcelAddin : IExcelAddIn
    {
        public void AutoOpen()
        {
            ComServer.DllRegisterServer();
        }
        public void AutoClose()
        {
            ComServer.DllUnregisterServer();
        }
    }
}

By adding class ExcelAddin (implements IExcelAddin interface), we are enabling the registration and unregistration of our dll file to be done programmatically, without the use of regsvr32.exe from command prompt.

Note also, that ClassInterfaceType attribute for our public COMLibrary class has been set to be AutoDual. This means, that a dual class interface is generated for this class and also exposed to COM. Class type information is produced for the class interface and published in type library file. As a part of this posting, we will create this type library file.

STEP TWO: Excel-DNA

Add reference to Excel-DNA library (Project - Add reference - Browse - \\ExcelDna.Integration.dll) and click OK. This dll file is inside the distribution folder what we just downloaded from Excel-DNA website. From the properties of this reference, set Copy Local to be True.

Add new file as text file to project (Project - Add new item - Text file) and name it to be XLServer.dna. CopyPaste the following xml code into this file.

<DnaLibrary Name="XLServer" RuntimeVersion="v4.0">
  <ExternalLibrary Path="XLServer.dll" ComServer="true" />
</DnaLibrary>

From the properties of this dna file, set Copy to Output Directory to be Copy if newer.

Next, from the downloaded Excel-DNA folder (Distribution), copy ExcelDna.xll file into your project folder (\\Projects\XLServer\XLServer) and rename it to be XLServer.xll. Then, add this xll file into your current project (Project - Add existing item). At this point, it might be that you do not see anything else, except cs files on this window. From drop down box on the bottom right corner of this window, select All files and you should see XLServer.xll file what we just pasted into this XLServer folder. Select this file and press Add. Finally, from the properties of this xll file, set Copy to Output Directory to be Copy if newer.

Build the solution. Everything should have gone well, without any errors or warnings. At this point, my \\XLServer\bin\Release folder looks like the following.

At this point, we are practically done with C#.

You may also notice, that ExcelDna.Integration.dll file has been copied into our release folder. This has not been an accident. To enable intellisense to be used, we need to create type library file (tlb) for our COM Server class by using Type library exporter in Visual Studio Command Prompt. Type library exporter (tlbexp.exe) needs to have that dll file in order to find all program parts it needs when creating type library file.

STEP THREE: Creating Type Library

Next, we have to open our VS command prompt (Start - All Programs - Microsoft Visual Studio 2010 Express - Visual Studio Command Prompt 2010). Before opening, move your cursor over the name (Visual Studio Command Prompt 2010) and by right-clicking, select and open properties window. On properties, replace the existing string in Start in text box with the complete address of our current project release folder (\\VS2010\Projects\XLServer\XLServer\bin\Release).

Press Apply and OK. By setting this start path string, we are effectively opening VS command prompt directly in our project release folder. Next, open VS command prompt and write the command "tlbexp XLServer.dll" in order to create type library into project release folder.

Press enter and the following information will confirm that type library file (XLServer.tlb) has been created into current project release folder.

At this point, we can close our Visual Studio command prompt. Let us take a look at our release folder. My folder has the following files.

We can see, that XLServer.tlb file has been successfully created.

STEP FOUR: VBA

Open a new Excel workbook, open VBA editor and create reference to COM class (Tools - References - Browse).

Select XLServer.tlb (from our release folder) and press Open. Insert a new standard VBA module and copyPaste the following code.

Option Explicit
'
Sub tester()
    '
    Dim lib As New COMLibrary
    Debug.Print lib.Add(12, 13)
    Set lib = Nothing
End Sub
'

If we run this small program, it prints the value 25 into editor immediate window. This confirms, that our COM Server is working correctly. Moreover, we have full intellisense support for COM Server class methods now available. At this point, we are done.

Early-binding scheme

In our example VBA program above, we are creating instance of our COM Server class by creating an object of type COMLibrary (C# class name) without using VBA CreateObject function. This scheme is called early-binding. The biggest advantage is intellisense support. Another advantage of this scheme is fast execution time (no compiler run-time checkings made). The first downside of this scheme is complex implementation compared to Late-binding scheme. Even bigger downside comes with the issues, concerning version handling safety.

STEP FIVE: Version handling issues - a fair warning

Let us assume, that we have done everything successfully and our COM Server is working well. Now, we would like to implement a new subtract method to our COM Server class. Let us open our project and implement this method. The content of our new XLServer.cs is given below.

using System;
using ExcelDna.Integration;
using ExcelDna.ComInterop;
using System.Runtime.InteropServices;
//
namespace XLServer
{
    [ComVisible(true)]
    [ClassInterface(ClassInterfaceType.AutoDual)]
    public class COMLibrary
    {
        public double add(double x, double y)
        {
            return x + y;
        }
        public double subtract(double x, double y)
        {
            return x - y;
        }
    }
    //
    [ComVisible(false)]
    [ClassInterface(ClassInterfaceType.None)]
    class ExcelAddin : IExcelAddIn
    {
        public void AutoOpen()
        {
            ComServer.DllRegisterServer();
        }
        public void AutoClose()
        {
            ComServer.DllUnregisterServer();
        }
    }
}

Build the project and save results. Now, by using Excel what we created before for testing purposes, let us open its VBA editor again and try to run the program (using add method). Remember to doubleClick XLServer.xll file in your \\XLServer\bin\Release folder after opening your Excel. Now, when running that VBA program, compiler will throw run-time error 430 (Class does not support Automation or does not support expected interface).

If you check your VBA code, you can confirm that intellisense is still alive and showing all class methods for our previous version (before changes). To be able to fix this error, we need to re-create our Type Library file again. After I created my type library file again, my intellisense was showing all methods of current version.

Just for the curious, I made exactly the same changes for late-binding scheme presented in previous posting. It turned out, that after those changes, VBA was able to use both class methods (add, subtract) without any further changes or modifications. Moreover, we did not need to create any type library files either.

Conclusions: using Early-binding scheme with COM Server class is providing intellisense support and fast execution time (without run-time compiler checkings), but whenever there will be any changes or modifications made in COM Server class source, type library file needs to be re-created. Late-binding scheme does not provide intellisense support and execution time is a bit slower (run-time compiler checkings), but whenever there will be any changes or modifications made in COM Server class source, no further modifications are needed to get VBA program working.

Now, interesting question is, that exactly how much slower (due to those run-time compiler checkings) late-bound version really is, compared to early-bound version and does it really matter on our program? If this difference is not substantial, it is hard to find any reason to implement COM Server class as early-bound object. It is nice to have intellisense support, but as we see, it comes with the price. Anyway, more information on this particular topic and also possible solution for version handling issues can be found from here and from here.

Final notes

For learning more things about Excel-DNA, check out its homepage. Getting more information and examples with your problems, the main source is Excel-DNA google group. For those who would like to see useful examples using Excel-DNA in financial programs, there is an excellent book C# for Financial Markets (chapter 22) written by Daniel Duffy and Andrea Germani (published 2013). Finally, Excel-DNA is an open-source project, and we (the happy users) can invest its future development by making a donation.

Thanks for reading again and good luck!

-Mike