How to easily create an Excel UDF with VSTO Add-in project

Question

What I am trying to do is to create User Defined Functions (UDFs) for Excel using VSTO’s C# “Excel 2007 Add-in”-project type (since I just want to generate some general UDFs). As I am only trying to learn the basics (at this stage anyhow) this is what my code looks like:

using System;
using System.Collections.Generic;
using System.Text;
using System.Xml.Linq;
using Excel = Microsoft.Office.Interop.Excel;
using Office = Microsoft.Office.Core;
using Microsoft.Office.Tools.Excel;
using Microsoft.Office.Tools.Excel.Extensions;
using System.Runtime.InteropServices;

namespace ExcelAddIn1
{
    public partial class ThisAddIn
    {
        private void ThisAddIn_Startup(object sender, System.EventArgs e)
        {}

        private void ThisAddIn_Shutdown(object sender, System.EventArgs e)
        {}

        //My UDF
        public static double HeronicCal(int a, int b, int c)
        {
            //first compute S = (a+b+c)/2
            double S = (a + b + c) / 2;    
            double area = Math.Sqrt(S * (S - a) * (S - b) * (S - c));
            return area;
        }

        #region VSTO generated code

        /// <summary>
        /// Required method for Designer support - do not modify
        /// the contents of this method with the code editor.
        /// </summary>
        private void InternalStartup()
        {
            this.Startup += new System.EventHandler(ThisAddIn_Startup);
            this.Shutdown += new System.EventHandler(ThisAddIn_Shutdown);
        }            
        #endregion
    }
}

It compiles fine, and when I run it, Excel pops up with a fresh spreadsheet, and when I look at the “Add-Ins”-list (in Excel options) I can see my add-in on the list (which is set to “Load at Startup). But here comes my problem, when I try to call my UDF from with-in Excel, Excel can’t find the method!

What I would imagine is wrong, is that I have to tag my method as an Excel UDF (using the square brackets – as for instance done when coding webservices -> “[WebService]”). But I haven’t been able to track down this tag (and since I am not sure at all if my hunch is correct), which is why I’ve decided to go to you fine people here at SO.

So my question basically is – from where I am with my code is there any easy way to make my UDF accessible to Excel? If yes, how?

I really would like to stay within the VSTO project-types (Add-In, Workbook, Template), since my overall goal for my current project is to establish whether or not C# UDF execution with VS2010/Excel2007 works at an acceptable speed. To test this I am working on Windows7RC and with the VS2010 beta1.

Answer 1

VSTO has no support for creating Excel UDFs. Automation Add-Ins can be created in .Net, and seem to be the Microsoft approved way of doing it.

You should have a look at ExcelDna - http://www.codeplex.com/exceldna. ExcelDna allows managed assemblies to expose user-defined functions (UDFs) and macros to Excel through the native .xll interface. The project is open-source and freely allows commercial use. And you'll find that the performance of your .Net-based UDF is similar to native .xll add-ins for Excel. Excel 2007 features like the large sheet, long Unicode strings and multi-threaded recalculation are supported.

With ExcelDna your function as posted above will be exposed to Excel with no VSTO - you can put to code into an xml-based .dna file or compile it to a .dll.

The .dna file exposing your UDF would look like this:

<DnaLibrary Language="C#">
   using System;
   using ExcelDna.Integration;

   public class MyFunctions
   {
      [ExcelFunction(Description="Calculate Stuff", Category="Cool Functions")]
      public static double HeronicCal(int a, int b, int c)
      {
         //first compute S = (a+b+c)/2
         double S = (a + b + c) / 2;
         double area = Math.Sqrt(S * (S - a) * (S - b) * (S - c));
         return area;        
      }
   }
</DnaLibrary>

Update: These days, the easiest way to get started with Excel-DNA is to make a new Class Library project in Visual Studio, then add the 'ExcelDna.AddIn' package from NuGet. That makes a starter add-in - just paste your code and press F5 to run.