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

Some readers might be aware, that there used to be a posting under this name before. However, after getting some comments and advices directly from Govert Van Drimmelen (inventor, developer and author of Excel-DNA), and further studying the issue just a bit more, I soon realized that there is lot more than meets the eye, when dealing with COM Server classes.

Even my original implementation was technically working correctly, it was potentially dangerous way to implement this scheme. Finally, I made a decision to re-implement and re-write the whole thing again. Now, I have divided everything to two separate postings.

• This first posting presents an easy way to create interface between C# and VBA, but having no intellisense support for your COM server class used in VBA.
• The second posting will present "not so easy" way to do the same thing and having full intellisense support for your COM server class 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 without 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 ExcelInterface. Also, change the class name to be InterfaceFunctions. CopyPaste the following code into your InterfaceFunctions.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 ExcelInterface
{
    [ComVisible(true)]
    [ClassInterface(ClassInterfaceType.AutoDispatch)]
    [ProgId("CSharp_functionLibrary")]
    public class InterfaceFunctions
    {
        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();
        }
    }
}

A couple of notes about the class implementations. Note the use attributes and enumeration in our InterfaceFunctions class. ProgIDs are automatically generated for a class by combining the namespace with the type name. However, with ProgID attribute we can set our own ProgId to be used, when calling class from VBA. With ComVisible attribute, we can control the class visibility to COM. With ClassInterfaceType enumeration we can set the type of class interface that is generated for a class. It might be worth of checking those MSDN links if this topic is completely new. Finally, 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.

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 False.

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

<DnaLibrary Name="ExcelInterface" RuntimeVersion="v4.0">
  <ExternalLibrary Path="ExcelInterface.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\ExcelInterface\ExcelInterface) and rename it to be ExcelInterface .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 ExcelInterface.xll file what we just pasted into this ExcelInterface 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 \\ExcelInterface\bin\Release folder looks like the following.

At this point, we are done with C#.

STEP THREE: VBA

Open a new Excel workbook. While this workbook is open, doubleClick ExcelInterface.xll file in your \\ExcelInterface\bin\Release folder. After this, our C# function (add) is available to be used in VBA. Next, open VBA editor, insert a new standard module and copyPaste the following program.

Option Explicit
'
Sub tester()
    '
    Dim lib As Object: Set lib = CreateObject("CSharp_functionLibrary")
    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. The only thing really missing is intellisense support for COM Server class methods. If you are able to live without it, then congratulations - we are done.

LATE-BINDING SCHEME

In our example VBA program above, we are creating instance of our COM Server class by creating an object of type "CSharp_functionLibrary" (set with our custom ProgID attribute) by using VBA CreateObject function. In the case we would not be using ProgID attribute, we would create an object of type "NamespaceName.ClassName".

This scheme is called late-binding. Lacking intellisense support is one downside of this scheme. Another downside is a bit slower execution time, due to some run-time checkings made by compiler. This issue might or might not be relevant for your program. Anyway, even there is no intellisense support available, we have an access to all COM-visible methods just by calling those with the correct syntax. If the syntax is not correct, compiler throws an error. Big advantage of this scheme is, that it is relatively easy to implement. Even bigger advantage comes with the issues concerning version handling safety. This advantage will be explicit after you check out my next posting (intellisense support).

If you are unable to live without intellisense support, things are getting a bit more complicated. I try to open up and present how to add such intellisense support to be used in VBA in the next posting.

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