My First C#.Net UDF Using Excel-DNA And Visual Studio

Having recently blogged about the importance of broadening my programming horizon, I felt it was high time I put together a step-by-step guide on how to create an Excel UDF in Visual Studio using Excel-DNA. In this post I’m specifically targeting Excel VBA developers with little or no experience of .Net so following the steps below should be easy and will cost you nothing more than your time.

First of all, you need to download and install Visual Studio. Visual Studio 2015 Community can be downloaded for free from MSDN. During the installation choose Visual C# as the default language when prompted.

Next launch Visual Studio and go to File > New > Project   (or press CTRL+SHIFT+N). Choose a new Visual C# Class Library project and give it the name/solution name MyFirstUDF, like so:

And then click on OK. Visual Studio will think for a few seconds and create a new solution for you.

Next, go to the View menu at the top > Other Windows > Package Manager Console and the Package Manager Console pane should appear:

Next to the PM> prompt in the package manager console, type in Install-Package Excel-DNA and press ENTER.

A bunch of text will appear in the package manager console and hopefully you’ll see lots of successful and complete messages.

You should now also see some extra stuff in the solution explorer window:

The project’s ready now, so we can finally do some coding!

In the solution explorer, select Class1.cs. Visual Studio should open a tab for you which looks just like this:

Don’t be frightened by the curly brackets { }. I know they’re alien to us VBA’ers but all they do is mark the beginning and end of code blocks in C#.

At the top of the module, add this extra using statement:

using ExcelDna.Integration;

C# is case-sensitive so it really needs to be exactly as above and as shown on this screenshot:

If you see any small, squiggly red lines then you’ve done something wrong.

Next, copy the below code and overwrite the Class1 section.

    public static class Class1
    {
        [ExcelFunction(Name = "TOMORROW")]
        public static DateTime Tomorrow()
        {
            return DateTime.Today.AddDays(1);
        }
    }

Your code window should now look exactly like this:

Take particular note that the public static class Class1 line now has the static keyword. That’s really important: without it you won’t see the function in Excel. I don’t want to delve too much into C# specifics but, in this context, just think of a static class in the same way as you do a standard code module in VBA: you don’t have to create an instance of the class before using its members.

The line in square brackets [ ] is an attribute. It tells Excel-DNA that the function underneath, Tomorrow(), needs to be exposed in Excel as worksheet function called TOMORROW. The rest of the code defines a function called Tomorrow() which returns a DateTime object representing tomorrow’s date.

************************************************************

This next step only applies if you have 64-bit Excel installed. If you have 32-bit Excel then you can go straight on to testing the project.

 In the solution explorer, right-click on the MyFirstUDF project and choose Properties.

This will open up a tab showing the project properties.

On the left-hand side select Debug and then, in the Command line arguments box, change "MyFirstUDF-AddIn.xll" to "MyFirstUDF-AddIn64.xll".

This change means that the 64-bit version XLL will be loaded in Excel when you debug the application. If you don’t do this then you will get the below error because 32-bit binaries can’t load in 64-bit Excel:

************************************************************

We’re now ready to test our UDF. In the commandbar section at the top of Visual Studio you should have two dropdown controls next to a green triangle:

Make sure that Debug is selected and then click on the green triangle to run the project. Typically when you debug a C# Class library a dll file is created, but Excel-DNA does some magic and creates an xll file (actually several xll files). At this point Excel should be launched and, depending on your security settings, you may be asked if the MyFirstUDF add-in should be enabled. If you get the prompt then you need to choose Enable.

The TOMORROW() function should be available for use in the Excel session. Navigate to an empty cell and put it in as a formula. Hopefully you should get tomorrow’s date as the return value:

Excel’s TODAY() function is volatile so let’s put the icing on the cake and make our TOMORROW() function volatile too. In Visual Studio click on the red stop icon to stop debugging and the Excel session should close:

Making the function volatile is really easy; we just have to add an IsVolatile property (set to true) to the ExcelFunction attribute:

[ExcelFunction(IsVolatile = true, Name = "TOMORROW")]

It’s really important to emphasise again that C# is case-sensitive: the true must be all lower case (in VBA the "t" would be upper case). Here’s how your code should look:

Test it again using the green start icon (or by pressing F5).

At this point all that’s left for me to write is congratulations: you’ve now entered the magical world of C#, Excel-DNA and XLLs. I know that, in itself, TOMORROW() is a pretty useless UDF – but it’s a great starting point to learning this technology. There may seem to be a lot of steps to get to this point but with a little practice it only takes a minute.