External data

Most Excel add-in functions have no dependencies other than their inputs. So, for example, a formula =FunctionOf(A, B, C) depends only on the values of A, B and C; nothing else can affect the result of the function. So long as A, B and C remain unchanged, there is no need to recalculate the formula.

The efficiency of Excel's recalculation depends on this assumption: cells are only recalculated when they need to be, which greatly improves response time.

However, some functions depend on external inputs, which are not passed as inputs to the function. The most obvious example is the Excel built-in function NOW().

Volatile Functions

Functions such as these are defined as Volatile. A cell containing a volatile function is recalculated whenever Excel recalculates any cell on the same worksheet, or whenever the user presses the F9 key, even if none of its inputs have changed.

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.

A function that uses external data

As an example of a volatile function, let us create a function that uses external 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
Return type:	CXlOper
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:

Type:	String
Name:	Code
Description:	Stock code

Make the function volatile

Finally, click on the Volatile check-box on the Features tab, 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, an in-memory database), we must always assume that this external data has changed since we last called the function, and recalculate the function.

Create the application "database"

Back in Visual Studio, add a simplistic in-memory database to the class definition in Tutorial1.h:

        #include <map>                                                                  
 
class CTutorial1App : public CXllApp
{
public:
    CTutorial1App();

// Names 
public:
    static LPCSTR m_pszDefName;

// Data                                                                         
    std::map<CString, CString> m_mapStockNames;                                
    void InitStockCodeMap();                                                   
    BOOL FindStockCode(const char* pszCode, CString& strCompanyName);          
 
// Overrides
    ...
};

In Tutorial1.cpp, add the implementation code:

// Initialise an application-level in-memory database 
void CTutorial1App::InitStockCodeMap()
{
    // In a real application, these codes would be loaded from a database 
    static const char* apszStockPairs[] = {
        "MSFT", "Microsoft",
        "ORCL", "Oracle",
        "IBM",  "International Business Machines",
        0
    };

    // Clear the map then add each pair
    m_mapStockNames.clear();
    for (int i = 0; apszStockPairs[i]; i += 2)
        m_mapStockNames[apszStockPairs[i]] = apszStockPairs[i + 1];
}

// Look up a stock code 
// Returns FALSE if not found, TRUE if found
BOOL CTutorial1App::FindStockCode(const char* pszCode, CString& strCompanyName)
{
    std::map<CString, CString>::const_iterator itF = m_mapStockNames.find(pszCode);
    if (itF == m_mapStockNames.end())
        return FALSE;
    strCompanyName = itF->second;
    return TRUE;
}

We need to make sure that the "database" is initialized, so add a call to InitStockCodeMap() to OnXllOpen():

BOOL CTutorial1App::OnXllOpenEx()
{
    ...
    InitStockCodeMap(); 
    return TRUE;
}

Use the application data

Finally, change the generated code as shown below:

CXlOper* STOCKNAME_Impl(CXlOper& xloResult, const CXlStringArg& Code)
{
    // End of generated code 
//}}XLP_SRC 
 
    // Look up the code and return the company name or #N/A!           
    CString strCompanyName;                                            
    if (XllGetTypedApp()->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 ::XllGetTypedApp().
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: Extended types >>