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.
Creating an asynchronous function
Let us use a ready-made slow function, AvgOptValue,
which can be found in the AvgOpt
sample application.
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
along with AvgOptValue.
Using an asynchronous function
As far as the user is concerned, there is no difference
between using AvgOptValue and using AvgOptValueAsync.
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
Developing asynchronous functions
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.