Optional Volatile UDFs
Continuing the theme from my last post on Volatile Managed UDFs...
In my previous post, I talked about how to mark a UDF as volatile, but I hard-coded this into each method. With traditional (XLL/XLA style) unmanaged UDFs, the user had the option to specify whether or not an individual call to a UDF should be treated as volatile or not.
If you want to give users back the power to choose, the simplest approach is to extend your parameter list to include a boolean whose value dictates whether the method should be volatile or not. For convenience, you can even make this an optional parameter. The idea is that if the user does omit this parameter at runtime, what you'll get passed in is a System.Reflection.Missing value, and you can then test against this to see if the parameter was omitted. The problem is that you'll only get this if you typed the parameter as an object not a bool - this makes the code somewhat cumbersome, but it's a small compromise.
For example:
public double Fahr2Cel(double val, [Optional] object isVolatile)
{
if (!(isVolatile is System.Reflection.Missing))
{
object missing = Type.Missing;
if ((bool)isVolatile)
{
xl.Volatile(missing);
}
}
Excel.Worksheet sheet = (Excel.Worksheet)xl.ActiveSheet;
double constantFactor =
(double)((Excel.Range)sheet.Cells[1, 1]).Value2;
return ((5.0 / 9.0) * (val - constantFactor));
}
Then, in the cell, the user would enter a formula such as this:
=Fahr2Cel(B2, TRUE)
Or this:
=Fahr2Cel(B2, FALSE)
Or this:
=Fahr2Cel(B2)