Combines the use of object handles, a results cache and asynchronous functions

Overview

This add-in demonstrates the combined use of:

• Object handles
• Asynchronous functions

Requirements

To edit the functions in this add-in, you need to load the extension file RtdHandles.xpe. See Loading an extension file for instructions.

You should also make sure that neither of the following extension files is loaded, since the various types of handles are mutually exclusive.

• NumericHandles.xpe
• StringHandles.xpe
• StringPtrHandles.xpe

In addition, at run-time the RTD Async Function Server DLL, XllRtdLink.dll, should be registered. On a machine where the XLL+ toolkit has been installed, this DLL should already be registered. See Requirements for asynchronous functions for details.

At run-time the RTD Handle Server DLL, RtdHandleServer.dll, should be registered. On a machine where the XLL+ toolkit has been installed, this DLL should already be registered. See Object handles: Requirements and deployment for details.

Object handles

The main calculator function is MonteCarlo. This runs a MonteCarlo simulation and accumulates a set of results. The function returns this object to Excel as a handle, which is a representation of an external object - in this case a MCResults object.

The other functions are "getters" which take an object handle as an argument and return a property of the object:

• MonteCarlo.Value
• MonteCarlo.MinValue
• MonteCarlo.MaxValue
• MonteCarlo.AllValues

For a full discussion of object handles, refer to the topic Object handles in the XLL+ User Guide.

Asynchronous functions

An asynchronous version of MonteCarlo is available: MonteCarloAsync. This function returns #WAIT! when first called, and then dispatches the calculation to a background thread. When the thread completes the calculation the function is called again, and this time it returns a valid handle.

Excel remains available during the background thread calculation, and the user can continue to edit cells or to work in other workbooks, without waiting for the calculation to complete.

For more information on asynchronous functions see Asynchronous functions in the XLL+ User Guide.

Clearing the cache

When clearing the cache, the features must be cleared in the following order:

1. RTD Link - first stop all running worker threads
2. Results cache (if used)
3. Handle cache

In this sample, the code is as follows:

::XllGetTypedApp()->m_rtd.Clear();
::XllGetTypedApp()->m_cache.ClearFunction(L"MonteCarlo");
psl::ClearHandleCache<MCResults>();

Recalculation

Whenever a spreadsheet containing MCResults handles is reopened, you will notice that the formula is recalculated. This may not be desired behavior, particularly for slow expensive functions. See the MonteCarlo2 sample for a solution to this issue.

Classes and functions used

CXlRuntimeException | CXlOper::Ret

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 MonteCarlo.sln or the project file MonteCarlo.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 MonteCarlo.help.xml and MonteCarlo.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 | MonteCarlo2 sample | Using object handles | Asynchronous functions