Quick detour #3: Updates we have made to XLLs
In the past few months, I have written a couple of articles (big grid, multi-threaded calculation) that prompted comments and questions along the lines of “what about XLLs?” Since the email questions have actually been picking up in frequency (I guess a lot of readers are also XLL authors), I thought I would write a quick article that outlines the changes we are making in this area to support new functionality.
Stepping back for a minute, let me briefly explain XLLs. An XLL is a DLL that is written so that Excel can open it directly. XLLs can be used for a number of things, but (in my experience at least) the most common use of XLLs is creating user-defined worksheet functions (UDFs) to supplement Excel’s intrinsic functions. Developers who write XLLs do so for a number of reasons, the paramount being performance.
We have made three changes to XLLs in Excel 12, primarily to give developers access to new functionality. With Excel 12, XLL authors will have support for
- The bigger grid
- More function arguments
- Multi-threaded calculation
Big grid and more arguments
When building an XLL today, developers can use a special Excel data type called an “XLOPER”. An XLOPER is essentially a structure that allows developers to pass data types like references, arrays, and error values to and from Excel (technically, XLOPERs can contain 12 possible data types – if you are curious, here is some documentation on MSDN). Currently, several of the data types (i.e. the one that can be used to communicate a reference to and from Excel) are not big enough to support the dimensions of the bigger grid size in Excel 12.
To address this, Excel 12 will implement a new XLOPER – which will be called something like XLOPER12 – which will have a larger reference (xltypeRef) data type and a larger array (xltypeMulti) data type which will support the entire big grid. In addition, XLOPER12 will support Unicode data and therefore strings larger than 255 characters. One happy by-product of the support for more characters is that XLLs written using XLOPER12 will be able to support 255 arguments, the same limit supported by Excel 12.
The Excel12 function
In order to use XLOPER12, developers will need to use a new C API function – Excel12 – instead of the existing Excel4 function. Excel12 is essentially the same function updated to handle the new XLOPER12, so if developers want to take advantage of any of the changes described above, they will need to update their code to call the new API. Note that existing code will continue to run as it always has, so developers will not face no backwards compatibility problems.
Updating XLL’s to take advantage of multi-threaded calculation
As discussed in a previous post, Excel 12 will support multi-threaded calculation. Since performance is very important for the bulk of XLL authors, we wanted to give developers who write UDFs using XLLs a way to allow their XLLs to participate in multi-threaded calculation (meaning Excel would calc multiple UDFs at once thereby improving calculation times).
To do so, XLL authors need to do two things. First, (the harder part) they need to make sure their XLL UDF (the code they wrote) is threadsafe (i.e. it must not make any non-thread-safe callbacks into Excel). Second, they need to make a minor update to their XLL to tell Excel 12 it is threadsafe. This is done in pretty much the same way that an XLL author would tell Excel that their function is volatile – by adding a special character to the “type text” entry in an XLL’s function table (the type text entry defines the data types for the functions’ return value and arguments).
Specifically, to indicate that an XLL function is safe for multi-threaded calculation, an XLL author needs to add a "$" to the type text entry in an XLL’s function table … so the type text entry for a UDF that had one argument would look like this “ RR$”. If this character is present, Excel will allow the UDF to participate in muti-threaded calc, just like Excel’s intrinsic functions; if the flag is absent, the reverse is true – all calls to that UDF will be run on a single thread. Note, the Excel4 API will also respond appropriately to the “$” character, so if an XLL author wants to take advantage of multi-threading, but doesn't care about the big grid, they do not have to use the Excel12 API.
