Volatile Managed UDFs
In previous posts and in my book, I discussed how to build managed User-Defined Functions (that is, managed Automation Add-ins that expose functions for use in cell formulas) for Excel. Eric Carter has also explored the topic in his blog. One small detail that I didn't mention is how to build volatile UDFs.
A little reminder of what exactly is a volatile UDF. The default (non-volatile) case is that when Excel calculates a cell that contains a UDF, it recalculates all cells and cell ranges that are passed as arguments to that UDF. This includes indirect references. For example, suppose we use a UDF called Fahr2Cel that converts between Fahrenheit and Celsius, and we put a call to this UDF in a cell. Let's say we pass another cell as an argument to the UDF. Let's also say that the UDF internally accesses the sheet and factors in the value of a third cell (say cell A1) to complete its calculations:
public double Fahr2Cel(double val)
{
Excel.Worksheet sheet = (Excel.Worksheet)xl.ActiveSheet;
double constantSubtraction =
(double)((Excel.Range)sheet.Cells[1, 1]).Value2;
return ((5.0 / 9.0) * (val - constantSubtraction));
}
This is how we'll setup the worksheet formulas:
cell A1 B1 C1
formula 32.0 451 =Fahr2Cel(B1)
As you can see, we only pass the cell reference B1 into the UDF, but internally the UDF also uses the value in cell A1. When you first enter the formula, it is evaluated and both the argument B1 and the internally-referenced A1 are used in the calculation.
However, if you subsequently change the value in A1, the formula is not recalculated because the UDF call does not directly (or indirectly) reference A1.
As things stand, in order for the formula to recalculate, the values in directly or indrectly referenced cells must change. For example, let's say we had this setup:
cell A1 B1 C1 D1 E1
formula 32.0 100 351 =B1+C1 =Fahr2Cel(D1)
The formula only directly references D1, but it will recalculate automatically if we change any of the formulas or values in D1, C1 or B1. Of course, it still won't recalculate if we only change A1.
So, if the result of the UDF depends on cells that the function does not explicitly refer to, and you want the UDF to be recalculated anytime any of those cells change, you can mark the function as volatile. This scenario is actually very common, especially in the financial services sector, where worksheets are typically complex and may include external realtime data feeds, internal VBA code or (increasingly) VSTO code that manipulates cell contents in a manner that is often not directly referenced in cell formulas.
Caveat: If you make your UDF volatile, it recalculates every time you change any value or recalculate any open workbook. This carries an obvious performance penalty.
So, how do you make a managed UDF volatile?
Simple: all you have to do is to call the Volatile method exposed by the Excel.Application object, and call it inside your UDF.
The next problem is how do you get hold of the Application object? Well, the simplest way is to implement IDTExtensibility2 in your automation add-in. Note that while regular COM add-ins must implement IDTExtensibility2, for automation add-ins this is optional. That said, it is generally useful because when Excel calls into your implementation of IDTExtensibility2.OnConnection it will pass you a reference to the Excel Application object.
The listing below shows how you could do this. I've made the Fahr2Cel UDF volatile, and the Cel2Fahr UDF non-volatile for comparison. Then, in my worksheet I have this setup:
cell A1 B1 C1 D1
formula 32.0 451 =Fahr2Cel(B1) =Cel2Fahr(B1)
The net result is that whenever the contents of A1 changes, C1 will be recalculated, but D1 will not.
namespace AutomationAddin
{
using System;
using Microsoft.Office.Core;
using Extensibility;
using System.Runtime.InteropServices;
using Excel = Microsoft.Office.Interop.Excel;
[GuidAttribute("D5FE326A-8DD1-4e0c-BEA0-F95BDEE39574")]
public interface ITemperatureConversion
{
double Fahr2Cel(double val);
double Cel2Fahr(double val);
}
[GuidAttribute("883C1BBA-F8B8-4B85-B7BF-51DB55773A6C"),
ProgId("AutomationAddin.Connect"),
ClassInterface(ClassInterfaceType.AutoDual)]
public class Connect :
Object, Extensibility.IDTExtensibility2, ITemperatureConversion
{
private Excel.Application xl;
private object addInInstance;
public Connect()
{
}
public void OnConnection(object application,
Extensibility.ext_ConnectMode connectMode,
object addInInst, ref System.Array custom)
{
// Cache the Application object for later use.
xl = (Excel.Application)application;
addInInstance = addInInst;
}
public void OnDisconnection(
Extensibility.ext_DisconnectMode disconnectMode,
ref System.Array custom)
{
}
public void OnAddInsUpdate(ref System.Array custom)
{
}
public void OnStartupComplete(ref System.Array custom)
{
}
public void OnBeginShutdown(ref System.Array custom)
{
}
public double Fahr2Cel(double val)
{
// This function is volatile.
// The function's calculations depend on the value of cell A1
// in the worksheet, even though this cell is not referenced
// directly or indirectly in the function's argument list.
// However, marking the function as volatile ensures that the
// function is recalculated whenever any cell changes (including
// cell A1).
object missing = Type.Missing;
xl.Volatile(missing);
Excel.Worksheet sheet = (Excel.Worksheet)xl.ActiveSheet;
double constantSubtraction =
(double)((Excel.Range)sheet.Cells[1, 1]).Value2;
return ((5.0 / 9.0) * (val - constantSubtraction));
}
public double Cel2Fahr(double val)
{
// This function is NOT volatile.
// The function's calculations depend on the value of cell A1
// in the worksheet, but this cell is not referenced either
// directly or indirectly in the function's argument list.
// Therefore, this function will not be automatically
// recalculated when cell A1 (or any other cell) changes.
Excel.Worksheet sheet = (Excel.Worksheet)xl.ActiveSheet;
double constantSubtraction =
(double)((Excel.Range)sheet.Cells[1, 1]).Value2;
return ((val * (9.0 / 5.0)) + constantSubtraction);
}
}
}
Don't forget that you must of course register this in the standard way for automation add-ins, and the .REG file listing below shows an example of the entries you need. The Programmable subkey is the important additional entry over+above what you'd normally use for a COM add-in:
Windows Registry Editor Version 5.00
[HKEY_CLASSES_ROOT\AutomationAddin.Connect]
@="AutomationAddin.Connect"
[HKEY_CLASSES_ROOT\AutomationAddin.Connect\CLSID]
@="{883C1BBA-F8B8-4B85-B7BF-51DB55773A6C}"
[HKEY_CLASSES_ROOT\CLSID\{883C1BBA-F8B8-4B85-B7BF-51DB55773A6C}]
@="AutomationAddin.Connect"
[HKEY_CLASSES_ROOT\CLSID\{883C1BBA-F8B8-4B85-B7BF-51DB55773A6C}\Implemented Categories]
[HKEY_CLASSES_ROOT\CLSID\{883C1BBA-F8B8-4B85-B7BF-51DB55773A6C}\Implemented Categories\{62C8FE65-4EBB-45E7-B440-6E39B2CDBF29}]
[HKEY_CLASSES_ROOT\CLSID\{883C1BBA-F8B8-4B85-B7BF-51DB55773A6C}\InprocServer32]
@="mscoree.dll"
"Assembly"="AutomationAddin, Version=1.0.0.0, Culture=neutral, PublicKeyToken=761a68a97aa92d3b"
"Class"="AutomationAddin.Connect"
"CodeBase"="file:///C:/Temp/AutomationAddin/bin/Debug/AutomationAddin.dll"
"RuntimeVersion"="v2.0.41222"
"ThreadingModel"="Both"
[HKEY_CLASSES_ROOT\CLSID\{883C1BBA-F8B8-4B85-B7BF-51DB55773A6C}\InprocServer32\1.0.0.0]
"Assembly"="AutomationAddin, Version=1.0.0.0, Culture=neutral, PublicKeyToken=761a68a97aa92d3b"
"Class"="AutomationAddin.Connect"
"CodeBase"="file:///C:/Temp/AutomationAddin/bin/Debug/AutomationAddin.dll"
"RuntimeVersion"="v2.0.41222"
[HKEY_CLASSES_ROOT\CLSID\{883C1BBA-F8B8-4B85-B7BF-51DB55773A6C}\ProgId]
@="AutomationAddin.Connect"
[HKEY_CLASSES_ROOT\CLSID\{883C1BBA-F8B8-4B85-B7BF-51DB55773A6C}\Programmable]
[HKEY_CLASSES_ROOT\TypeLib\{8ABE10B3-E686-4C81-8F15-866EF405B4C9}]
[HKEY_CLASSES_ROOT\TypeLib\{8ABE10B3-E686-4C81-8F15-866EF405B4C9}\1.0]
@="AutomationAddin"
[HKEY_CLASSES_ROOT\TypeLib\{8ABE10B3-E686-4C81-8F15-866EF405B4C9}\1.0\0]
[HKEY_CLASSES_ROOT\TypeLib\{8ABE10B3-E686-4C81-8F15-866EF405B4C9}\1.0\0\win32]
@="C:\\Temp\\AutomationAddin\\bin\\debug\\AutomationAddin.tlb"
[HKEY_CLASSES_ROOT\TypeLib\{8ABE10B3-E686-4C81-8F15-866EF405B4C9}\1.0\FLAGS]
@="0"
[HKEY_CLASSES_ROOT\TypeLib\{8ABE10B3-E686-4C81-8F15-866EF405B4C9}\1.0\HELPDIR]
@=""
Also, the usual caveat about unshimmed add-ins applies. All add-ins should be shimmed - either with the OTKLoadr or with your own COM shim as generated by the COM Shim Wizards. See my earlier post for shimming automation add-ins.