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:

firstdnaudf1

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:

firstdnaudf2

firstdnaudf3

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

firstdnaudf4

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:

firstdnaudf5

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:

firstdnaudf6

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:

firstdnaudf7

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:

firstdnaudf8

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.

firstdnaudf9

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

firstdnaudf10

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:

firstdnaudf11

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

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:

firstdnaudf12

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:

firstdnaudf13

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:

firstdnaudf14

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:

firstdnaudf15

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.

Advertisements

About Colin Legg

RAD Developer Microsoft MVP - Excel 2009 - 2014
This entry was posted in Excel-DNA, Microsoft Excel, Visual C# and tagged , , , , . Bookmark the permalink.

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

  1. Jean-Marie De Smet says:

    wow, wel explained. I’ll try this out in a couple of weeks. Thx!

    Like

  2. David Reddy says:

    That is great!
    But one qusetion: when I use TODAY() Excel formats the cell as a date. Can that be done with TOMORROW() ?

    Like

    • Colin Legg says:

      Good question.

      Excel only does the date formatting when you enter the formula, not each time it calculates. So it relates to a “formula entry event” rather than a calculation event. In other words, it’s not the TODAY() function that is applying the formatting.

      From a UDF perspective, the only option I can think of would be to use a worker thread to format the cell if it isn’t in a date format when you return a value.
      It’s not even that easy though – for example, what if the function is embedded in a formula which the user would expect to return a number? Consider what Excel does with TODAY():

      If you put in this formula: =TODAY()-(TODAY()-1) then Excel doesn’t format the cell as a date.
      Furthermore, Excel considers the existing format of the cell when deciding whether or not to apply date formatting – eg. if the cell is already formatted as a number then it won’t change the format to a date.

      I think this one is best left to the user!

      Liked by 1 person

  3. bjoernstiel says:

    Cool stuff. One of the (many) cool things about building Addins with Excel-DNA is that you can do TDD so easily without having to fire up an Excel session. So, in your case…

    using NUnit.Framework;
    using ExcelDna.Integration;
    using MyFirstUDF;

    namespace MyFirstUDF.Test
    {
    class AddinTest
    {
    [Test]
    public void Can_Call_Tomorrow()
    {
    Assert.AreEqual(DateTime.Today.AddDays(1), MyFirstUDF.Tomorrow());
    }
    }
    }

    Which is pretty cool as you can even test for excel specific stuff like #N/A, #VALUE.

    Looking forward to reading more ExcelDNA related stuff here!

    Like

  4. Pingback: My First Custom Ribbon Using Excel-DNA And Visual Studio | RAD Excel

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s