Writing Asynchronous User-Defined Functions in Excel 2010

  • Article

Office Visual How To

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

Overview

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.

Code It

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).

LPXLOPER12 WINAPI XllEcho(LPXLOPER12 oper)
{
    // Simulate a long operation.
    Sleep(1000);

    if (oper->xltype & xltypeNum)
        oper->val.num *= 2;
    return oper;
}

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.

void WINAPI XllEchoA(LPXLOPER12 oper, LPXLOPER12 asyncHandle)
{
    // Point to the arguments from a pointer array that will be freed by 
    // XllEchoSetReturn.
    LPXLOPER12* argsArray = new LPXLOPER12[2];
    if (argsArray == NULL)
    {
        AsyncStubFailHelper(asyncHandle);
        return;
    }

    argsArray[0] = TempOper12(oper);
    if (argsArray[0] == NULL)
    {
        delete argsArray;
        AsyncStubFailHelper(asyncHandle);
    }

    argsArray[1] = TempOper12(asyncHandle);
    if (argsArray[1] == NULL)
    {
        xlAutoFree12(argsArray[0]);
        delete argsArray;
        AsyncStubFailHelper(asyncHandle);
    }

    // Simulate an external async operation-start a thread and return.
    if (CreateThread(NULL, 0, XllEchoSetReturn, argsArray, 0, NULL) == NULL)
    {
        xlAutoFree12(argsArray[1]);
        xlAutoFree12(argsArray[0]);
        delete argsArray;
        AsyncStubFailHelper(asyncHandle);
    }
}

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.

DWORD WINAPI XllEchoSetReturn(LPVOID args)
{
    LPXLOPER12* opers = (LPXLOPER12*)args;
    XLOPER12 xlResult;

    // Simulate waiting for a long external operation.
    Sleep(1000);
    if (opers[0]->xltype & xltypeNum)
        opers[0]->val.num *= 2;

    int retval = Excel12(xlAsyncReturn, &xlResult, 2, opers[1], opers[0]);

    // Free the passed pointer array
    // (Excel itself calls xlAutoFree12 to free the XLOPERs, since they have 
    // xlbitDLLFree).  
    delete opers;

    ExitThread(0);
    return 0;
}

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:

  • "Q" represents a pointer to an XLOPER12, an Excel polymorphic type.

  • ">" represents a return type of VOID.

  • "X" represents a callback handle.

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.

int WINAPI xlAutoOpen(void)
{
    // Register XllEcho functions.
    HelpRegister12(L"XllEcho", L"QQ", L"XllEcho", L"Anything");
    HelpRegister12(L"XllEchoA", L">QX", L"XllEchoA", L"Anything");

    return 1;
}

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.

void HelpRegister12(XCHAR* procedure, XCHAR* typeText, XCHAR* functionText, 
    XCHAR* argumentText)
{
    // Create XLOPER12s.
    XLOPER12 xResult;
    XLOPER12 xModuleText;
    Excel12(xlGetName, &xModuleText, 0);
    LPXLOPER12 pxProcedure = TempStr12(procedure);
    LPXLOPER12 pxTypeText = TempStr12(typeText);
    LPXLOPER12 pxFunctionText = TempStr12(functionText);
    LPXLOPER12 pxArgumentText = TempStr12(argumentText);

    // Register the function.
    Excel12(xlfRegister, &xResult, 5, &xModuleText, pxProcedure, 
        pxTypeText, pxFunctionText, pxArgumentText);

    // Clean up the XLOPER12.
    Excel12(xlFree, &xResult, 1, &xModuleText);

    xlAutoFree12(pxProcedure);
    xlAutoFree12(pxTypeText);
    xlAutoFree12(pxFunctionText);
    xlAutoFree12(pxArgumentText);
}
Read It

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.

 See It

Watch the video

> [!VIDEO https://www.microsoft.com/en-us/videoplayer/embed/ff6574ea-18bf-4afb-a3eb-7b935757815f]

Length: 00:07:52

Click to grab code

Grab the Code

Explore It