Using C# .NET User Defined Functions (UDF) in Excel

N.B.Throughout this post I am using Excel 2010 and Visual Studio 2010.

Writing a UDF in VBA to be exposed to Excel cells is straight forward, just write the function in a VBA module and Bob’s your uncle. However, it is slightly trickier to expose your functions to Excel in a managed language, such as C# or F#.

Essentially there are two ways to achieve this and for every method there are pros and cons:

1. Automation Add-Ins Method
2. XLL Add-Ins Method

I will demonstrate how to implement each method then I will discuss my verdict. I have created a sample project in each method; you could download it at the end of this post.

Automation Add-Ins Method

Automation Add-ins are COM functions to be called from formulas in Excel worksheets, it is supported since Excel 2002. The idea is that .NET can expose a COM interface that can be consumed from Excel through Automation Add-ins support.

To create your custom functions, you need to create a new C# code library project from Visual Studio, then go to:
Right click Project > Properties > Build > Register for COM Interop and enable it.

Then go to Assembly.cs and set ComVisible to true. Then you need to create a base class that you will inherit later to create your UDF:

using System;
using System.Runtime.InteropServices;
using Microsoft.Win32;

namespace ExcelUdf.Automation
{
public abstract class UdfBase
{
[ComRegisterFunction]
public static void ComRegisterFunction(Type type)
{
    Registry.ClassesRoot.CreateSubKey(
        GetClsIdSubKeyName(type, "Programmable"));

    // Solves an intermittent issue where Excel
    // reports that it cannot find mscoree.dll
    // Register the full path to mscoree.dll.
    var key = Registry.ClassesRoot.OpenSubKey(
        GetClsIdSubKeyName(type, "InprocServer32"), true);
    if (key == null)
    {
        return;
    }
    key.SetValue("", 
        String.Format("{0}\\mscoree.dll", Environment.SystemDirectory), 
        RegistryValueKind.String);
}

[ComUnregisterFunction]
public static void ComUnregisterFunction(Type type)
{
    // Adds the "Programmable" registry key under CLSID
    Registry.ClassesRoot.DeleteSubKey(
        GetClsIdSubKeyName(type, "Programmable"));
}

private static string GetClsIdSubKeyName(Type type, String subKeyName)
{
    return string.Format("CLSID\\{{{0|||\\{1}", 
        type.GUID.ToString().ToUpper(), subKeyName);
}

// Hiding these methods from Excel
[ComVisible(false)]
public override string ToString()
{
    return base.ToString();
}

[ComVisible(false)]
public override bool Equals(object obj)
{
    return base.Equals(obj);
}

[ComVisible(false)]
public override int GetHashCode()
{
    return base.GetHashCode();
}
}
}

Then your UDF class should inherit UdfBase as such:

using System.Runtime.InteropServices;
using ExcelUdf.Automation;

namespace AutomationSample
{
    [ClassInterface(ClassInterfaceType.AutoDual)]
    [Guid("7a9de936-0e99-4d37-9c2b-a02a09fb371f")]
    public class AutomationSample : UdfBase
    {
        public double AutomationSampleAdd(double a, double b)
        {
            return a + b;
        }

        public double AutomationSampleSubtract(double a, double b)
        {
            return a - b;
        }
    }
}

Build your project, then the last step is opening an Excel file, going to: File > Options then selecting Add-Ins. Select “Excel Add-Ins” in the drop down list and then hit “Go…”. Select the “Automation” button and select your component (in this example, the item name to select is AutomationSample.AutomationSample).

Write =AutomationSampleAdd(1,2) in a worksheet cell and you should get 3.

Automation Add-Ins Method with a Reference to Excel

The previous method, mentioned above, allows Excel to call .NET, not the other way around. What if you want to have a reference to the Excel application executing your .NET code? Say to colour certain worksheet columns based on some criteria or for asynchronous call back. In this case, you need to implement IDTExtensibility2 interface.

To implement this method, you need to reference the assemblies displayed to the right, inherit the UdfBase abstract class and implement IDTExtensibility2 interface.

using System;
using ExcelUdf.Automation;
using Extensibility;
using Microsoft.Office.Interop.Excel;

namespace ExcelUdf.ExtensibilityAutomation
{
public abstract class UdfExtensibilityBase : UdfBase, IDTExtensibility2
{
protected Application ExcelApplication { get; set; }

public void OnConnection(object application, 
    ext_ConnectMode connectMode, object addInInst,
    ref Array custom)
{
    ExcelApplication = application as Application;
}

public void OnDisconnection(ext_DisconnectMode removeMode, 
    ref Array custom)
{
}

public void OnAddInsUpdate(ref Array custom)
{
}

public void OnStartupComplete(ref Array custom)
{
}

public void OnBeginShutdown(ref Array custom)
{
}
}
}

In my download project, I implemented this class in a standalone project rather than combining it with the existing one. The reason is this approach requires references to specific Excel interop component version. And once you have these references, your deployment project is increased in complexity as now you need to manage more dependencies and make sure that the right referenced version of Excel is installed on the target machine (check NetOffice if you want to avoid that).

To create your UDF methods and to have a reference to the current Excel instance:

using System.Runtime.InteropServices;
using ExcelUdf.ExtensibilityAutomation;

namespace ExtensibilitySample
{
[ClassInterface(ClassInterfaceType.AutoDual)]
[Guid("7a9de936-0e99-4d38-9c2b-a02a09fb371f")]
public class ExtensibilitySample : UdfExtensibilityBase
{
    public double ExtensibilitySampleAdd(double a, double b)
    {
        return a + b;
    }

    public string WhoAreYou()
    {
        string name = 
           ExcelApplication.Application.InputBox("Who are you?");
        if (string.IsNullOrWhiteSpace(name))
        {
            return string.Empty;
        }
        return "Hello " + name;
    }
}
}

Use this project with Excel as mentioned above.

XLL Add-Ins Method

An XLL is an add-in for Excel that you can build with any compiler that supports building native DLLs (dynamic link libraries), it is supported since Excel 97. It is faster than the Automation Add-Ins and has more features, but XLL components are usually built via C/C++.

Luckily for .NET, there is an open source component with permissive licence called Excel DNA that allows .NET to build XLL add-ins effortlessly.

To build an XLL component, create a new project, download Excel DNA and reference ExcelDna.Integration.dll then write your functions

using ExcelDna.Integration;

namespace XllSample
{
    public class XllSample
    {
        [ExcelFunction(Description = "Adds two numbers", 
                Category = "XLL with .NET Sample Function")]
        public static double XllSampleAdd(double a, double b)
        {
            return a + b;
        }
    }
}

Build then create a file called YourDllName.dna, in this case XllSample.dna with the following content:

<DnaLibrary RuntimeVersion="v4.0">
	<ExternalLibrary Path="XllSample.dll" />
</DnaLibrary>

Drop it next to your dll, then copy ExcelDna.xll or ExcelDna64.xll next to your dll and rename it to match your dll name, in this case XllSample.xll.

Build your project, then the last step is opening an Excel file, going to: File > Options then selecting Add-Ins. Select “Excel Add-Ins” in the drop down list and then hit “Go…”. Select the “Browse” button and select your XllSample.xll.

In an Excel cell, start typing XllSampleAdd and you will get the rest of the function via Excel’s auto complete.

Comparison

Here is a comparison table between the two methods:

Conclusion

Automation Add-Ins support feels more like it is made with VB6 in mind rather than .NET and lacks some important features like auto complete and description when typing in a cell.

XLL Add-Ins per se are complicated from a .NET development point of view, however, Excel DNA did an excellent job in making the interface transparent and abstracted all the nifty details away for the .NET developers.

Download

I have created a Visual Studio 2010 project with all the sample code demonstrated above, feel free to use it and distribute it.

Excel UDF with C# Sample (203 KB)