An option value calculator add-in, which demonstrates the use of a results cache to speed up performance

Overview

This add-in contains a calculator for calculating the value of a complex option. It uses a results cache to speed up performance.

The calculation of the option value is very slow (since it uses a Monte-Carlo algorithm) and it is therefore worthwhile storing the results of calculations in a cache, and retrieving them if they are ever needed again. Since Excel often recalculates cells whose inputs have not changed, this technique offers a significant performance enhancement. The programming cost is zero, since the XLL+ AppWizard and Function Wizard generate and maintain all the code for using the cache.

Features

The sample displays the following features:

• Uses a CXlOperCache to accelerate slow functions.
• Saves the cache to disk and restores it each time the add-in is used.
• Offers the user a menu to clear the cache.

Implementation

The following steps were significant in creating and completing the AvgOpt application.

1. When the AvgOpt project was generated by the XLL+ AppWizard, the Include a cache for results check-box was ticked, so that the cache object in the header file was included automatically. The Save the cache to file check-box was also ticked.
2. The Add a Menu check-box was ticked in the XLL+ AppWizard, so that code to create a menu was added to the project, in AvgOpt.cpp.
3. The default menu captions and command name were changed in CAvgOptApp::OnXllOpen.
4. A new add-in function, AvgOptClearCache, was added, marked as a Macro Function in the XLL+ Function Wizard. The implementation calls the Clear() method of the application's cache object.
5. The add-in function AvgOptValue was marked as cached in the XLL+ Function Wizard, by checking Cache results on the Features tab.
6. Note that no code was required to make AvgOptValue a cached function; the Function Wizard handled it completely.

Classes and functions used

CXlOperCache | CXlOperCache::Save | CXlOperCache::Restore | CXlOperCache::Clear | CXlOper::Format | CXlOper::Ret | CXlMenu | CXlMenu::SetTexts | CXlMenu::AddItem | CXlMenu::Create | CXlMenu::Destroy | CXllApp::WinMessageBox | ::XllGetTypedApp

Sample project

Each sample project is located in a sub-directory of the Samples directory of the XLL+ installation. To use the sample project, open the solution file AvgOpt.sln or the project file AvgOpt.vcproj.

You can enable debugging under Excel by using the Setup Debugging command in the XLL+ ToolWindow.

When delivered, the help files are excluded from the build. You can enable the help build by selecting the files AvgOpt.help.xml and AvgOpt.chm in the Solution Explorer, and using the right-click menu to view Properties. Select the page "Configuration Properties/General" and set the "Excluded from build" property to "No". See Generating help in the User Guide for more information.

See Also

List of Sample Projects