Volatile functions

Add a function that uses application-level data

Open Tutorial1.cpp, and use the XLL+ Function Wizard to add a new function, STOCKNAME. Set the function's attributes as follows.

Name:	STOCKNAME
Category:	Equities
Description:	Look up a stock code and return the company name

Notice that we've created a new category, Equities, which will now appear in Excel's Formula Wizard.

Add an argument, as follows:

Name:	Code
Type:	String
Dimensions:	Scalar
Description:	Stock code

Make the function volatile

Finally, click on the Volatile check-box, as shown below.

By marking the function as volatile, we are instructing Excel to recalculate the formula every time the worksheet containing it is recalculated. Because STOCKNAME depends on data that is outside Excel's control (in this case, the in-memory database), we must always assume that this external data has changed since we last called the function, and recalculate the function.

Marking functions as volatile is a standard solution, but potentially it can be very expensive. Even if the value you return from a volatile function is unchanged, Excel will assume that it has changed, and will recalculate all cells which depend on the volatile cell. If those dependent cells contain slow expensive calculations, you can find yourself doing a long recalculation every time any cell is changed.

Use the application data

Back in DevStudio, change the generated code as shown below:

extern "C" __declspec( dllexport )
LPXLOPER STOCKNAME(const char* Code)
{
    CXlOper xloResult;
//}}XLP_SRC

    // Get a pointer to the application object                         
    CTutorial1App* app = (CTutorial1App*)XllGetApp();                  

    // Look up the code and return the company name or #N/A!           
    CString strCompanyName;                                            
    if (app->FindStockCode(Code, strCompanyName))                      
        xloResult = strCompanyName;                                    
    else                                                               
        xloResult = xlerrNA;                                           
        
    return xloResult.Ret();
}

This is a standard pattern for a function that uses application-level data.

1. Get a pointer to the application object, using ::XllGetApp().
2. Delegate the business logic to the application object (in this case, by calling the application object's FindStockCode() function).
3. Put the result into the result CXlOper.

The add-in function is now complete and ready for testing.

Next: COM events >>