Results cache

Excel often recalculates cells more often than is necessary, recalculating even when none of the inputs to a formula have changed. For slow calculations this can be very inefficient. The results cache allows you to save the results of each invocation of a function, The next time the function is called with the same inputs, the cache can be examined and the existing results can be returned.

There is no coding involved in using a results cache; the XLL+ Function Wizard provides all the code.

Creating a cache

When you create a new application using the XLL+ AppWizard, be sure to check the box "Include a cache for results". Optionally, you can also check "Save the cache to a file", so that the cache will automatically be saved when the XLL is closed and reloaded each time the XLL is opened.

Using the cache for a function

You can mark your functions as cached using the XLL+ Function Wizard:

Or you can use the Properties window:

Code generated for cache

The Function Wizard generates code in the wrapper functions, like the following. Before calling the implementation function it checks the cache, looking for the exact set of inputs. If the check succeeds, then the stored result is returned immediately.

If the result is not found, then the implementation function is called as usual, and the result is added to the cache.

The code changes do not touch the implementation function, where your own code is located. The cache can therefore be switched on or off with no impact on your code.

extern "C" __declspec(dllexport)
LPXLOPER4 SLOWCALC_4(double X, double Y)
{
    XLL_FIX_STATE;
    CXlOper xloResult;
    CXlInputKey key("SLOWCALC", &X, true);                
    if (XllGetTypedApp()->m_cache.Find(key, xloResult))   
        return xloResult.Ret4();                          
    try {
        xloResult.HandleResult(SLOWCALC_Impl(xloResult, X, Y));
    }
    catch(const CXlRuntimeException& ex) {
        CXllApp::Instance()->DisplayException(xloResult, ex);
    }
    XllGetTypedApp()->m_cache.Add(key, xloResult);        
    return xloResult.Ret4();
}

Next: Menus >>