UPDATE: See POC example project: http://davecra.com/2014/02/25/codeplex-loading-an-excel-udf-from-vsto/
One of the most common questions I get – and I am getting more often these days – is how to create an Excel User Defined Function (UDF) in Excel with the following criteria:
- Using Visual Studio and C# preferably from a Visual Studio Tools for Office (VSTO) add-in.
- Be able to access the Excel.Application object
- Have it complete register itself without administrative rights.
- Not use 3rd party libraries, XLL shims and/or VBA intermixed
The most common request is actually, how to get a VSTO add-in in Excel to register a UDF. Unfortunately, the answer is: you cannot. I welcome anyone who has figured this out – without using an XLL or VBA shim – to let me know how they did it from a VSTO project.
I have tried many different options via separate COM registration, a separate COM class in my project, and finally by asking this question directly to the Microsoft VSTO team. This seems to be a very popular topic on the web these days, so hopefully, they will take notice and build an interface for UDF’s in the next release of VSTO.
With that said, you can still get your VSTO add-in to load your UDF’s, albeit they must be in a separate DLL file. And in order to register on the system without Administrator rights, you will need a VSTO add-in to create an instance, connect to the class and then call the register function (a non-static register function) (see below).
As for gaining access to the Excel Application object – something you get with a VBA UDF – I find a lot of articles out there having users just creating a basic Windows DLL and registering it for Programmability. This is not really enough. To enjoy the full power of Excel in your UDF add-in, you need to register it as an add-in and to do this outside of VSTO means you need to create an COM Extensibility add-in (something gone in Visual Studio 2012).
Finally, the code below will demonstrate how to register the add-in completely in the HKEY_CURRENT_USER key of the registry so there is no need for Administrator rights to deploy the add-in. The requirement will be for the VSTO add-in to call the UDF register function.
Here is the code, with comments:
using System;
using Extensibility;
using System.Runtime.InteropServices;
using Microsoft.Win32;
using Excel = Microsoft.Office.Interop.Excel;
using System.Windows.Forms;
using System.Reflection;
using System.Threading;
namespace ExcelFunctions
{
[GuidAttribute("FF4AF1AD-7E2A-4611-AA6F-47351FF46AFD")]
public interface IFunctions
{
string RANDOMTABLE(int r, int c);
}
[GuidAttribute("30A29909-AF27-4814-9CBE-ED6A39A4B9A5"),
ProgId("ExcelFunctions.Connect"),
ClassInterface(ClassInterfaceType.AutoDual),
ComDefaultInterface(typeof(IFunctions))]
public class Connect : Object, Extensibility.IDTExtensibility2, IFunctions
{
static string NAME = "";
/// <summary>
/// Generates a random able in the row and cell just
/// below the current cell - regardless of current
/// contents
/// </summary>
/// <param name="r"></param>
/// <param name="c"></param>
/// <returns></returns>
public string RANDOMTABLE(int r, int c)
{
// this gives s a reference to the exact cell with this function
// what is importat to NOTE is that wihtout passing in a reference
// to a Range object, I still have access to the full Excel
// application Object Model becase we are registering this DLL
// as an IDTExtensibility2 add-in. This interface, as you will see
// below, provides us with a referene to the Application object
// on the Connection...
Excel.Range rng = (Excel.Range)Application.get_Caller(1);
// now - this is important to note. Excel will not allow you to
// manipluate other cells or change certain items while you are
// evaluating a function as part of a claculation event. The
// best way to describe this is that Excel is in "Edit Cell"
// mode. Attempting to manipulate the cell or any other cell
// will throw an exception - so, we spawn a thread and do the
// work - later...
new Thread(() =>
{
// In this thread which will not execute until the current
// calculation chain is complete, we will bild a table full
// of random values.
for (int rowCnt = rng.Row + 1; rowCnt <= (rng.Row + r); rowCnt++)
{
for (int colCnt = rng.Column; colCnt < (rng.Column + c); colCnt++)
{
Excel.Range nextCell = ((Excel.Worksheet)rng.Parent).Cells[rowCnt, colCnt];
nextCell.Value2 = new Random().Next(999).ToString();
Marshal.ReleaseComObject(nextCell); // clean
}
}
// important - release the range to prevent Excel hanging
// around after the user closes it
Marshal.FinalReleaseComObject(rng);
}).Start();
// simply retun a string to the active cell
return "RANDOM TABLE";
}
#region IDTExtensibility2
private static Excel.Application Application; // our ref to Excel
private static object ThisAddIn;
private static bool fVstoRegister = false;
public Connect() { }
/// <summary>
/// We call this from VSTO so that we can get the DLL
/// to register itself and load every time
/// </summary>
/// <returns></returns>
public string Register() // exposed to VSTO
{
fVstoRegister = true;
RegisterFunction(typeof(Connect));
return NAME; // return the name of this instance
}
/// <summary>
/// When we finally do connect and load in Excel we want to get the
/// reference to the application, so that we can use the application
/// instace in our UDF as needed
/// </summary>
/// <param name="application"></param>
/// <param name="connectMode"></param>
/// <param name="addInInst"></param>
/// <param name="custom"></param>
public void OnConnection(object application, Extensibility.ext_ConnectMode connectMode, object addInInst, ref System.Array custom)
{
// get a reference to the instance of the add-in
Application = application as Excel.Application;
ThisAddIn = addInInst;
}
/// <summary>
/// When we disconnect - remove everything - clean up
/// </summary>
/// <param name="disconnectMode"></param>
/// <param name="custom"></param>
public void OnDisconnection(Extensibility.ext_DisconnectMode disconnectMode, ref System.Array custom)
{
// clean up
Marshal.ReleaseComObject(Application);
Application = null;
ThisAddIn = null;
GC.Collect();
GC.Collect();
GC.WaitForPendingFinalizers();
}
// the following functions are required to be defined, but not needed
public void OnAddInsUpdate(ref System.Array custom) {}
public void OnStartupComplete(ref System.Array custom) {}
public void OnBeginShutdown(ref System.Array custom) { }
/// <summary>
/// Registers the COM Automation Add-in in the CURRENT USER context
/// and then registers it in all versions of Excel on the users system
/// without the need of administrator permissions
/// </summary>
/// <param name="type"></param>
[ComRegisterFunctionAttribute]
public static void RegisterFunction(Type type)
{
string PATH = System.Reflection.Assembly.GetExecutingAssembly().GetName().CodeBase.Replace("\\", "/");
string ASSM = Assembly.GetExecutingAssembly().FullName;
int startPos = ASSM.ToLower().IndexOf("version=") + "version=".Length;
int len = ASSM.ToLower().IndexOf(",", startPos) - startPos;
string VER = ASSM.Substring(startPos, len);
string GUID = "{" + type.GUID.ToString().ToUpper() + "}";
NAME = type.Namespace + "." + type.Name; // global
string BASE = @"Classes\" + NAME;
string CLSID = @"Classes\CLSID\" + GUID;
// open the key
RegistryKey CU = Registry.CurrentUser.OpenSubKey("Software", true);
// is this version registred?
RegistryKey key = CU.OpenSubKey(CLSID + @"\InprocServer32\" + VER);
if (key == null)
{
// The version of this class currently being registered DOES NOT
// exist in the registry - so we will now register it
// BASE KEY
// HKEY_CURRENT_USER\CLASSES\{NAME}
key = CU.CreateSubKey(BASE);
key.SetValue("", NAME);
// HKEY_CURRENT_USER\CLASSES\{NAME}\CLSID}
key = CU.CreateSubKey(BASE + @"\CLSID");
key.SetValue("", GUID);
// CLSID
// HKEY_CURRENT_USER\CLASSES\CLSID\{GUID}
key = CU.CreateSubKey(CLSID);
key.SetValue("", NAME);
// HKEY_CURRENT_USER\CLASSES\CLSID\{GUID}\Implemented Categories
key = CU.CreateSubKey(CLSID + @"\Implemented Categories").CreateSubKey("{62C8FE65-4EBB-45e7-B440-6E39B2CDBF29}");
// HKEY_CURRENT_USER\CLASSES\CLSID\{GUID}\InProcServer32
key = CU.CreateSubKey(CLSID + @"\InprocServer32");
key.SetValue("", @"c:\Windows\SysWow64\mscoree.dll");
key.SetValue("ThreadingModel", "Both");
key.SetValue("Class", NAME);
key.SetValue("CodeBase", PATH);
key.SetValue("Assembly", ASSM);
key.SetValue("RuntimeVersion", "v4.0.30319");
// HKEY_CURRENT_USER\CLASSES\CLSID\{GUID}\InProcServer32\{VERSION}
key = CU.CreateSubKey(CLSID + @"\InprocServer32\" + VER);
key.SetValue("Class", NAME);
key.SetValue("CodeBase", PATH);
key.SetValue("Assembly", ASSM);
key.SetValue("RuntimeVersion", "v4.0.30319");
// HKEY_CURRENT_USER\CLASSES\CLSID\{GUID}\ProgId
key = CU.CreateSubKey(CLSID + @"\ProgId");
key.SetValue("", NAME);
// HKEY_CURRENT_USER\CLASSES\CLSID\{GUID}\Progammable
key = CU.CreateSubKey(CLSID + @"\Programmable");
// now register the addin in the addins sub keys for each version of Office
foreach (string keyName in Registry.CurrentUser.OpenSubKey(@"Software\Microsoft\Office\").GetSubKeyNames())
{
if (IsVersionNum(keyName))
{
// and now set it to a loaded state by adding it to the options key
key = Registry.CurrentUser.OpenSubKey(@"Software\Microsoft\Office\" + keyName + @"\Excel\Options", true);
if (key != null)
{
// loop though all the names and count how many have the name OPEN#
int openCnt = 0;
foreach (string optionKeyName in key.GetValueNames())
if (optionKeyName.StartsWith("OPEN"))
openCnt++;
// Add the open key
key.SetValue("OPEN" + (openCnt == 0 ? "" : openCnt.ToString()), "/A " + NAME);
}
}
}
if (!fVstoRegister)
{
// all done - this just helps to assure REGASM is complete
// this is not needed, but is useful for troubleshooting
MessageBox.Show("Registered " + NAME + ".");
}
}
}
/// <summary>
/// Unregisters the add-in, by removing all the keys
/// </summary>
/// <param name="type"></param>
[ComUnregisterFunctionAttribute]
public static void UnregisterFunction(Type type)
{
string GUID = "{" + type.GUID.ToString().ToUpper() + "}";
string NAME = type.Namespace + "." + type.Name;
string BASE = @"Classes\" + NAME;
string CLSID = @"Classes\CLSID\" + GUID;
// open the key
RegistryKey CU = Registry.CurrentUser.OpenSubKey("Software", true);
// DELETE BASE KEY
// HKEY_CURRENT_USER\CLASSES\{NAME}
try
{
CU.DeleteSubKeyTree(BASE);
}
catch { }
// HKEY_CURRENT_USER\CLASSES\{NAME}\CLSID}
try
{
CU.DeleteSubKeyTree(CLSID);
}
catch { }
// now un-register the addin in the addins sub keys for Office
// here we just make sure to remove it from allversions of Office
foreach(string keyName in Registry.CurrentUser.OpenSubKey(@"Software\Microsoft\Office\").GetSubKeyNames())
{
if(IsVersionNum(keyName))
{
RegistryKey key = Registry.CurrentUser.OpenSubKey(@"Software\Microsoft\Office\" + keyName + @"\Excel\Add-in Manager", true);
if (key != null)
{
try
{
key.DeleteValue(NAME);
}
catch { }
}
key = Registry.CurrentUser.OpenSubKey(@"Software\Microsoft\Office\" + keyName + @"\Excel\Options", true);
if (key == null)
continue;
foreach (string valueName in key.GetValueNames())
{
if (valueName.StartsWith("OPEN"))
{
if (key.GetValue(valueName).ToString().Contains(NAME))
{
try
{
key.DeleteValue(valueName);
}
catch { }
}
}
}
}
}
MessageBox.Show("Unregistered " + NAME + "!");
}
/// <summary>
/// HELPER FUNCTION
/// This assists is in determining if the subkey string we are passed
/// is of the type like:
/// 8.0
/// 11.0
/// 14.0
/// 15.0
/// </summary>
/// <param name="s"></param>
/// <returns></returns>
public static bool IsVersionNum(string s)
{
int idx = s.IndexOf(".");
if (idx >= 0 && s.EndsWith("0") && int.Parse(s.Substring(0, idx)) > 0)
return true;
else
return false;
}
#endregion
}
}
Finally, from your VSTO add-in, you will want to write code like this:
ExcelFunctions.Connect functionsAddinRef = null;
private void ThisAddIn_Startup(object sender, System.EventArgs e)
{
// get a new reference to out UDF Project
functionsAddinRef = new ExcelFunctions.Connect();
// first register it
string name = functionsAddinRef.Register();
// then install it
Application.AddIns.Add(name).Installed = true;
}
When your VSTO add-in loads, your UDF will Register and Load as well. This this example, once loaded in Excel, simply enter the following formula in an empty cell and press enter:
=RANDOMTABLE(4,4)
You will get a table like this:
