An option value calculator add-in, which demonstrates the use of a results cache to speed up performance
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.
The sample displays the following features:
The following steps were significant in creating and completing the AvgOpt application.
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.
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.
The default menu captions and command name were changed in CAvgOptApp::OnXllOpen; the menu offers the user the option to clear the cache at any time.
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.
The add-in function AvgOptValue had several features added in the XLL+ Function Wizard, by checking four options on the Features tab.
The function was marked as cached, by checking Cache results on the Features tab. Note that no coding on the part of the developer was required to make AvgOptValue a cached function; the Function Wizard generated it completely.
A check was also put against Create asynchronous version",
which caused a second function AvgOptValueAsync to be generated.
This function returns immediately with the result #WAIT!
, after dispatching the calculation to
a background thread.
In the meantime the spreadsheet remains available for use.
The user can move about the sheet, and can even change cells.
When the background thread completes the calculation, it informs Excel, which automatically updates the
affected cells with the result of the calculation.
A check was put against Do not call in Formula Wizard. This is an important behaviour for slow functions. Otherwise, the calculation is run in the Formula Wizard every time the user changes a value.
Thread safe was also checked, to tell Excel that it is safe to call the AvgOptValue function from any number of different threads during calculation. In a spreadsheet which contains more than one instance of the formula, this can improve performance by up to 8 times.
CXlOperCache | CXlOperCache::Save | CXlOperCache::Restore | CXlOperCache::Clear | CXlOper::Format | CXlOper::Ret | CXlMenu | CXlMenu::SetTexts | CXlMenu::AddItem | CXlMenu::Create | CXlMenu::Destroy | CXllApp::WinMessageBox | ::XllGetTypedApp
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.