Writing Asynchronous User-Defined Functions in Excel 2010
Summary: Learn how to create an asynchronous user-defined function (UDF) within an XLL. Microsoft Excel 2010 introduces the ability to define an asynchronous UDF within an XLL.
Applies to: Excel 2010 | Office 2010 | VBA
Published: June 2010
Provided by: Jonathan Fingold, SDK Bridge, LLC
Microsoft Excel 2010 introduces the ability to create asynchronous UDFs within an XLL. You may want to use an asynchronous UDF if the operation may wait on a query or calculation, which can be an issue for network operations. In such a case, instead of blocking Excel on each call, you could start many asynchronous operations in parallel.
The example bases its asynchronous UDF on the following synchronous UDF, XllEcho. XllEcho simulates an external operation and takes one second to complete a calculation. The function takes a single parameter, sleeps for one second, and returns the original input value (multiplied by 2 if it is a number).
Defining the Aynchronous UDF
The asynchronous UDF, XllEchoA, also takes one pointer to an XLOPER12 parameter, which is the argument in the Excel formula, plus a callback handle parameter. The callback handle parameter is not visible to the user calling the function in an Excel formula; however, Excel passes the first, user-supplied argument, plus the callback handle, to the XLL.
In this example, the asynchronous function makes a copy of the input parameters, spawns a thread to perform the calculation, and returns. In this way, the asynchronous function does not block the Excel thread. Here, the spawned thread is simulating the external operation.
Returning the Aynchronous UDF Result
The XllEchoSetReturn function is the main entry point of the thread that the XllEchoA function spawned. It sleeps for one second, simulating a lengthy, external calculation, then calculates the return value in the same way as the XllEcho function did, notifies Excel of the return value by using the xlAsyncReturn callback function, and then frees the memory for the copied input parameters. The Excel12 function is used to perform the xlAsyncReturn Excel callback which notifies Excel of the asynchronous calculation result.
Registering a UDF
Excel calls the xlAutoOpen function when it loads the XLL file, and this function registers the UDFs.
The typeText parameter of the HelpRegister12 function indicates the return type in the first character of the string and indicates the parameter types in the subsequent characters. The string "QQ" is provided for the typeText parameter when registering the XllEcho UDF, and the string ">QX" for the XllEchaA UDF:
When the asynchronous function completes, the XLL uses the callback handle to return the result of the operation. The callback handle is always the last parameter in the function call, and signifies that the registered function is asynchronous.
In the AsyncUDFDemo project, the helper function, HelpRegister12, is defined to simplify the registration of each UDF. In HelpRegister12, the first parameter, procedure, contains the function name to register. The second parameter, typeText, identifies the return value and parameter types for the function being registered.
Excel 2010 introduces the ability to create asynchronous UDFs within an XLL. An Excel calculation thread calls UDFs in a serial fashion, one after the other, waiting for each call to complete before proceeding down the calculation chain. Asynchronous UDFs return control quickly to the calling thread, and then send the calculation result to Excel later, on a separate thread. Asynchronous UDFs are ideal for any function that takes a long time to complete, but does not actually perform local, intensive processor operations.
For example, a UDF that brings back a stock quote from a public web service may take a fair amount of time per call. If the UDF is synchronous and called many times on a worksheet, a calculation cycle could take a very long time, an order of N times a single call completion. For this case, multithreaded recalculation does not scale well. Multiple threads have their overhead, and it is not practical to define as many threads as you have calls to the UDF on the sheet. However, if the UDF is asynchronous, a calculation cycle would only take an order of 1 times a single call completion.
In Excel 2010, you can code such an operation as an asynchronous UDF. This ability is supported in XLL add-ins, and the new Excel 2010 XLL SDK supports asynchronous UDFs. To do so, separate your UDF into two parts: a synchronous part, which initializes the asynchronous operation and returns immediately, and an asynchronous part, which returns the result to Excel. Excel tracks pending UDF calls that have not yet completed, and continues independent parts of a spreadsheet calculation. An asynchronous result might become available to the add-in through an event, such as an incoming web service result, or some other asynchronous operation completion.
Registering Asynchronous UDFs
To register an asynchronous UDF, designate the return value type (void) with a greater-than sign (>) in the function's type string, and include a new trailing character, "X". "X" designates an XLOPER of type xltypeBigData and represents the asynchronous callback handle.
The callback handle is invisible on the UDF call in the Excel formula. (The Excel model author would call it with the same arguments as for a synchronous UDF.) Excel passes a unique handle to the function for each asynchronous UDF call during a calculation cycle, and uses the handle to track each operation's progress.
Your add-in should keep track of the callback handle for each call to the asynchronous UDF. When the result for the UDF is ready, use the new xlAsyncReturn callback and the callback handle to notify Excel of the result. Once Excel is notified of the result, your application can discard the handle.
> [!VIDEO https://www.microsoft.com/en-us/videoplayer/embed/ff6574ea-18bf-4afb-a3eb-7b935757815f]