HOW TO: How can I force a cell to recalculate?

HOW TO: How can I force a cell to recalculate?

Question

How do I force a cell to recalculate? I have a formula that depends on external data and I would like it to recalculate under my control. I do not want to mark it as volatile, since it takes too long to calculate.

Answer

You can use CXlOper::GetFormula and CXlOper::SetFormula to re-set the formula to its current value.

Construct a reference to the cell which you wish to recalculate. Use CXlOper::GetFormula to get its formula, and use CXlOper::SetFormula with the resulting formula.

Example

The macro function below uses CXllFinder to search for all occurrences of MyFunc( in the formulae of the current worksheet. It then forces each cell containing the function to recalculate immediately.

(Note that if the cell is part of an array formula, it is not recalculated in this example.)

#include <xllfinder.h>

// Function:    DirtyCells
// Purpose:     Touch a cell, so that cells dependent on it are recalculated

//{{XLP_SRC(DirtyCells)
    // NOTE - the FunctionWizard will add and remove mapping code here.
    //    DO NOT EDIT what you see in these blocks of generated code!
IMPLEMENT_XLLFN2(DirtyCells, "R", "DirtyCells", "", "User Defined"
    "", "Touch a cell, so that cells dependent on it are"
    " recalculated", "", "appscope=1\0", 2)

extern "C" __declspec( dllexport )
LPXLOPER DirtyCells()
{
    XLL_FIX_STATE;
    CXlOper xloResult;
//}}XLP_SRC
    CXlOper xloActive;
    DWORD sheetId = 0;

    if (xloActive.GetActiveCell()
        && (sheetId = xloActive.GetSheetId()) != 0)
    {
        CXllFinder finder("MyFunc(", TRUE, FALSE, sheetId);
        CXlRef xlr;
        while (finder.FindNext(xlr))
        {
            CXlOper xloRef;
            xloRef.FromMRef(&xlr, 1, sheetId);
            CString formula;
            BOOL bIsArray;
            if (xloRef.GetFormulaIntl(formula, bIsArray, TRUE) && !bIsArray)
                xloRef.SetFormulaIntl(formula, FALSE, TRUE);
        }
    }
    
    return xloResult.Ret();
}

The add-in function below is an example of a non-volatile function that depends on external data (in this case, the current time).

#include <xlldate.h>

// Function:    MyFunc
// Purpose:     A non-volatile function with external dependencies

//{{XLP_SRC(MyFunc)
    // NOTE - the FunctionWizard will add and remove mapping code here.
    //    DO NOT EDIT what you see in these blocks of generated code!
IMPLEMENT_XLLFN2(MyFunc, "R", "MyFunc", "", "User Defined", 
    "A non-volatile function with external dependencies", "", 
    "appscope=1\0", 1)

extern "C" __declspec( dllexport )
LPXLOPER MyFunc()
{
    XLL_FIX_STATE;
    CXlOper xloResult;
//}}XLP_SRC

    double dNow;
    ::XlNow(&dNow);
    xloResult = dNow;
    return xloResult.Ret();
}

See also

CXlOper::GetFormula() in the online documentation.
CXlOper::SetFormula() in the online documentation.
CXllFinder class in the online documentation.