Writing efficient VBA UDFs (Part 8) – Getting the previously calculated value from the calling cells

If you have a UDF that depends on some slow-calculating resource you may want the UDF to mostly just return the values obtained  at the last calculation from the cells the UDF occupies, and only occasionally go and use the slow-calculating resource.

In Excel 2010 you can create efficient C++ XLL UDFs that execute asynchronously and multithreaded, which can be a very effective solution.

But how do you get the previous value from the cells calling a VBA UDF?

Lets suppose that you want to pass the UDF a parameter for the slow-calculating resource and a switch to tell it when to use the slow resource. You can set the switch (I am using a defined name called “RefreshSlow”)  and refresh the UDFs in a VBA sub like this:

Sub RefreshUDFs()
Dim lCalcMode As Long
lCalcMode = Application.Calculation
Application.Calculation = xlCalculationManual
Names("RefreshSlow").RefersTo = True
Calculate
Names("RefreshSlow").RefersTo = False
Application.Calculation = lCalcMode
End Sub

I will use a dummy function to simulate getting a slow resource:

Function GetSlowResource(vParam As Variant) As Variant
Dim j As Long
For j = 1 To 10000000
Next j
GetSlowResource = Rnd()
End Function

This function (ignores the parameter) and just (slowly) returns a random number.

There are several ways of getting the previously calculated value for a UDF: they each have advantages and disadvantages.

Application.Caller.Value

You can use Application.Caller.Value, but this causes a circular reference that you have to switch on Iteration to solve. This is slow and can mask other unintentional circular refs, so its not recommended.

Function UDF1(vParam, Refresh)
If Not Refresh Then
UDF1 = Val(Application.Caller.Value2)
Else
UDF1 = GetSlowResource(vParam)
End If
End Function

Application.Caller.Text

If you use Application.Caller.Text you don’t get the circular reference, but it retrieves the formatted value that is displayed in the cell as a string. So if the cell is formatted as a number with 2 decimal places the retrieved value will be truncated to 2 decimal places.

Function UDF2(vParam, Refresh)
If Not Refresh Then
UDF2 = Val(Application.Caller.Text)
Else
UDF2 = GetSlowResource(vParam)
End If
End Function

This solution will work OK if you can control the formatting or the function returns a string.

Application.Caller.ID

You can use the Range.ID property to store and retrieve a string value within the UDF.

Function UDF3(vParam, Refresh)
Dim var As Variant
If Not Refresh Then
UDF3 = Val(Application.Caller.ID)
Else
var = GetSlowResource(vParam)
UDF3 = var
Application.Caller.ID = var
End If
End Function

This works well, except that the Range.ID property is not stored in a Saved workbook, so the next time you open the workbook the retrieved value will be Blank/Zero.

Using an XLM or XLL function to pass the Previous value to the UDF

Using XLM or XLL technology it is possible to create a non-multi-threaded command-equivalent function to retrieve the previous value.
Here is the code for an XLL+ function called PREVIOUS which has a parameter to make it Volatile or not Volatile.
(Command-equivalent functions default to Volatile but when using it to pass the previous value to a VBA UDF you generally want it to be non-volatile).
This function also works for multi-celled array formulae.
Edited following Keith Lewis comment.

CXlOper* PREVIOUS_Impl(CXlOper& xloResult, const CXlOper* Volatile_op)
{
// Input buffers
bool Volatile;
// Validate and translate inputs
static CScalarConvertParams Volatile__params(L"Volatile",
XLA_DEFAULT_ZERO|XLA_DEFAULT_EMPTY|XLA_DEFAULT_NONNUMERIC|
XLA_DEFAULT_BLANK, 0, -1, true);
XlReadScalar(*Volatile_op, Volatile, Volatile__params);
// End of generated code
//}}XLP_SRC
// defined as a macro function defer recalc so that the func gets previous results
CXlOper xloCaller,xlo;
CXlOper arg;
arg=true;
if (!Volatile) arg=false;
// set volatility of this function: 237 is the function number for volatile
xlo.Excel(237,1,&arg);
// Get caller. Fail if it is not a range of cells
if ( ( xloCaller.GetCaller() != 0 ) || !xloCaller.IsRef() ) return CXlOper::RetError(xlerrNA);
//coerce the caller ref
xloResult.Coerce(xloCaller);
return xloResult.Ret();
}

Then you can use this to pass the previous value to the UDF.

Function UDF4(vParam, Refresh, Previous)
Dim var As Variant
If Not Refresh Then
UDF4 = Previous
Else
var = GetSlowResource(vParam)
UDF4 = var
End If
End Function

The UDF is called from a formula like this =UDF4(“AAPL”,RefreshSlow,PREVIOUS(False))

This works well, but requires access to the XLL PREVIOUS function (Laurent Longre’s MOREFUNC addin has a similar function).

Its supposed to be possible to write a similar function using the old XLM Macro language, but I have not tried it.

Conclusion

There are several ways of getting the previous value from the last calculation for a VBA UDF. But the best solution requires using a C++ XLL.

Special prize to the first person to write the XLM Macro Previous UDF!