Many developers of Excel add-in functions would like to write functions whose values are updated under the control of the add-in, not merely when the Excel recalculation engine tells them to.
For example, we may want to write a function GETPRICE(StockCode) which retrieves the latest price for a stock, and automatically recalculates when a price change is sent via a data feed. Or we may want to see the interim results of a simulation being run in another process or even on another machine, without locking up the Excel interface for the entire period of the simulation.
This discussion refers to such functions as asynchronous functions. There are two basic cases where asynchronous functions are useful.
If all the inputs to an Excel function are contained in its arguments, and no external data is used to calculate the result of the function, then the function is a perfect fit for Excel’s calculation model. For example, the SQRT(Number) function needs no information other than the Number input to calculate its result.
On the other hand, some functions rely on external data, which is not passed in as an argument. For example, the Now() function relies on the current time, and is therefore implemented specially.
More commonly, a function like GETPRICE(StockCode) retrieves price data which is not in the spreadsheet. It is still true that Excel will recalculate the formula if we change the value of StockCode, for instance to look at Oracle’s share price in place of IBM’s, but Excel cannot be relied on to recalculate every time the share price of IBM changes.
See the MtBackground sample and MtFeed sample for examples of this type of asynchronous add-in.
Even if all the inputs for a function are contained in its arguments, the function may still benefit from being implemented as an asynchronous function.
For example, a very long slow calculation, requiring millions of iterations, will lock up the entire Excel application until it is complete. If we implement it as an asynchronous function, the user can carry on using their spreadsheets while the calculation completes, and the cell containing the formula will “magically” update when the answer is ready.
See the MtCalc sample for an example of this type of asynchronous add-in.
The asynchronous functions discussed above are very tricky to implement well. Any add-in which supports the asynchronous update behavior discussed above will usually be implemented as a multi-threaded library, and must integrate itself into Excel's threads very carefully.
We have therefore written the XllPush library as an extension of XLL+, to manage all the complex technical issues involved. The topic continues with a discussion of the XllPush libary.