Skip to main content

Excel User Defined Functions with C# and ExcelDna

Adding user defined functions to Excel can be laborious, either you use VBA or COM. Why can't Microsoft make it easier to use Visual Studio and .NET within Excel?

Enter ExcelDna.

This example creates a static function in C# using Visual Studio. There is documentation that already describes this, what I am adding is:

  • Use Visual Studio to create project
  • Post Build Events to help create the final XLL file.

NOTE: I hate Post Build Events. The ONLY exception is in this case where I am modifying the output of the final build. Post Build Events are Evil especially when used in a large development team. A better solutions is to create actual Build Scripts that do what I am about to do. You have been warned.

First, create a new Class Library project. Use NuGet to add a reference to the Excel-DNA package. NuGet will also add ExcelDna.dna and ExcelDna.xll to your project. Rename them both to the name that you want your final output xll to be. In my case I renamed them to Scratch-ExcelDna.dna and Scratch-ExcelDna.xll. Also for both files change to properties for Build Action to "Content" and Copy to Output Directory to "Copy Always".

Within the packages folder created by NuGet is the ExcelDnaPack utility, in the tools folder. This will package your project into one xll file. For it to work you need to update the .dna file:

<DnaLibrary RuntimeVersion="v4.0">
     <ExternalLibrary Path="DC.Scratch.ExcelDna.dll" Pack="true" />

Note that I am using .Net 4.

To get the pack utility to work, add a Pre-build event to delete the existing xll:

del "$(TargetDir)Scratch-ExcelDna-packed.xll"

Then add a Post-build event to recreate it:

"$(SolutionDir)packages\Excel-DNA.0.29\tools\ExcelDnaPack.exe" "$(TargetDir)Scratch-ExcelDna.dna"

While you are in the Project Properties messing with the evil build events set the Debug options so you can test your code. Set the External Program to you MS Excel and add a Command Line argument with a path to your final xll file.

Now, add a class TestFunctions:
using ExcelDna.Integration;

namespace DC.Scratch.ExcelDna
public class TestFunctions
[ExcelFunction(Description = "Product of two numbers", Category = "DNA Test")]
public static double TheProductOf(double x, double y)
return x*y;

Hit F5 and see if Excel Starts. If it does, add some numbers to the Excel spreadsheet and see if the TheProduct() function works.

Download sample project here: DC.Scratch.ExcelDna

Microsoft should really look at buying Excel-DNA and incorporating it into Visual Studio. You Guys Listening!?!


Popular posts from this blog

Json for jqGrid from ASP.Net MVC

jqGrid takes a specific format for its json (taken from jqGrid documentation): [js]{ total: "xxx",page: "yyy", records: "zzz", rows : [ {id:"1", cell:["cell11", "cell12", "cell13"]}, {id:"2", cell:["cell21", "cell22", "cell23"]}, ... ]}[/js] The tags mean the following: total - Total number of Pages. page - Current page Index. records - Total number of records in the rows group. rows - An array with the data plus an identifier. id - The unique row identifier, needs to be an int from what I have found. cell - An array of the data for the grid. The ASP.Net MVC framework has the JsonResult response type which we can use to populate the jqGrid. As an example I created a Person model and a method to return some data: [csharp] public class Person { public int ID { get; set; } public string Name { get; set; } public DateTime Birthday { get; set; } } public I

Changing Typed DataSet Connection String

I was working on a WinForm app that connected to a MS Access database. Yeah, Access sucks but I didn't have a choice in the matter. The app itself is used to import a bunch of CSV files into the Access database. It is more of a utility program and it has going through several variations, from being a simple hand driven command line tool to being GUI driven. The command line version was all hand controlled. I had to go in and update configuration files to point to the CSV files and the MDB database. That got old fast so I decided to make a GUI version that would allow me to pick the MDB file and each of the CSV files to import. Picking and using the CSV files was easy, it was changing the connection string for the MDB that proved to be the hardest. I am using strongly typed datasets in VS2005. If you have ever worked with them you find out soon that the connection string gets saved with the project in the app.config file, even if it is a seperate DAL dll project. My guess is

Remote Controlled RoboTank

This is my version of the ever popular to build RoboTank. It uses an Arduino Mega 2560 with the AdaFruit motor shield and an XBee S1 to communicate to the DFRobot Gamepad. The sketch for the RoboTank makes use of the AFMotor.h to drive the motors and includes a serial parser to read and process the commands coming from the Gamepad. DFRobot Wireless Joystick