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

Question

First of I apologize beforehand if this question seems rather “noobish” – but I am entirely new to VSTO development and to StackOverflow, so please bear with me. Thanks :-)

What I am trying to do is to create UDF’s for Excel using VSTO’s C# “Excel 2007 Add-in”-project type (since I just want to generate some general UDF’s). 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.

I hope my question is understandable, and that you are able to help me :-)

Kind regards

andershh

Answer 1

As far as I know, you cannot directly create UDFs in VSTO.

See Paul Stubbs' article How to create Excel UDFs in VSTO managed code where he uses a VBA add-in to expose VBA UDFs, which in turn call his Managed UDFs written in VSTO.

You can use managed code to create UDFs, however, when not using VSTO. See Eric Carter's article Writing user defined functions for Excel in .NET on how to do this.

As for VSTO's execution speed, I think you'll find it fine for virtually all tasks. Looping through cells, however, which is already Excel's weak-point, might be painfully slow, depending on what you are doing. Try to execute things in batch, as much as possible. For example, instead of looping through the cells one by one, return a two dimensional array of values from an area, process the array, and then pass it back to the range.

To demonstrate, the following will return a two dimensional array of values from an area, process the values, and then pass the resulting array back to the original area in one shot:

Excel.Range rng = myWorksheet.get_Range("A1:D4", Type.Missing);

//Get a 2D Array of values from the range in one shot:
object[,] myArray = (object[,])rng.get_Value(Type.Missing);

// Process 'myArray' however you want here.
// Note that the Array returned from Excel is base 1, not base 0.
// To be safe, use GetLowerBound() and GetUpperBound:
for (int row = myArray.GetLowerBound(0); row <= myArray.GetUpperBound(0); row++)
{
    for (int column = myArray.GetLowerBound(1); column <= myArray.GetUpperBound(1); column++)
    {
        if (myArray[row, column] is double)
        {
            myArray[row, column] = (double)myArray[row, column] * 2;
        }
    }
}

// Pass back the results in one shot:
rng.set_Value(Type.Missing, myArray);

Hope this helps!

Mike

Answer 2

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>