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!

About these ads
This entry was posted in Calculation, UDF, VBA, XLL. Bookmark the permalink.

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

  1. There is an easier way to implement your PREVIOUS function. See http://xllutility.codeplex.com/SourceControl/changeset/view/10353#136228.

    Source code:

    static AddInX xai_this(
    FunctionX(XLL_LPXLOPERX XLL_UNCALCEDX, _T(“?xll_this”), _T(“THIS”))
    .Category(CATEGORY)
    .FunctionHelp(_T(“Return the contents of the calling cell.”))
    .Documentation(
    _T(“The contents are the last calculated value for the cell.”)
    )
    );
    LPXLOPERX WINAPI
    xll_this(void)
    {
    #pragma XLLEXPORT
    static OPERX x;

    x = ExcelX(xlCoerce, ExcelX(xlfCaller));

    return &x;
    }

    • fastexcel says:

      Thanks Keith, I have simplified the array-handling bit but kept the volatility option and error-checking. BTW how do you make your function command-equivalent?

      • XLL_UNCALCED does the trick. Another way to create the add-in is

        static AddIn xai_this(
        “?xll_this”, XLL_LPXLOPER XLL_UNCALCED,
        “THIS”, “”,
        CATEGORY, “Return the contents of the calling cell.”
        );

        XLL_LPXLOPER is #defined as “R” and XLL_UNCALCED is “#”. I let the C++ preprocessor concatenate the strings into “R#”.

        To make it volatile tack on XLL_VOLATILE, aka “!”

      • fastexcel says:

        Macro-command-equivalent functions are by default considered volatile unless you switch it off: see the False Volatility – is this a bug post

  2. Harlan Grove says:

    XLM: name the top cell prev as a function in a Macro sheet.

    =RESULT(95)
    =RETURN(GET.CELL(5, CALLER()))

    Call this in a standard worksheet cell formula as follows:

    =prev()+1

    • fastexcel says:

      Thanks Harlan: works great for a single cell. You earn the title of XLM Master.

      Is there a way to make it work for a multi-cell array formula? Looking at the documentation for GET.CELL(5) it looks like it only returns the value from the top-left calling cell.

      • Harlan Grove says:

        I think arrays would require another XLM function. Call it kludge referring to the top cell of

        =RESULT(95)
        =ARGUMENT(“foo”,95)
        =RETURN(foo)

        Now change prev to refer to the top cell of

        =RESULT(95)
        =RETURN(kludge(CALLER()))

        In a worksheet, select C3:D6, enter the array formula

        =(prev()+{2,3;5,7;11,13;17,19})

        and repeatedly press [F9].

      • fastexcel says:

        Thanks Harlan: Its strange – your example works with the extra XLM function but when I try using it with a multi-cell array VBA UDF it gives me a circular ref.

      • Harlan Grove says:

        It’s not a good thing that the XLM functions work without triggering circular recalc. Using it could require using [Ctrl]+[Alt]+[F9] full recalcs rather than minimal [F9] recalcs. FWLIW, Lotus 123′s @@ function, roughly equivalent to Excel’s INDIRECT, didn’t trigger circular recalc in 123, and that was a pain to work around.

        Anyway, where’s my prize?!

  3. fastexcel says:

    @Harlan “The XLM Master”,
    Your prize is to be awarded the title of
    “The XLM Master”
    for the first XLM (single-cell) Previous function …

  4. Lori Miller says:

    For multicell references this appears to be sufficient and refreshes with F9:

    =RESULT(95)
    =RETURN(CALLER())

    Otherwise i think you could just use a sheet level name and with sheet1!A1 selected define “sheet1!prev” to be either of:

    =GET.CELL(53,sheet1!A1)
    =GET.CELL(53,INDEX(sheet1!1:1:sheet1!A:A,ROW(),COLUMN()))

    which return the equivalent of the Text property and refresh with ctrl+alt+F9. The second allows for multicell refs and should be backward compatible.

    Interestingly, it seems you can use an array of single cell references as the argument to XLM functions that take reference arguments but only if the formula is array-entered in multiple cells. So entering:

    =RETURN(GET.CELL(5,OFFSET(CALLER(),ROW(CALLER())-MIN(ROW(CALLER()))
    ,COLUMN(CALLER())-MIN(COLUMN(CALLER())),1,1)))

    with Ctrl+Shift+Enter in a 2×1 range in the second line of the macro also appears to allow for multicell refs.

    This all follows on from Harlan’s earlier comment – he is the true XLM Master :)

  5. carancho says:

    Hi. I need to implement this on a VBA UDF that has multiple cells as arguments and that is called from arrays {=x12f(E6:E18)}
    (it is an implementation of the X12 seasonal adjustment program from the Census Bureau). I have no experience with XLLs or XLM macros, and I couldn’t get any of this. Could you give me a pointer? I guess the XLL takes care of it but I don’t even know how to compile it. I tried using the XLL UTILITY THIS() function, but I can’t get it to work even on the simplest single cell argument/single cell caller case.

    Thanks a lot!!

    • fastexcel says:

      Well it has been a few years since I last used X12 Arima, but I can’t think of any reason why you would need the cell values from the previous calculation? Are you sure thats what you need?

      • carancho says:

        I’d prefer to manually recalc only those UDFs. X12 arima runs in a slow external .exe; I’d rather retain automatic recalculation of the whole book except for those arrays.

        I got the XLL UTILITY’s THIS function working just now. Still I can’t it to work properly. Instead of passing THIS() as a function parameter I’m evaluating this as a return value
        x12f=application.evaluate(“=this()”)
        and it returns a #Value error. I’ll change the code and try again with your way.

      • kalx says:

        This sounds like a job for an asynchronous function.
        See http://xll.codeplex.com/SourceControl/changeset/view/20106#139508 for an example of how to do this.
        The THIS() function is not what you want.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s