Beginning in Excel 14 (Excel 2010), functions can be marked as "Asynchronous". This flag does not mean that the functions immediately return "#WAIT!", run asynchronously and inform Excel when they have finished; see Asynchronous functions for this behavior.
The effect of the new Asynchronous flag is more subtle. If a function is marked as thread-safe then many threads may be running the same function at the same time. If the function makes use of a shared resource, such as a database, then all but one of the threads may be locked out, waiting for the thread currently using the resource to complete.
If the function is also marked as asynchronous, then Excel will call the function and immediately return, without waiting for the result. The branches of the recalculation tree whch are affected by the current cell will not be calculated at this time, but will be queued to be dealt with later. The calculation thread can now be used to resolve some other part of the tree, which is ready to be calculated. It is the responsibility of the add-in function to dispatch the task to a background thread, and to inform Excel when the background thread has completed the task (by calling the SDK call-back function xlAsyncReturn).
Once Excel has received the xlAsyncReturn signal, it knows that the cell is ready, and can continue to calculate the branches of the tree that were queued.
The effect of the behavior on functions which compete for the same resource can be very powerful: performance can improve by 100% or even more.
To create a queued version of a function, use the Features tab of the XLL+ Function Wizard. In the Behavior section, put a check against Create queued version.
An additional version of the function will be generated by the wizard, with the suffix "Queued". For example, a function "GetBid" will have a queued version name "GetBidQueued". The developer is responsible for coding the "GetBid" function, as a normal synchronous function. The wizard will create the queued version, and will write all the thread management code.
After experimenting with the queued version, you may decide that the queued version is more effective than the synchronous version, and that you therefore do not need to offer the synchronous version to the user. At this point you can use the Hide synchronous version flag.
The synchronous version of the function wil be renamed, e.g. "GetBidSync", and will be hidden, so that it does not appear in the Excel Formula Wizard. The queued version will be shown in Excel without the "Queued" suffix, e.g. "GetBid".
The XLL+ Function Wizard generates all the code that implements the asynchronous behavior.
A class will be created that holds a copy of the input values, e.g.
The class is derived from
a base class
CXlAsyncFunctionInputs, which handles the implementation details.
A thread function (e.g.
GetBid_XlAsyncThreadFn) will be created, which will
be run in the background thread.
The thread function simply calls the synchronous version of the function, saves the results,
and signals Excel that the calculation is complete, by calling the xlAsyncReturn SDK call-back.
A wrapper function which implements the queued behavior will also be generated, along
with the code to register it as an add-in function.
The wrapper function packages all the inputs into the class declared above
CGetBid_XlAsyncFunctionInputs), and invokes
the CXlAsyncManager class which will dispatch the task
to a background thread.
Version of Excel earlier than Excel 2010 do not support the "asynchonous" flag, and therefore an additional registration is made if the Excel version is found to be 2007 or earlier. This registers the synchronous version of the function, and no attempt is made to register the queued version of the function. For the user, there is no difference in behavior, only a difference in performance.