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!

Advertisements
This entry was posted in Calculation, UDF, VBA, XLL. Bookmark the permalink.

21 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 🙂

    • hymced says:

      all this does not make any sense to me… lets say CALLER() is Range $A$1:$C$3, then OFFSET(CALLER(),ROW(CALLER())-MIN(ROW(CALLER())),COLUMN(CALLER())-MIN(COLUMN(CALLER())),1,1) does not refer to a valid Range.

      The first part of the OFFSET arguments (meaning OFFSET(CALLER(),ROW(CALLER())-MIN(ROW(CALLER())),COLUMN(CALLER())-MIN(COLUMN(CALLER()))) ) should somehow “stretch” the initial Range ($A$1 –> $A$1 ; $A$2 –> $A$3 ; $A$3 –> $A$5 ; etc.) since ROW(CALLER())-MIN(ROW(CALLER())) would be a vector {0;1;2}, as would be COLUMN(CALLER())-MIN(COLUMN(CALLER())). But this is not possible. Array function {=OFFSET($A$1:$C$3,ROW( $A$1:$C$3)-MIN(ROW( $A$1:$C$3)),COLUMN( $A$1:$C$3)-MIN(COLUMN( $A$1:$C$3)))} returns #VALUE! error.

      But lets assume it would work, and returns Range $A$1:$E$5. In regard of the second part of the OFFSET arguments (meaning the [height] and [width] optional arguments), the OFFSET would “shrink back” the Range to $A$1. So it all sums up to a simple GET.CELL(5;R1C1) !!!

      So I have no idea what you tried to tell here.

  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.

  6. Jeff Weir says:

    This is awesome, Charles. I’m using it to short-circuit a computationally expensive Fuzzy Match UDF from running if the undo stack contains the word ‘Filter, which largely gets around the behaviour you comment on at https://fastexcel.wordpress.com/2011/11/25/writing-efficient-vba-udfs-part-7-udfs-calculated-multiple-times/#comment-1022

    • Jeff Weir says:

      Ah bugger…on closer inspection it seems that the undo stack only gets updated AFTER the UDFS have executed, meaning that my short-circuit code is always one action behind. Perhaps the only way around is to have some kind of UDF Refresh capability similar to PivotTables…they only update once the user hits that global REFRESH button. Not ideal…kind of like turning calculation to manual for UDFs only.

  7. Jeffrey Weir says:

    Double bugger. I think there’s a flaw in your approach of short circuiting the UDF until you want it to be called: It only works if you happen to be in Manual Calc Mode, and it doesn’t work if in Automatic Calc Mode. Which pretty much defeats the purpose of the code. Or rather, it adds nothing compared with simply switching to Manual Calc Mode in the first place.

    Here’s a walkthrough of my logic, which seems to be backed up by the tests I’ve run:

    1. In Automatic Calc mode, you change the input cells to UDF2(i.e. whatever cell that vParam points at)
    2. This triggers UDF2. Because Names(“RefreshSlow”).RefersTo is False, the cell gets overwritten with it’s previous value, courtesy of the UDF2 = Val(Application.Caller.Text) line. But this cleans all cells concerned, meaning Excel can’t tell that you might want to update this value in the future.
    3. You trigger the RefreshUDFs sub, but UDF2 doesn’t get triggered, because the cells concerned are not dirty. So you still have the old random number in the cell, instead of generating a new one.

    You don’t have the same issue when in Manual Calculation Mode, because when you change the input cells, the UDF doesn’t get called, and the changed input cell remains dirty…meaning that when you next call the RefreshUDF sub, Excel knows to recalculate the UDF.

    The only way I can see around this is to either:
    1. Use ForceFullCalculation, as per your post at https://fastexcel.wordpress.com/2015/05/19/excel-forcefullcalculation-trading-off-editing-speed-vs-calculation-speed/ (but this can be tedious, and is application-wide until restart); or
    2. Scan each and every cell for your UDF, and forcefully recalculate it. (Which could take ages).

    If I’ve lost you, I have a spreadsheet using your code that demonstrates the issue. Would love to hear your thoughts on this.

  8. fastexcel says:

    @Jeff,
    Try this mod to Test 2: makes it volatile if its automatic
    Function TEST2(vParam)
    If Application.Calculation = xlCalculationAutomatic Then
    Application.Volatile True
    Else
    Application.Volatile False
    End If
    If Names(“CalculateUDF”).RefersTo = “=FALSE” Then
    TEST2 = Val(Application.Caller.Text)
    Else
    TEST2 = GetSlowResource(vParam)
    End If
    End Function

  9. jeffrey Weir says:

    Charles…that results in the UDF not being short circuited at all, and also in the UDF being recalculated whenever any change is made anywhere. Which is pretty much the opposite of the intent of being able to control when you want the UDF calculated.

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