Creating a User Defined Function in C#

UPDATE: See latest post: http://davecra.com/2013/06/08/update-creating-excel-udfs-in-c/

UPDATE: See POC example project: http://davecra.com/2014/02/25/codeplex-loading-an-excel-udf-from-vsto/

…and replacing a formula at runtime.

In this posting, I am going to tackle two things at once:

The first is writing an Excel User Defined Function (UDF) using an Add-in via C#.
The second is a far more difficult proposition of having the function replace the formula in the evaluated cell.

I was working at a customer who has some legacy UDF’s still in VBA Add-ins (XLA or XLAM files). I strongly advise clients against mixing and matching VSTO and VBA. So, I showed them how to move their UDF’s completely to a C# add-in.

Additionally, in evaluating their issue, they want to replace their legacy UDF with a simple placeholder function that will repeat the value given. So, it boils down to taking the existing formula while it is being evaluated and replacing it. Excel will not let you do this… easily.

The following code demonstrated how to define a UDF in C# and then to replace a formula while it is being evaluated:

[GuidAttribute(“D94AF1AD-7E2A-4611-AA6F-47351FF46ACD”)]
public interface IFunctions
{
string UDF_Replace(object a); // new interface
string UDF(object a, object b); // legacy interface
}///<summary>
/// This is how COM will see the class for the functions
/// using the interface defined above. This has to use
/// the IDTExtensibility interface as well. You cannot
/// use VSTO as it short-circuts the extensibility for
/// you automatically and there is no way to register
/// it to be seen in this way…
///</summary>
[GuidAttribute(“1A43DEAA-EE1D-4e0d-8CC1-79B3998B7CEB”),
ProgId(“ExcelFunctionsExample.Connect”),
ClassInterface(ClassInterfaceType.AutoDual)]
[ComDefaultInterface(typeof(IFunctions))]
public class Connect : Object, Extensibility.IDTExtensibility2, IFunctions
{
public Connect() { } // constructor
// These 6 methods are required for the IDTExtensibility2 interface
public void OnBeginShutdown(ref System.Array a) { }
public void OnStartupComplete(ref System.Array a) { }
public void OnAddInsUpdate(ref System.Array a) { }
public void OnDisconnection(Extensibility.ext_DisconnectMode e,
ref System.Array a) { }
public void OnConnection(object application,
Extensibility.ext_ConnectMode e,
object oo, ref System.Array a) { }
// These next three methods are registering the DLL with COM
[ComRegisterFunctionAttribute]
public static void RegisterFunction(Type type)
{
Registry.ClassesRoot.CreateSubKey(GetSubKeyName(type));
}
[ComUnregisterFunctionAttribute]
public static void UnregisterFunction(Type type)
{
Registry.ClassesRoot.DeleteSubKey(GetSubKeyName(type), false);
}
private static string GetSubKeyName(Type type)
{
string s = @”CLSID\{“ + type.GUID.ToString().ToUpper() +
@”}\Programmable”;
return s;
}

///<summary>
/// This function is the new user defined function
///</summary>
///<param name=”a”></param>
///<returns></returns>
public string UDF_Replace(object a)
{
// >> Do your work here <<
return a.ToString();
}

///<summary>
/// This function is the legacy user defined function
///</summary>
///<param name=”a”></param>
///<param name=”b”></param>
///<returns></returns>
public string UDF(object a, object b)
{
// get reference to the cell where the function
// is being evaluated
Excel.Range thisRange = (Excel.Range)
((Excel.Range)a).Application.get_Caller(1);
// get the value in the cell…
string ret = thisRange.Value2.ToString();
// You cannot change a formula while you are inside the
// evaluation of that formula. But you can spawn off a
// thread so that this will get processed after this
// function exits…
new Thread(new ThreadStart(delegate
{
// replace the formula
thisRange.FormulaR1C1Local = “=UDF_Replace(“ + ret + “)”;
})).Start();
return ret; // return
}
}