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()
.
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.
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
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.
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; }
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.
The add-in function is now complete and ready for testing.