Creating C# Excel Addin with Excel-DNA

In the last posting, I was presenting how to create interface between C# and VBA by using Excel-DNA. As a result, we were able to write a function in C# and use that function inside VBA with full support for intellisense. For the sake of completeness, I will also present how create Excel XLL Addin with Excel-DNA and use C# function in Excel, just like an ordinary Excel worksheet function. So let us start, once again.

PROJECT RESULT

The end result of this small example project will be a simple C# function, which can be used in Excel just like an ordinary Excel worksheet function. 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# function

Create a new C# Class project "FunctionLibrary". Rename created default "Class1.cs" to be "VBAFunction.cs". For the class VBAFunction.cs, copyPaste the following code and replace the existing code with this one.

using System;
//
namespace FunctionLibrary
{
    public static class VBAFunction
    {
        public static double add(double x, double y)
        {
            return x + y;
        }
    }
}

Next, build solution and remember to save this project.

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 FunctionLibrary.dna. CopyPaste the following xml code into this file.

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

At this point, we are done with C# and Excel-DNA.

STEP THREE: Excel

Open a new Excel workbook. While this workbook is open, doubleClick FunctionLibrary.xll file in your \\FunctionLibrary\bin\Release folder. After this, xll file content can be used by Excel and our C# function (add) is available like an ordinary worksheet function in Excel.

Job done. We have now successfully created Excel XLL worksheet function Addin with Excel-DNA.

STEP FOUR: Packing

Even our Addin is now fully operational, we would be facing variable amount of problems with all separate files included in the project, in the case we would like to distribute this for our clients. Frankly speaking, other people usually do not have time or interest for settling any potential issues involved with all these files received. For this purpose, we can pack all our project files into a neat package, which then can be distributed.

In C#, open FunctionLibrary.dna file, and replace its content with the following xml code.

<DnaLibrary Name="FunctionLibrary" RuntimeVersion="v4.0">
  <ExternalLibrary Path="FunctionLibrary.dll" Pack="true"/>
</DnaLibrary>

Now, build the project again. Next, open command prompt and write the following command:

Replace my address strings with your own address strings. In a nutshell, within the strings what I have been writing into command prompt above, there are two addresses joined (a space character between the strings): first address is to ExcelDnaPack.exe (in \\ExcelDna-0.30\Distribution folder) and second address is to FunctionLibrary.dna (in \\FunctionLibrary\bin\Release folder). After writing the strings and pressing enter, the following information appears in command prompt. If you do not see the following information, then something has gone wrong.

This information tells us, that ExcelDnaPack.exe has now successfully created fully distributable and stand-alone Excel XLL Addin. Let us take a look at my project folder \\FunctionLibrary\bin\Release.

We can see that FunctionLibrary-packed.xll file has been created into this folder. Now, this packed xll file can be freely re-named, distributed for your clients and run with no other files required. Both FunctionLibrary.dna and FunctionLibrary.dll are packed inside xll file as resources, and will be loaded at runtime. If you repeat step three (using FunctionLibrary-packed.xll), you will see that this Excel XLL Addin is working as expected.

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.

-Mike