If a function takes a very long time to complete, Excel is locked and unusable in the meantime. It is sometimes useful to run the function asynchronously, in a separate thread, so that Excel remains available while the function completes. While the calculation is continuing, the target cell will display "#WAIT!", and when it is complete, the result will automatically be updated to display the result.
The XLL+ Function Wizard now lets you create an asynchronous version of a function with no coding.
Let us use a ready-made slow function,
which can be found in the AvgOpt
This function runs a MonteCarlo simulation to calculate the value of an average price option, and can take a very long time to run.
Adding an asynchronous version of the function is very simple: just open up the function in Xll+ Function Wizard and put a check against "Create asynchronous version" on the Features tab:
The Wizard will now create an extra add-in function,
AvgOptValueAsync, which will be available in Excel
As far as the user is concerned, there is no difference
AvgOptValue and using
When the function is called, it will initially return "#WAIT!".
Excel will remain usable, and the user can continue to move around the workbook and make changes to it.
Once the function has completed, the cell will automatically be recalculated, and this time it will return the result.
Note: in versions 6.0.0 to 6.0.4 of XLL+, asynchronous functions required an extra argument, RtdSignalCell. Since XLL+ 6.2, this argument in no longer needed, after an enhancement of the implementation of asynchronous functions. See Upgrading asynchronous functions from XLL+ 6.0 for more information
Before you begin using or building the sample code, make sure that your development platform and target environment will support the model: check the requirements in Requirements for asynchronous functions.