Writing user defined functions for Excel in .NET
I've updated this article with some new guidance--for the details, see this post.
Excel enables the creation of user defined functions that can be used in Excel formulas. A developer must create a special kind of DLL called an XLL. Excel also allows you to write custom functions in VBA that can be used in Excel formulas. Unfortunately, Excel does not support or recommend writing an XLL that uses managed code. If you are willing to take your chances that your XLL might not run in current or future versions of Excel, there are solutions available that enable this scenario—search the web for “managed XLL”.
Fortunately, there is an easier way to create a user defined function that doesn’t require you to create an XLL dll. Excel XP, Excel 2003, and Excel 2007 support something called an Automation Add-in. An Automation Add-in can be created quite simply in C# or VB.NET. I'm going to show you an example in C#.
First, launch Visual Studio 2005 or Visual Studio 2008 and create a new C# class library project called AutomationAddin for this example. Screenshots here will show Visual Studio 2005 and Excel 2003, but the steps are pretty much the same for Visual Studio 2008 and Excel 2007. I'll note inline where they are different.
Then, in your Class1.cs file, enter the code shown below. Replace the GUID with your own GUID that you create by using Generate GUID in the Tools menu of Visual Studio.
using System;
using System.Runtime.InteropServices;
using Microsoft.Win32;
namespace AutomationAddin
{
// Replace the Guid below with your own guid that
// you generate using Create GUID from the Tools menu
[Guid("5268ABE2-9B09-439d-BE97-2EA60E103EF6")]
[ClassInterface(ClassInterfaceType.AutoDual)]
[ComVisible(true)]
public class MyFunctions
{
public MyFunctions()
{
}
public double MultiplyNTimes(double number1, double number2, double timesToMultiply)
{
double result = number1;
for (double i = 0; i < timesToMultiply; i++)
{
result = result * number2;
}
return result;
}
[ComRegisterFunctionAttribute]
public static void RegisterFunction(Type type)
{
Registry.ClassesRoot.CreateSubKey(
GetSubKeyName(type, "Programmable"));
RegistryKey key = Registry.ClassesRoot.OpenSubKey(
GetSubKeyName(type, "InprocServer32"), true);
key.SetValue("",
System.Environment.SystemDirectory + @"\mscoree.dll",
RegistryValueKind.String);
}
[ComUnregisterFunctionAttribute]
public static void UnregisterFunction(Type type)
{
Registry.ClassesRoot.DeleteSubKey(
GetSubKeyName(type, "Programmable"), false);
}
private static string GetSubKeyName(Type type,
string subKeyName)
{
System.Text.StringBuilder s =
new System.Text.StringBuilder();
s.Append(@"CLSID\{");
s.Append(type.GUID.ToString().ToUpper());
s.Append(@"}\");
s.Append(subKeyName);
return s.ToString();
}
}
}
With this code written, show the properties for the project by double clicking on the properties node under the project in Solution Explorer. Click on the Build tab and check the check box that says “Register for COM Interop”. At this point you have an extra step if you are running on Windows Vista or higher. Visual Studio has to be run with administrator privileges to register for COM interop. Save your project and exit Visual Studio. Then find Visual Studio in the Start menu and right click on it and choose "Run as Administrator". Reopen your project in Visual Studio. Then choose “Build” to build the add-in.
Now launch Excel and go to the Tools, Add-ins dialog in Excel 2003 and click on the Automation button. If you are running Excel 2007, you get to the Automation servers dialog by following these steps:
1. Launch Excel and click the Microsoft Office button in the top left corner of the window.
2. Choose Excel Options.
3. Click the Add-Ins tab in the Excel Options dialog.
4. Choose Excel Add-Ins from the combo box labeled Manage. Then click the Go button.
5. Click the Automation button in the Add-Ins dialog.
You can find the class you created by looking for AutomationAddin.MyFunctions in the list of Automation add-ins:
By clicking OK in this dialog, you add AutomationAddin.MyFunctions to the list of installed add-ins as shown here. In prior versions of these instructions, you would get a dialog at this point about not being able to find mscoree.dll. I've updated this article so this doesn't happen anymore--the magic is in the two final lines of RegisterFunction that register the full path to mscoree.dll which makes Excel happier.
Now, let’s try to use the function MultiplyNTimes inside Excel. First create a simple spreadsheet that has a number, a second number to multiple the first by, and a third number for how many times you want to multiply the first number by the second number. An example spreadsheet is shown here:
Click on an empty cell in the workbook below the numbers and then click on the Insert Function button in the formula bar. From the dialog of available formulas, drop down the “Or select a category” drop down box and choose “AutomationAddin.MyFunctions. Then click on the MultiplyNTimes function as shown here:
When you press the OK button, Excel pops up a dialog to help you grab function arguments from the spreadsheet as shown here:
Finally, click OK and see your final spreadsheet as shown here with your custom formula in cell C5.
You might experiment with other functions that return other types of values. For example, the code snippet below shows several other functions you could add to your MyFunctions class. To use this code you must add a reference to the Excel 11.0 Object Library for Excel 2003 or the Excel 12.0 Object model for Excel 2007 and also add the code “using Excel = Microsoft.Office.Interop.Excel” to the top of your class file. Note in particular that when you declare a parameter as an object, Excel passes you a Range object. Also note how optional parameters are supported by AddNumbers.
public string GetStars(double Number)
{
string s = "";
for (double i = 0; i < Number; i++)
{
s = s + "*";
}
return s;
}
public double AddNumbers(double Number1, [Optional] object Number2, [Optional] object Number3)
{
double result = 0;
result += Convert.ToDouble(Number1);
if (!(Number2 is System.Reflection.Missing))
{
Excel.Range r2 = Number2 as Excel.Range;
double d2 = Convert.ToDouble(r2.Value2);
result += d2;
}
if (!(Number3 is System.Reflection.Missing))
{
Excel.Range r3 = Number3 as Excel.Range;
double d3 = Convert.ToDouble(r3.Value2);
result += d3;
}
return result;
}
public double CalculateArea(object Range)
{
Excel.Range r = Range as Excel.Range;
return Convert.ToDouble(r.Width) * Convert.ToDouble(r.Height);
}
public double NumberOfCells(object Range)
{
Excel.Range r = Range as Excel.Range;
return r.Cells.Count;
}
public string ToUpperCase(string input)
{
return input.ToUpper();
}