Developing Add-ins (XLLs) in Excel 2007

Summary: Learn about Microsoft Office Excel 2007 features that affect XLL add-ins and enable new XLL functionality, and also changes to the XLL C API itself. (23 printed pages)

Steve Dalton, Eigensys Ltd.

October 2006

Updated: September 2007

Applies to: 2007 Microsoft Office System, Microsoft Office Excel 2007

Contents

  • Developing XLLs in Excel 2007

  • Overview of Excel 2007 XLL-Related Features

  • Overview of XLLs

  • Changes to XLLs in Excel 2007

  • Writing Cross-Version XLLs

  • Writing Thread-Safe XLLs and Worksheet Functions

  • Conclusion

  • Additional Resources

Download the Excel 2007 XLL Software Development Kit.

Developing XLLs in Excel 2007

The intended audience for this article consists of C and C++ developers who already have experience developing Microsoft Office Excel add-ins, or XLLs. This article is not an introduction to XLL development although a brief overview is included. To make the most of this article, readers should be familiar with:

  • C and C++ language concepts and constructs. Code examples are written in C++.

  • Building DLLs that export functions.

  • The XLOPER data structure and other Excel data types, such as the floating-point matrix structure (FP).

  • The add-in manager interface functions, such as xlAutoOpen and xlAutoClose.

  • XLOPER memory management (xlAutoFree, xlFree, and the use of xlbitDLLFree and xlbitXLFree).

    Important noteImportant

    The functionality described in this article is now available in the Microsoft Office Excel 2007 XLL Software Development Kit (SDK). The Excel 2007 XLL SDK is available as a download from the Microsoft Download Center.

The most obvious change to XLL-related features in Microsoft Office Excel 2007 is that the worksheet is expanded from 224 to 234 cells, or to be more precise, from 256 columns x 65,536 rows (28 x 216) to 16,384 x 1,048,576 (214 x 220). These new limits overflow the integer types that contain them in the old range and array structures. This change requires new data structures with wider integers to define the size of ranges and arrays.

The maximum number of arguments a function can take increases from 30 to 255. Additionally, XLLs can now exchange long Unicode strings with Excel instead of just limited-length byte strings.

Multithreaded workbook recalculation is supported on single-processor and multiprocessor computers. Unlike Microsoft Visual Basic for Applications (VBA) User-Defined Functions (UDFs), you can register XLL UDFs as thread-safe. Like the majority of the built-in worksheet functions in Excel, you can assign them to concurrent threads to accelerate recalculation. This benefit comes at the expense of some restrictions, and also a responsibility not to abuse the multithreading privileges with unsafe behavior.

The Analysis Toolpak functions are now fully integrated into Excel, although the add-in is still required for the Data Analysis tools. This creates an area of incompatibility for XLLs developed for earlier versions that call ATP functions using xlUDF.

The user interface also changes dramatically. Customization of the user interface is beyond the scope of this article, other than to say that old XLL custom menus and menu items are still enabled but are not placed where the old C application programming interface (API) functions intended.

Overview of XLLs

Since Microsoft Excel 4.0, the linking of XLLs to Excel has been supported. In addition, an interface (known as the C API), through which the XLL can access Excel functions and commands, is also supported. XLL is the name for a DLL that contains the callbacks required by the Excel Add-in Manager, and also the XLL's exported commands and worksheet functions. This interface has not changed significantly since Excel 5.0. Many of the new features of Excel 2007, and some features of earlier versions that were not previously supported, are now available in an updated C API. This article reviews additions to the new C API and discusses some transition issues that developers face.

Microsoft published a software development kit (SDK) with Excel 97, including the following components:

  • A C header file, xlcall.h, containing definitions of data structures used by Excel to exchange data with XLLs; enumerated function and command definitions corresponding to the built-in Excel worksheet functions, XLM information functions and many commands; and prototypes for the Excel callback functions Excel4, Excel4v, and XLCallVer.

  • A static import library, xlcall32.lib. The Excel callbacks are exported from this library with C name decoration.

  • An XLL SDK Framework project containing a complete XLL project and a number of routines for handling Excel data types and assisting with calls to Excel4 and Excel4v.

The new version of the Excel 2007 XLL SDK is available on the Microsoft Download Center.

The simplest XLL is one that exports a function called by Excel when the add-in is loaded: xlAutoOpen. This function performs initialization tasks and registers any functions and commands the XLL exports. This entails the add-in calling the C API equivalent of the XLM function REGISTER(). An Excel library, xlcall32.dll, exports functions that enable XLLs to call back into Excel: Excel4 and Excel4v (reflecting that it was version 4 when XLL functionality was introduced), and now supplemented by Excel12 and Excel12v in Excel 2007.

The Excel Add-in Manager loads and manages XLLs. It looks for the following XLL exports:

  • xlAutoOpen: Called when the XLL is loaded. The ideal place to register XLL functions and commands, initialize data structures, and customize the user interface.

  • xlAutoClose: Called when the XLL is unloaded. The place to unregister functions and commands, release resources, and undo customizations.

  • xlAutoAdd: Called when the XLL is activated or loaded during a session.

  • xlAutoRemove: Called when the XLL is inactivated or unloaded during a session.

  • xlAddInManagerInfo (xlAddInManagerInfo12): Called when the add-in manager is invoked for the first time. If passed an argument = 1, it returns a string (the name of the add-in), otherwise it should return #VALUE!

  • xlAutoRegister (xlAutoRegister12): Called when REGISTER (XLM) or xlfRegister (C API) is called without the function's return and argument types. It searches the XLL internally to register the function with this information supplied.

  • xlAutoFree (xlAutoFree12): Called when Excel is returned an XLOPER flagged as pointing to memory that the XLL needs to release.

The last three functions accept or return XLOPERs. In Excel 2007, they are supported by both XLOPER and XLOPER12.

The only required function is xlAutoOpen, without which the XLL does not load. When you are allocating memory or other resources within your DLL, you should also implement xlFree (xlFree12) to avoid memory leaks. You should implement xlAutoClose to clean up when the XLL is unloaded. The others can all be omitted.

The C API is so-called for a reason: Excel exchanges data using some standard C data types; the library functions are C name decorated; and the data structures are ANSI C. With the appropriate experience, using C++ brings more manageability, readability, and stability to an XLL project. Therefore, the remaining portion of the article assumes a basic understanding of C++ classes.

The data structures used by Excel to exchange data with XLLs are summarized in Table 1, which also provides the type letter used in the third argument to xlfRegister when registering worksheet UDFs.

Table 1. Excel Data Structures used to exchange data with XLLs

Data Type

Pass by Value

Pass by Ref (Pointer)

Comments

Boolean

A

L

short (0=false or 1=true)

Double

B

E

char *

C, F

Null-terminated ASCII byte string

unsigned char *

D, G

Counted ASCII byte string

[v12+] unsigned short *

C%, F%

Null-terminated Unicode wide-char string

[v12+] unsigned short *

D%, G%

Counted Unicode wide character string

unsigned short [int]

H

DWORD, size_t, wchar_t

[signed] short [int]

I

M

16-bit

[signed long] int

J

N

32-bit

FP

K

Floating-point array structure

[v12+] FP12

K%

Larger grid floating-point array structure

XLOPER

P

Variable-type worksheet values and arrays

R

Values, arrays and range references

[v12+] XLOPER12

Q

Variable-type worksheet values and arrays

U

Values, arrays and range references

The types C%, F%, D%, G%, K%, Q, and U are all new in Excel 2007 and are not supported in earlier versions. The string types F, F%, G, and G% are used for arguments that are modified-in-place. When XLOPER or XLOPER12 UDF function arguments are registered as types P or Q respectively, Excel converts single-cell references to simple values and multi-cell references to arrays when preparing these arguments. P and Q types always arrive in your function as one of the following types: xltypeNum, xltypeStr, xltypeBool, xltypeErr, xltypeMulti, xltypeMissing, or xltypeNil, but not xltypeRef or xltypeSRef because these are always dereferenced.

Argument 3 to xlfRegister, type_text, is a string of the previous codes. This string can also be suffixed by a number sign (#)that indicates that the function is a macro-sheet equivalent. The string can also be suffixed by an exclamation point (!) indicating that the function is a macro-sheet equivalent and/or that it is to be treated as volatile. Declaring functions as macro-sheet equivalents enables them to get the value of unrecalculated cells (including the current value of the calling cell or cells) and to call XLM information functions. Functions registered as # and as taking R or U type arguments are volatile by default.

Excel 2007 also allows you to append a dollar sign symbol ($) to indicate that the function is thread-safe. However, macro-sheet functions are not considered thread-safe. Therefore, you cannot append both # and $ signs to a function's type type_text string. If an XLL attempts to register a function with both # and $, it fails.

Changes to XLLs in Excel 2007

Excel 2007 loads and runs any add-in created for earlier versions. This does not mean that every XLL runs as intended in Excel 2007. Before you consider an XLL to be fully compatible with Excel 2007, there are some hazards to be avoided. This section reviews some of the implicit or explicit assumptions that may no longer be true.

One of the two major changes that the new structures address is the introduction of larger grids, for which rows and columns are counted with two new data typedefs:

typedef INT32 RW;        /* XL 12 Row */
typedef INT32 COL;        /* XL 12 Column */

Used in the new XLOPER12 and FP12 structures, these signed 32-bit integers replace the WORD rows and BYTE columns used in XLOPER ranges and the WORD rows used in XLOPER arrays and the FP data structure. The other major change is that Unicode strings are now supported in XLLs. The XLOPER12 is just an XLOPER that includes the RW and COL types, and where the ASCII byte string is replaced by a Unicode string.

New Worksheet Functions

Analysis Toolpak (ATP) functions are now part of Excel 2007. Previously an XLL called an ATP add-in function using xlUDF. In Excel 2007, you should replace such a call with a call to xlfPrice, for example. There are also many new worksheet functions that you can call only when running Excel 2007. The C API returns xlretInvXlfn if these are called in earlier versions. For more information, see Writing Cross-Version XLLs.

Strings

For the first time, Excel 2007 gives XLLs direct access to wide character Unicode strings of up to 32,767 (215–1) characters long. These strings have been supported in worksheets for several versions now. This is a huge improvement on the previous C API where strings could not exceed 255 ASCII bytes. Byte strings are still supported, still with the same length limits, through the C, D, F, and G argument types and the XLOPER xltypeStr.

In Microsoft Windows, conversion between byte strings and Unicode strings is locale-dependent. This means that the 255-byte characters are converted up to and down from wide Unicode characters in a way that depends on the locale settings of the system. The Unicode standard assigns unique characters to each code, but this is not true of extended ASCII codes. You should remember this locale-specific conversion. For example, it is possible for two unequal Unicode strings to compare equal after conversion to byte strings.

In Excel 2007, any string that the user sees is typically represented internally in Unicode. Therefore, the most efficient way to exchange strings in Excel 2007 is to use these. Earlier versions only allow you to access byte strings when interacting through the C API, although you can work with wide Unicode strings through string Variants. These can be passed to a DLL or XLL from VBA or by using the Excel 2007 COM interface. When running Excel 2007, you should try to work with Unicode strings whenever possible.

String Types Available to the Excel C API

Table 2 shows the C API xltypeStr XLOPERs.

Table 2. C API xltypeStr XLOPERs

Byte Strings: XLOPER

Wide Character Strings: XLOPER12

All versions of Excel

Excel 2007+ only

Max length: 255 extended ASCII bytes

Maximum length 32,767 Unicode chars

First (unsigned) byte = length

First wide character = length

Important noteImportant

Do not assume null-termination.

Table 3 shows the C/C++ strings.

Table 3. C/C++ strings

Byte Strings

Wide Character Strings

Null-terminated (char *) "C"
Max length: 255 extended ASCII bytes

Null-terminated (wchar_t *) "C%" Maximum length 32,767 Unicode chars

Length-counted (unsigned char *) "D"

Length-counted (wchar_t *) "D%"

Converting One String Type to Another

The addition of new string types to XLLs creates the possibility that you might need to convert byte-strings to wide-char or wide-char strings to byte-strings.

When copying strings, ensure that the source string is not too long for the destination string buffer. It is a matter of implementation whether to fail or truncate in this case. Table 4 shows the conversion and copying library routines.

Table 4. Conversion and copying library routines

Conversion and copying library routines

Note that all the library functions shown in table 4 take a (maximum) string length argument. You should always supply this to avoid over-running the Excel-limited buffers.

Consider the following:

  • When working with length-counted byte strings declared as [signed] char *, cast the length to BYTE to avoid negative results for strings longer than 127.

  • When copying a maximum-length null-terminated string to a length-counted string buffer, do not copy the null termination character because this could over-run the buffer.

  • When allocating new memory for null-terminated strings, allocate space for the null termination.

  • Set the null termination explicitly when copying length-counted strings to null-terminated strings, unless you are using an API function that always does this for you, such as lstrcpynA().

  • When speed is important, and you know the number of bytes you are copying, use memcpy() instead of strcpy(), strncpy(), wcscpy(), or wcsncpy().

The following functions safely convert a length-counted byte string to a null-terminated C byte string, and a null-terminated C byte string to a length-counted byte string. The first assumes a big enough target buffer is passed, and the second at most a 256-byte buffer (including the length byte):

char *BcountToC(char *cStr, const char *bcntStr)
{
    BYTE cs_len = (BYTE)bcntStr[0];
    if(cs_len) memcpy(cStr, bcntStr+1, cs_len);
    cStr[cs_len] = '\0';
    return cStr;
}
#define MAX_XL4_STR_LEN 255u
char *CToBcount(char *bcntStr, const char *cStr)
{
    size_t cs_len = strlen(cStr);
    if(cs_len > MAX_XL4_STR_LEN) cs_len = MAX_XL4_STR_LEN;
    memcpy(bcntStr+1, cStr, cs_len);
    bcntStr[0] = (BYTE)cs_len;
    return bcntStr;
}

Implications of Larger Grids in Code

In Microsoft Office Excel 2003, the maximum size of a single-block range is 224 cells, which is well within the limits of a 32-bit integer. In Excel 2007, the limit is 234 cells. The two-gigabyte memory limit that all applications are limited to is reached with a simple array of doubles at about 228 cells, so that recording the size of an xltypeMulti array or an FP or FP12 with a signed 32-bit integer is safe. However, to get the size of a huge range safely, such as the entire Excel 2007 grid, you need a 64-integer type such as __int64 or INT64.

Range Names

The rules determining what is and is not a valid range name change with Excel 2007. The maximum column is now XFD. For example, the formula =RNG1 is now interpreted as a reference to the first cell in the 371st column unless the workbook is running in Compatibility mode, that is, when an Excel 2003 format workbook is opened. If a user saves a 2003 workbook in the Excel 2007 format, Excel redefines names such as RNG1 as _RNG1 and alerts the user to that change. All workbook occurrences are replaced except those in VBA code, breaking that code and possibly external references in other workbooks. You should check any code that creates, validates, or looks for such names. One way to modify code so that it functions in both saved formats is to look for _RNG1 if RNG1 is not defined.

Handling Large Arrays and Out-of-Memory Conditions

In Excel 2007, XLOPER arrays (xltypeMulti) that are coerced XLOPER range types are limited in size by the Excel 32-bit address space rather than by the number of rows and columns or the width of the integers used to count them. With its much larger grids, Excel 2007 can hit this memory limit more easily. Attempts to coerce a range reference explicitly within code using xlCoerce, or implicitly by registering exported function XLOPER arguments as type P, or XLOPER12s as type Q, fail if the range is too large for the available memory. In the first case, a call to Excel4 or Excel12 fails with xlretFailed. In the latter case, the Excel returns #VALUE! to the calling cell.

For performance reasons, where there is a significant risk that a user may pass a huge array to your add-in, you should either detect the size of the array and reject it if over a certain limit, or, you should enable user-breaks of worksheet functions using xlAbort.

Handling Huge Range References and Recalculation

In Excel 2003, a reference could point to, at most, all 224 cells on a worksheet. Even if you were processing just a fraction of these, you could effectively hang the computer from the user's point of view. You should, therefore, check range size to gauge whether to process in smaller chunks and, as with the processing of large arrays, detect user-breaks using xlAbort. With Excel 2007, ranges can be up to roughly 1,000 times larger, so careful checking is even more important. A command that might take one second to process the entire worksheet in Excel 2003 might, all other things being equal, take over 15 minutes in Excel 2007.

More Function Arguments

Excel 2007 allows XLLs to export functions with up to 255 arguments. In practice, a function with that number of arguments, each with a distinct meaning, is probably over-complex and should be broken down. This number of arguments is much more likely to be used by a function that processes a variable number of similar types of input.

Multithreaded Recalculation

You can configure Excel 2007 to use multiple threads to recalculate worksheet functions that are registered as thread-safe. This can lead to a reduction in calculation times on multiprocessor computers, but can also be useful on single-processor computers, especially where UDFs are used to access functionality on a multithreaded server. One advantage of XLLs over VBA, COM, and C# add-ins is that they can register their functions as thread-safe.

Access to New Commands and Worksheet Functions

The enumerated function definitions are extended to include all the worksheet functions that are added since Excel 97 (version 9) and several new commands. The new functions are shown in Table 5.

Table 5. New functions

xlfAccrint

xlfCumprinc

xlfImlog10

xlfQuotient

xlfAccrintm

xlfDec2bin

xlfImlog2

xlfRandbetween

xlfAmordegrc

xlfDec2hex

xlfImpower

xlfReceived

xlfAmorlinc

xlfDec2oct

xlfImproduct

xlfRoundbahtdown

xlfAveragea

xlfDelta

xlfImreal

xlfRoundbahtup

xlfAverageif

xlfDisc

xlfImsin

xlfRtd

xlfAverageifs

xlfDollarde

xlfImsqrt

xlfSeriessum

xlfBahttext

xlfDollarfr

xlfImsub

xlfSqrtpi

xlfBesseli

xlfDuration

xlfImsum

xlfStdeva

xlfBesselj

xlfEdate

xlfIntrate

xlfStdevpa

xlfBesselk

xlfEffect

xlfIseven

xlfSumifs

xlfBessely

xlfEomonth

xlfIsodd

xlfTbilleq

xlfBin2dec

xlfErf

xlfIsthaidigit

xlfTbillprice

xlfBin2hex

xlfErfc

xlfLcm

xlfTbillyield

xlfBin2oct

xlfFactdouble

xlfMaxa

xlfThaidayofweek

xlfComplex

xlfFvschedule

xlfMduration

xlfThaidigit

xlfConvert

xlfGcd

xlfMina

xlfThaimonthofyear

xlfCountifs

xlfGestep

xlfMround

xlfThainumsound

xlfCoupdaybs

xlfGetpivotdata

xlfMultinomial

xlfThainumstring

xlfCoupdays

xlfHex2bin

xlfNetworkdays

xlfThaistringlength

xlfCoupdaysnc

xlfHex2dec

xlfNominal

xlfThaiyear

xlfCoupncd

xlfHex2oct

xlfOct2bin

xlfVara

xlfCoupnum

xlfHyperlink

xlfOct2dec

xlfVarpa

xlfCouppcd

xlfIferror

xlfOct2hex

xlfViewGet

xlfCubekpimember

xlfImabs

xlfOddfprice

xlfWeeknum

xlfCubemember

xlfImaginary

xlfOddfyield

xlfWorkday

xlfCubememberproperty

xlfImargument

xlfOddlprice

xlfXirr

xlfCuberankedmember

xlfImconjugate

xlfOddlyield

xlfXnpv

xlfCubeset

xlfImcos

xlfPhonetic

xlfYearfrac

xlfCubesetcount

xlfImdiv

xlfPrice

xlfYield

xlfCubevalue

xlfImexp

xlfPricedisc

xlfYielddisc

xlfCumipmt

xlfImln

xlfPricemat

xlfYieldmat

The new commands are shown in Table 6.

Table 6. New commands

xlcActivateNotes

xlcInsertdatatable

xlcOptionsSettings

xlcUnprotectRevisions

xlcAddPrintArea

xlcInsertMapObject

xlcOptionsSpell

xlcVbaactivate

xlcAutocorrect

xlcLayout

xlcPicklist

xlcViewDefine

xlcClearPrintArea

xlcMoveBrk

xlcPivotTableChart

xlcViewDelete

xlcDeleteNote

xlcNewwebquery

xlcPostDocument

xlcViewShow

xlcEditodc

xlcNormal

xlcProtectRevisions

xlcWebPublish

xlcHideallInkannots

xlcOptionsMe

xlcRmPrintArea

xlcWorkgroupOptions

xlcHideallNotes

xlcOptionsMenono

xlcSheetBackground

xlcHidecurrNote

xlcOptionsSave

xlcTraverseNotes

The commands shown in Table 7 have been removed.

Table 7. Removed commands

xlcStart

xlcVbaObjectBrowser

xlcVbaAddWatch

xlcVbaReferences

xlcVbaClearBreakpoints

xlcVbaReset

xlcVbaDebugWindow

xlcVbaStepInto

xlcVbaEditWatch

xlcVbaStepOver

xlcVbaEnd

xlcVbaToggleBreakpoint

xlcVbaInstantWatch

C API Functions: Excel4, Excel4v, Excel12, Excel12v

Experienced XLL developers should be very familiar with the old C API functions:

int _cdecl Excel4(int xlfn, LPXLOPER operRes, int count,... ); 
/* followed by count LPXLOPERs */
int pascal Excel4v(int xlfn, LPXLOPER operRes, int count, LPXLOPER opers[]);

In Excel 2007, the new SDK also includes a source code module that contains definitions of two more C API functions that work identically but take XLOPER12 arguments. If these functions are called in an earlier version of Excel they both return xlretFailed:

int _cdecl Excel12(int xlfn, LPXLOPER12 operRes, int count,... ); 
/* followed by count LPXLOPER12s */
int pascal Excel12v(int xlfn, LPXLOPER12 operRes, int count, LPXLOPER12 opers[]);

Both functions return the same success and error values as Excel4 and Excel4v in earlier versions, but in Excel 2007 all four can return a new error: xlretNotThreadSafe, defined as 128. This is returned whenever a function registered as thread-safe attempts to call a function that is not thread-safe, typically a macro-sheet function or an unsafe UDF.

The Add-in Manager Interface Functions

A couple of the xlAuto- functions take or return XLOPERs. These still function as expected in Excel 2007, but XLOPER12 versions are now also recognized. The functions affected are as follows:

xloper * __stdcall xlAutoRegister(xloper *p_name);
xloper * __stdcall xlAddInManagerInfo(xloper *p_arg);

The new functions are as follows:

xloper12 * __stdcall xlAutoRegister12(xloper12 *p_name);
xloper12 * __stdcall xlAddInManagerInfo12(xloper12 *p_arg);

None of these four functions is required, and Excel uses default behavior if they are not present. In Excel 2007, if the XLOPER12 versions are present they are called in preference to the XLOPER versions. When you are creating multi-version XLLs you should ensure that the two versions function equivalently.

Floating-Point Matrix Types

The following structure, registered as type K, has been supported in Excel for many versions. The structure is defined in the Excel 2007 xlcall.h SDK header file:

typedef struct
{
    WORD rows;
    WORD columns;
    double array[1]; // Start of array[rows * columns]
}
    FP;

Here is an example of the kind of assumption that could cause problems. Before Excel 2007, you could assume that the following code was safe, although admittedly poor style:

// Unsafe function: returns the offset (from 0) of the column with max sum.
int __stdcall max_column_index(FP *pArray)
{
    int c, columns = pArray->columns;
    int r, rows = pArray->rows;
    double *p, column_sums[256]; // Explicit assumption!!!

    for(c = 0; c < columns; c++)
        column_sums[c] = 0.0; // Overrun possible if columns > 256!!!

    for(r = 0, p = pArray->array; r < rows; r++)
        for(c = 0; c < columns; c++)
            column_sums[c] += *p++; // overrun possible!!!

    int max_index = 0;
    for(c = 1; c < columns; c++)
        if(column_sums[c] > column_sums[max_index]) // overrun!!!
            max_index = c;
    return max_index;
}

Even though the FP type is not one of the new data types, in Excel 2007 this structure accommodates arrays that span the entire width of the new grid (214 columns), but are truncated to 216 rows at most. In this case, the fix is very simple: dynamically allocate a buffer that is always the right size:

    double *column_sums = new double[columns];
// ...
    delete[] column_sums;
    return max_index;

To enable more than 216 rows to be passed, Excel 2007 also supports a new data type, registered as K%:

typedef struct
{
    RW rows;
    COLS columns;
    double array[1]; // Start of array[rows * columns]
}
    FP12;

XLCallVer

The signature for the XLCallVer function is as follows:

int pascal XLCallVer(void);

In Microsoft Excel 97 through Excel 2003, XLCallVer returns 1280 = 0x0500 = 5*256, which indicates Excel version 5 (the last time any change was made to the C API). In Excel 2007, it returns 0x0C00, which similarly indicates version 12.

Although you can use this to determine whether the new C API is available at run time, you may prefer to detect the running version of Excel using Excel4(xlfGetWorkspace, &version, 1, &arg), where arg is a numeric XLOPER set to 2 and version is a string XLOPER, which can then be coerced to an integer. This is because there are differences between Microsoft Excel 2000, Microsoft Excel 2002, and Excel 2003 that your add-in may also need to detect. For example, changes were made to the accuracy of some of the statistics functions and you may need to detect this.

C API Functions That Behave Differently in Different Versions

Generally, worksheet functions do not change the way they work from version to version, although numeric functions may improve accuracy. For the C API-only functions, however, the following three work differently in Excel 2007 than in earlier versions.

xlStack

This function now returns either the actual stack space or 64Kbytes, whichever is the lesser. The following code sample demonstrates how to get the stack space in any version.

double __stdcall get_stack(void)
{
    if(gExcelVersion12plus)
    {
        xloper12 retval;
        if(xlretSuccess != Excel12(xlStack, &retval, 0))
            return -1.0;

        if(retval.xltype == xltypeInt)
            return (double)retval.val.w; // returns min(64Kb, actual space)
// This is not the returned type, but was returned in
// an Excel 12 beta, so the code is left here.
        if(retval.xltype == xltypeNum)
            return retval.val.num;
    }
    else
    {
        xloper retval;
        if(xlretSuccess != Excel4(xlStack, &retval, 0))
            return -1.0;

        if(retval.xltype == xltypeInt)
            return (double)(unsigned short)retval.val.w;
    }
    return -1.0;
}

xlGetHwnd

In Excel 2003, xlGetHwnd returns an xltypeInt XLOPER containing a 2-byte short, the low part of the full Windows HWND handle for Excel. The full handle must be obtained using the Windows API EnumWindows as shown in this section. In Excel 2007, when called using Excel12, the returned xltypeInt XLOPER12 contains a 4-byte signed integer which is the full handle. Note that even when called in Excel 2007, Excel4 only returns the low part of the handle.

HWND get_xl_main_handle(void)
{
    if(gExcelVersion12plus) // xlGetHwnd returns full handle
    {
        xloper12 main_xl_handle;
        if(Excel12(xlGetHwnd, &main_xl_handle, 0) != xlretSuccess)
            return 0;
        return (HWND)main_xl_handle.val.w;
    }
    else // xlGetHwnd returns low handle only
    {
        xloper main_xl_handle;
        if(Excel4(xlGetHwnd, &main_xl_handle, 0) != xlretSuccess)
            return 0;
        get_hwnd_enum_struct eproc_param = {main_xl_handle.val.w, 0};
        EnumWindows((WNDENUMPROC)get_hwnd_enum_proc, (LPARAM)&eproc_param);
        return eproc_param.full_handle;
    }
}

#define CLASS_NAME_BUFFER_SIZE    50

typedef struct
{
    short main_xl_handle;
    HWND full_handle;
}
    get_hwnd_struct;

// The callback function called by Windows for every top-level window
BOOL __stdcall get_hwnd_enum_proc(HWND hwnd, get_hwnd_struct *p_enum)
{
// Check if the low word of the handle matches Excel's
    if(LOWORD((DWORD)hwnd) != p_enum->main_xl_handle)
        return TRUE; // keep iterating

    char class_name[CLASS_NAME_BUFFER_SIZE + 1];
// Ensure that class_name is always null terminated
    class_name[CLASS_NAME_BUFFER_SIZE] = 0;
    GetClassName(hwnd, class_name, CLASS_NAME_BUFFER_SIZE);
// Do a case-insensitive comparison for the Excel main window class name
    if(_stricmp(class_name, "xlmain") == 0)
    {
        p_enum->full_handle = hwnd;
        return FALSE; // Tells Windows to stop iterating
    }
    return TRUE; // Tells Windows to continue iterating
}

xlGetInst

As with xlGetHwnd, when called using Excel12, the returned xltypeInt XLOPER12 contains the full running instance handle, whereas, Excel4 returns an xltypeInt XLOPER containing only the low part of the handle.

User Interface Customization

In earlier versions of Excel, you could use XLL code to customize menu bars, menus, command bars, or toolbars using commands such as xlcAddBar, xlcAddMenu, xlcAddCommand, xlcShowBar, xlcAddToolbar, xlcAddTool, and xlcShowToolbar. These commands are still supported but, because the old menu-bar and command-bar structures are replaced, they place access to your XLL commands in the Add-in group of the Ribbon and therefore may not give your users the intended interface.

You can only customize the UI in the 2007 Microsoft Office release using managed code. One approach to UI customization in Excel 2007 is to have a separate managed code resource or add-in, within which the functions that customize the UI reside. You can then tightly couple it to your XLL, calling back into your XLL code to invoke the commands and functions it contains.

Writing Cross-Version XLLs

The following sections review how to write XLLs that are compatible across multiple versions of Excel.

Some Useful Constant Definitions

You should consider including definitions such as these in your XLL project code and replacing all instances of literal numbers used in this context. This will greatly clarify code that is version-specific, and reduce the probability of version-related bugs in the form of innocuous-looking numbers.

#define MAX_XL11_ROWS        65536
#define MAX_XL11_COLS        256
#define MAX_XL12_ROWS        1048576
#define MAX_XL12_COLS        16384
#define MAX_XL11_UDF_ARGS    30
#define MAX_XL12_UDF_ARGS    255
#define MAX_XL4_STR_LEN        255u
#define MAX_XL12_STR_LEN    32767u

Getting the Running Version

You should detect which version is running using Excel4(xlfGetWorkspace, &version, 1, & arg), where arg is a numeric XLOPER set to 2 and version is a string XLOPER which can then be coerced to an integer. For Excel 2007, this is 12. You should do this in, or from, xlAutoOpen. You can also call XLCallVer, but this does not indicate which of the pre-2007 versions you are running.

Linking to the xlcall32 Library and the C API Functions

Based on the Excel 97 SDK and Framework project, the standard way of linking to the xlcall32 library is to include a reference in the project to the xlcall32.lib import library. (Alternatively, you can link explicitly to xlcall32.dll at run-time using LoadLibrary and GetProcAddress.) For projects built like this, the library is linked at compile time and its exports are prototyped in the usual manner. For example:

#ifdef __cplusplus
extern "C" {
#endif
int _cdecl Excel4(int xlfn, LPXLOPER operRes, int count,... );
//...
#ifdef __cplusplus
} // extern "C" block end
#endif

At run time, when the XLL is loaded by Excel, it is implicitly linked to xlcall32.dll.

Any add-in that was built (linked at compile-time) with an earlier version of the import library runs with Excel 2007, but cannot access the Excel12 and Excel12v callbacks because they are not defined. Code that uses the Excel 2007 SDK version of xlcall.h and C++ source file [name?].cpp and which is linked to the Excel 2007 version of xlcall32.lib, can safely invoke these functions in all recent versions of Excel. If called from an earlier version than Excel 2007, they just return xlretFailed. This is only a fail-safe, so you should ensure that your code is aware of the running version and calls the appropriate callback.

Creating Add-ins That Export Dual Interfaces

Consider an XLL function that takes a string and returns a single argument that can be any of the worksheet data types or a range. In Excel 2003 and Excel 2007 you could export a function registered as type RD and prototyped as follows, where the string is passed as a length-counted byte string:

xloper * __stdcall my_xll_fn(unsigned char *arg);

First, this functions correctly in all recent versions of Excel but is subject to the limitations of the old C API strings. Second, although Excel 2007 can pass and accept XLOPERs, internally it converts them to XLOPER12s, so there is an implicit conversion overhead in Excel 2007 that is not there when the code runs in Excel 2003. Third, maybe this function can be made thread-safe, but if the type string is changed to RD$ registration fails in Excel 2003. For all these reasons, ideally, you should export a function for your Excel 2007 users that was registered as UD%$ and prototyped as follows:

xloper12 * __stdcall my_xll_fn_v12(wchar_t *arg);

Another reason why you may want to register a different function when running Excel 2007 is that it allows XLL functions to take up to 255 arguments (the old limit is 30). Fortunately, you can have the benefits of both by exporting both versions from your project. Then you detect the running Excel version and conditionally register the most appropriate function.

There are many ways that you can manage the data passed when registering the XLL's exports within a project.

One simple way is to define a data structure, called ws_func_export_data for example as in the following code, and then declare and initialize an array of ws_func_export_data which can then be used by your XLL code to initialize the XLOPERs or XLOPER12s passed to xlfRegister. For example:

#define XL12_UDF_ARG_LIMIT    255
typedef struct
{
// REQUIRED (if v12+ strings undefined use v11- strings):
    char *name_in_code;    // RegArg2: Function name as in code (v11-)
    char *types;        // RegArg3: Return type and argument types (v11-)
    char *name_in_code12;        // RegArg2: Function name as in code (v12+)
    char *types12;        // RegArg3: Return type and argument types (v12+)
    char *ws_name;        // RegArg4: Fn name as it appears on worksheet
    char *arg_names;        // RegArg5: Argument names (Excel 11-: max 30)
    char *arg_names12;        // RegArg5: Argument names (Excel 12+: max 64)
// OPTIONAL:
    char *fn_category;        // RegArg7: Function category for Function Wizard
    char *help_file;        // RegArg9: Help file (optional)
    char *fn_description;    // RegArg10: Function description text (optional)
    char *arg_help[MAX_XL12_UDF_ARGS - 11]; // RegArg11...: Arg help text
}
    ws_func_export_data;

Note that whatever the registration function does with this data, only one worksheet function name is provided so that worksheets are not aware (and do not need to know) which function is called. Here is an example of a function that calls standard library functions to reverse a worksheet string:

// Excel 11-:  Register as type "1F"
void __stdcall reverse_text_xl4(char *text) {strrev(text);}

// Excel 12+:  Register as type "1F%$" (if linking with thread-safe library)
void __stdcall reverse_text_xl12(wchar_t *text) {wcsrev(text);}

You could then initialize the structure for this function as follows:

ws_func_export_data WsFuncExports[1] =
{
    {
        "reverse_text_xl4",
        "1F",
        "reverse_text_xl12",
        "1F%$",
        "Reverse",
        "Text",
        "", // arg_names12
        "Text", // function category
        "", // help file
        "Reverse text",
        "Text ",
    },
};

The previous strings are null-terminated byte strings. Any code that uses these to initialize XLOPERs must first convert them to length-counted strings. They would also need to be converted from bytes to Unicode if used to initialize XLOPER12s. Alternatively, you could initialize these strings with a leading space, which other code can overwrite with the strings' lengths. However, this can cause problems with some compilers running in debug mode. You could easily modify the previous structure definition to pass Unicode strings to Excel when running Excel 2007. This would also require that you modify the code that used the structure.

This approach leads to the possibility that a worksheet running in Excel 2003 could display different results than the same sheet running in Excel 2007. For example, Excel 2003 maps a Unicode string in an Excel 2003 worksheet cell to an ASCII byte-string and truncates it before passing it to an XLL function call. Excel 2007 will pass an unconverted Unicode strong to an XLL function registered in the correct way. This could lead to a different return result. You should be aware of this possibility and the consequences to your users, not just in the upgrade to Excel 2007. For example, some built-in numeric functions were improved between Excel 2000 and Excel 2003.

Wrapping the Data Types in a Common Container Class or Structure

In general, an XLL worksheet function performs the following actions:

  • Checks the validity of the inputs.

  • Interprets the inputs in the context of one another.

  • Returns specific errors if the inputs are not what they should be.

  • Populates data structures.

  • Calls some deeper core code.

  • Processes the return value from core code and returns something appropriate to Excel.

Where you are providing two exported interfaces, as outlined here, having to duplicate all this logic is not ideal, but if the argument data types are all different, what choice do you have? The answer is to wrap Excel data types in a common container. There are many possible approaches, but let us for the moment constrain the discussion of containing XLOPERs and XLOPER12s. The solution outlined here is to create a C++ class that understands XLOPERs and XLOPER12s (and in this example contains an instance of both). The following examples discuss a C++ class cpp_xloper, which is described fully in the second edition of the author's book listed at the end of the article.

The class should ideally have a constructor that makes a shallow copy, by default, of a supplied XLOPER or XLOPER12 initializer. (Copies are shallow to speed up interpretation of read-only UDF arguments.) It should also provide accessor functions to enable the extraction and modification of type and value. The exported functions then only need to convert their XLOPER or XLOPER12 arguments to this common type, call a common function that performs the real task, and process that function's return values. Here is an example using the class cpp_xloper:

xloper * __stdcall my_function_xl4(xloper *arg)
{
    cpp_xloper Arg(arg); // constructor makes shallow copy
    cpp_xloper RetVal; // passed by ref to retrieve return value
    my_core_function(RetVal, Arg);
    return RetVal.ExtractXloper();
}

xloper12 * __stdcall my_function_xl12(xloper12 *arg)
{
    cpp_xloper Arg(arg); // constructor makes shallow copy
    cpp_xloper RetVal; // passed by ref to retrieve return value
    my_core_function(RetVal, Arg);
    return RetVal.ExtractXloper12();
}

void my_core_function(cpp_xloper &RetVal, cpp_xloper &Arg)
{
    double d;
    if(Arg.IsMissing() || Arg.IsNil())
        RetVal.SetToError(xlerrValue);
    else if(Arg.IsNum() && (d = (double)Arg) >= 0.0)
        RetVal = sqrt(d); // overloaded assignment operator
    else
        RetVal.SetToError(xlerrNum);
}

It is assumed the methods ExtractXloper and ExtractXloper12 return pointers to a thread-safe static XLOPER and XLOPER12 respectively, with the appropriate memory-free bits set where necessary.

To minimize the overhead of all this wrapping, not only should the constructor make shallow copies but, internally, it should recognize the running version and convert XLOPERs to XLOPER12s and call Excel12 instead of Excel4 when running Excel 2007. This is because in Excel 2007, if Excel4 is called, the XLOPER arguments are converted up to XLOPER12s and the return value converted back down to an XLOPER. Getting the class to use the appropriate type and callback avoids this conversion on every call.

Wrapping the C API Functions

Following on from the previous section, if my_core_function needs to call back into Excel through the C API, it must convert the cpp_xloperback into an XLOPER or an XLOPER12 and then call either Excel4 or Excel12 depending on the running version. One solution to this is to wrap the functions Excel4, Excel4v, Excel12, and Excel12v into cpp_xloper as class member functions. Then you could rewrite my_core_function as:

void my_core_function(cpp_xloper &RetVal, cpp_xloper &Arg)
{
    if(!Arg.IsNum() || Arg.Excel(xlfSqrt, 1, &Arg) != xlretSuccess)
        RetVal.SetToError(xlerrValue);
}

where cpp_xloper::Excel places the return value directly into Arg. To do this and still provide flexibility so that you can call this function with XLOPER, XLOPER12, or cpp_xloper arguments, you should create a number of overloaded member functions:

int Excel(int xlfn); // not strictly necessary, but simplifies the others
int Excel(int xlfn, int count, const xloper *p_op1, ...);
int Excel(int xlfn, int count, const xloper12 *p_op1, ...);
int Excel(int xlfn, int count, const cpp_xloper *p_op1, ...);
int Excel(int xlfn, int count, const xloper *p_array[]);
int Excel(int xlfn, int count, const xloper12 *p_array[]);
int Excel(int xlfn, int count, const cpp_xloper *p_array[]);

Note that it is assumed that the caller of the variable argument versions of these functions does not mix argument types. Note also that the use of const here requires the addition of const to the definitions of Excel4v and Excel12v.

After implementing such a wrapper, you should not call the C API functions directly. Another advantage of using this approach is that you can contain the memory management of the returned value within the class. If Excel returns a string, the class can set a flag telling it to call xlFree before overwriting or destroying that instance. You can also build additional checks into these wrappers. For example, you can check that the count is not less than zero or greater than the version-specific limit. In this case, you may want to define and return an additional error:

#define xlretNotCalled      -1   // C API not called

Here is an example implementation of one of these functions, where variables prefixed with m_ are class member variables; the flags m_XLtoFree12 and m_XLtoFree indicate to the class to call xlFree to release memory; and m_Op and m_Op12 are the class' internal copies of the XLOPER and XLOPER12 data structures respectively:

int cpp_xloper::Excel(int xlfn, int count, const cpp_xloper *p_op1, ...)
{
    if(xlfn < 0 || count < 0 || count > (gExcelVersion12plus ?
        MAX_XL12_UDF_ARGS : MAX_XL11_UDF_ARGS))
        return xlretNotCalled;

    if(count == 0 || !p_op1) // default to 0 and NULL if omitted
        return Excel(xlfn); // Call a simpler version of this function

    va_list arg_ptr;
    va_start(arg_ptr, p_op1); // Initialize

    if(gExcelVersion12plus)
    {
        const xloper12 *xloper12_ptr_array[MAX_XL12_UDF_ARGS];
        xloper12_ptr_array[0] = &(p_op1->m_Op12);
        cpp_xloper *p_cpp_op;

        for(int i = 1; i < count; i++) // retrieve as ptrs to cpp_xlopers
        {
            p_cpp_op = va_arg(arg_ptr, cpp_xloper *);
            xloper12_ptr_array[i] = &(p_cpp_op->m_Op12);
        }
        va_end(arg_ptr); // Reset

        xloper12 temp;
        m_ExcelRtnCode = Excel12v(xlfn, &temp, count, xloper12_ptr_array);
        Free();

        if(m_ExcelRtnCode == xlretSuccess)
        {
            m_Op12 = temp; // shallow copy
            m_XLtoFree12 = true;
        }
    }
    else // gExcelVersion < 12
    {
        const xloper *xloper_ptr_array[MAX_XL11_UDF_ARGS];
        xloper_ptr_array[0] = &(p_op1->m_Op);
        cpp_xloper *p_cpp_op;

        for(int i = 1; i < count; i++) // retrieve as ptrs to cpp_xlopers
        {
            p_cpp_op = va_arg(arg_ptr, cpp_xloper *);
            xloper_ptr_array[i] = &(p_cpp_op->m_Op);
        }
        va_end(arg_ptr); // Reset

        xloper temp;
        m_ExcelRtnCode = Excel4v(xlfn, &temp, count, xloper_ptr_array);
        Free();

        if(m_ExcelRtnCode == xlretSuccess)
        {
            m_Op = temp; // shallow copy
            m_XLtoFree = true;
        }
    }
    return m_ExcelRtnCode;
}

New Worksheet Functions and Analysis Toolpak Functions

Unlike earlier versions of Excel, Analysis Toolpak (ATP) functions have been incorporated into Excel 2007. Previously, an XLL could call an ATP function in the following way using xlUDF:

double call_ATP_example(void)
{
    xloper settlement, maturity, coupon, yield,
        redepmtion_value, num_coupons, rate_basis, price;

// Initialise the data types to be passed to the ATP function PRICE
    settlement.xltype = maturity.xltype = coupon.xltype =
    yield.xltype = redepmtion_value.xltype =
    num_coupons.xltype = rate_basis.xltype = xltypeNum;

// Set the values to be passed to the ATP function PRICE
    settlement.val.num = 39084.0; // 2-Jan-2007
    maturity.val.num = 46706.0; // 15-Nov-2027
    coupon.val.num = 0.04;
    yield.val.num = 0.05;
    redepmtion_value.val.num = 1.0; // 100% of face value
    num_coupons.val.num = 1.0; // Annual coupons
    rate_basis.val.num = 1.0; // Act/Act

    xloper fn;
    fn.xltype = xltypeStr;
    fn.val.str = "\005" "PRICE";
    if(Excel4(xlUDF, &price, 8, &fn, &settlement, &maturity,
        &coupon, &yield, &redepmtion_value, &num_coupons,
        &rate_basis) != xlretSuccess || price.xltype != xltypeNum)
        return -1.0; // an error value
    return price.val.num;
}

In Excel 2007, you should replace the call into Excel with something like this, where gExcelVersion is an integer variable that has global scope within your project, and is initialized during the call to xlAutoOpen.

    int xl_ret_val;
    if(gExcelVersion12plus)
    {
        xl_ret_val = Excel4(xlfPrice, &price, 7, &settlement,
            &maturity, &coupon, &yield, &redepmtion_value,
            &num_coupons, &rate_basis);
    }
    else // gExcelVersion < 12
    {
        xloper fn;
        fn.xltype = xltypeStr;
        fn.val.str = "\005" "PRICE";
        xl_ret_val = Excel4(xlUDF, &price, 8, &fn, &settlement,
            &maturity, &coupon, &yield, &redepmtion_value,
            &num_coupons, &rate_basis);
    }
    if(xl_ret_val != xlretSuccess || price.xltype != xltypeNum)
        return -1.0; // an error value
    return price.val.num;

You can make the function more version-independent and efficient in both Excel 2003 and Excel 2007 with the use of a container such as cpp_xloper. For example:

double call_ATP_example_3(void)
{
    cpp_xloper Settlement(39084.0); // 2-Jan-2007
    cpp_xloper Maturity(46706.0); // 15-Nov-2027
    cpp_xloper Price, Coupon(0.04), YTM(0.05);
    cpp_xloper RedepmtionValue(1.0); // 100% of face
    cpp_xloper NumCoupons(1.0); // Annual coupons
    cpp_xloper RateBasis(1.0); // Act/Act
    int xl_ret_val;

    if(gExcelVersion12plus)
    {
        xl_ret_val = Price.Excel(xlfPrice, 7, &Settlement,
            &Maturity, &Coupon, &YTM, &RedepmtionValue,
            &NumCoupons, &RateBasis);
    }
    else
    {
        cpp_xloper Fn("PRICE");
        xl_ret_val = Price.Excel(xlUDF, 8, &Fn, &Settlement,
            &Maturity, &Coupon, &YTM, &RedepmtionValue,
            &NumCoupons, &RateBasis);
    }
    if(xl_ret_val != xlretSuccess || !Price.IsNum())
        return -1.0; // an error value
    return (double)Price;
}

If you try to call new C API worksheet functions in earlier versions, you get an xlretInvXlfn error.

Writing Thread-Safe XLLs and Worksheet Functions

Earlier versions of Excel used a single thread for all worksheet calculations. On a multiprocessor computer, or on a single-processor computer that the user has explicitly configured to use multiple threads, Excel 2007 tries to balance the load between the main thread and one or more additional threads, which the operating system allocates across all the processors. On a dual-processor (or dual-core) computer, this can speed up the recalculation time by at most a factor of 2, contingent on the topology of the dependency tree within the workbook(s) and how many of the functions involved are thread-safe.

Excel 2007 uses one thread (its main thread) to call all commands, thread-unsafe functions, xlAuto functions (except xlAutoFree), and COM and VBA functions.

XLL developers can create thread-safe functions provided that they observe a few simple rules:

  • Do not call resources in other DLLs that may not be thread-safe.

  • Do not make any thread-unsafe calls through the C API or COM.

  • Protect resources that could be used simultaneously by more than one thread using critical sections.

  • Use thread-local memory for thread-specific storage, and replace static variables within functions with thread-local variables.

When Excel is returned an XLOPER or XLOPER12 with xlbitDllFree set, it calls xlAutoFree on the same thread before calling any other functions on that thread. This is true of all functions, thread-safe or not, and it avoids the risk of a thread-local XLOPER getting reused before memory associated with it can be freed.

Calling C API Functions from Thread-Safe Functions

VBA and COM add-in functions are not considered thread-safe. In addition to C API commands (such as xlcDefineName, which no worksheet function is allowed to call), thread-safe functions cannot access XLM information functions. Neither can you register thread-safe XLL functions as macro-sheet equivalents by appending a number sign (#) to the type string. The consequences are that a thread-safe function cannot:

  • Read the value of an uncalculated cell (including the calling cell).

  • Call functions such as xlfGetCell, xlfGetWindow, xlfGetWorkbook, and xlfGetWorkspace and other information functions.

  • Define or delete XLL-internal names using xlfSetName.

The one XLM exception is xlfCaller which is thread-safe. If the caller was a worksheet cell or range, xlfCaller returns a reference. However, you cannot safely coerce this resulting reference to a value using xlCoerce in a thread-safe function because this would return xlretUncalced. Registering the function with # addresses this problem, but in this case, the function is a macro-sheet equivalent, and therefore, is not considered thread-safe. This prevents functions that return the previous value, such as when a certain error condition exists, from being thread-safe.

It should be noted that the C API–only functions are all thread-safe:

  • xlCoerce (although coercion of uncalculated-cell references fails)

  • xlFree

  • xlStack

  • xlSheetId

  • xlSheetNm

  • xlAbort (except that it cannot be used to clear a break condition)

  • xlGetInst

  • xlGetHwnd

  • xlGetBinaryName

  • xlDefineBinaryName

The one exception is xlSet which is a command-equivalent and so cannot be called from any worksheet function.

All the Excel 2007 built-in worksheet functions and their C API equivalents are thread-safe except for the following:

  • PHONETIC

  • CELL when either of the "format" or "address" arguments are used

  • INDIRECT

  • GETPIVOTDATA

  • CUBEMEMBER

  • CUBEVALUE

  • CUBEMEMBERPROPERTY

  • CUBESET

  • CUBERANKEDMEMBER

  • CUBEKPIMEMBER

  • CUBESETCOUNT

  • ADDRESS where the fifth parameter (the sheet_name) is given

  • Any database function (such as DSUM or DAVERAGE) that refers to an Excel PivotTable

  • ERROR.TYPE

  • HYPERLINK

Resources Used by Multiple Threads

You should protect read/write memory that can be accessed by more than one thread with the use of critical sections. You need a named critical section for each block of memory you want to protect. You can initialize these during the call to xlAutoOpen, and release them and set them to null during the call to xlAutoClose. You should contain each access to the protected block with calls to EnterCriticalSection and LeaveCriticalSection. Only one thread is permitted to be in the critical section at any time. Here is an example of the initialization, un-initialization, and use of a section called g_csSharedTable:

CRITICAL_SECTION g_csSharedTable; // global scope (if required)
bool xll_initialised = false; // module scope

int __stdcall xlAutoOpen(void)
{
    if(xll_initialised)
        return 1;
// Other initialisation omitted
    InitializeCriticalSection(&g_csSharedTable);
    xll_initialised = true;
    return 1;
}

int __stdcall xlAutoClose(void)
{
    if(!xll_initialised)
        return 1;
// Other cleaning up omitted
    DeleteCriticalSection(&g_csSharedTable);
    xll_initialised = false;
    return 1;
}

bool read_shared_table_element(unsigned int index, double &d)
{
    if(index >= SHARED_TABLE_SIZE) return false;
    EnterCriticalSection(&g_csSharedTable);
    d = shared_table[index];
    LeaveCriticalSection(&g_csSharedTable);
    return true;
}
bool set_shared_table_element(unsigned int index, double d)
{
    if(index >= SHARED_TABLE_SIZE) return false;
    EnterCriticalSection(&g_csSharedTable);
    shared_table[index] = d;
    LeaveCriticalSection(&g_csSharedTable);
    return true;
}

Another, perhaps safer, way of protecting a block of memory is to create a class that contains its own CRITICAL_SECTION and whose constructor, destructor, and accessor methods take care of its use. This approach has the added advantage of protecting objects that may be initialized before xlAutoOpen is run, or survive after xlAutoClose is called, but you should be careful about creating too many critical sections and slowing down the operating system unnecessarily.

Where you have code that needs access to more than one block of protected memory at the same time, you need to be very careful about the order in which the critical sections are entered and exited. For example, the following two functions could create a deadlock:

bool copy_shared_table_element_A_to_B(unsigned int index)
{
    if(index >= SHARED_TABLE_SIZE) return false;
    EnterCriticalSection(&g_csSharedTableA);
    EnterCriticalSection(&g_csSharedTableB);
    shared_table_B[index] = shared_table_A[index];
    LeaveCriticalSection(&g_csSharedTableA);
    LeaveCriticalSection(&g_csSharedTableB);
    return true;
}
bool copy_shared_table_element_B_to_A(unsigned int index)
{
    if(index >= SHARED_TABLE_SIZE) return false;
    EnterCriticalSection(&g_csSharedTableB);
    EnterCriticalSection(&g_csSharedTableA);
    shared_table_A[index] = shared_table_B[index];
    LeaveCriticalSection(&g_csSharedTableA);
    LeaveCriticalSection(&g_csSharedTableB);
    return true;
}

If the first function on one thread enters g_csSharedTableA while the second on another thread enters g_csSharedTableB, both threads hang. The correct approach is to enter in a consistent order and exit in the reverse order, as follows:

    EnterCriticalSection(&g_csSharedTableA);
    EnterCriticalSection(&g_csSharedTableB);
    // code that accesses both blocks
    LeaveCriticalSection(&g_csSharedTableB);
    LeaveCriticalSection(&g_csSharedTableA);

Where possible, it is better from a thread-cooperation point of view to isolate access to distinct blocks, as shown here:

bool copy_shared_table_element_A_to_B(unsigned int index)
{
    if(index >= SHARED_TABLE_SIZE) return false;
    EnterCriticalSection(&g_csSharedTableA);
    double d = shared_table_A[index];
    LeaveCriticalSection(&g_csSharedTableA);
    EnterCriticalSection(&g_csSharedTableB);
    shared_table_B[index] = d;
    LeaveCriticalSection(&g_csSharedTableB);
    return true;
}

Where there is a lot of contention for a shared resource, such as frequent short-duration access requests, you should consider using the critical section's ability to spin. This is a technique that makes waiting for the resource less processor-intensive. To do this, you can use either use InitializeCriticalSectionAndSpinCount when initializing the section or SetCriticalSectionSpinCount after the critical section has been initialized, to set the number of times the thread loops before waiting for resources to become available. The wait operation is expensive, so spinning avoids this if the resource is freed in the meantime. On a single processor system, the spin count is effectively ignored, but you can still specify it without doing any harm. The memory heap manager uses a spin count of 4000. For more information about the use of critical sections, you should refer to the Critical Sections Object topic in the Platform SDK documentation.

Declaring and Using Thread-Local Memory

For example, consider a function that returns a pointer to an XLOPER:

xloper * __stdcall mtr_unsafe_example(xloper *arg)
{
    static xloper ret_val; // memory shared by all threads!!!
// code sets ret_val to a function of arg ...
    return &ret_val;
}

This function is not thread-safe because it is possible for one thread to return the static XLOPER while another is overwriting it. The probability of this occurring is greater still if the XLOPER needs to be passed to xlAutoFree. One solution is to allocate memory for a return XLOPER and implement xlAutoFree so that the XLOPER memory itself is freed:

xloper * __stdcall mtr_safe_example_1(xloper *arg)
{
    xloper *p_ret_val = new xloper; // must be freed by xlAutoFree
// code sets ret_val to a function of arg ...
    p_ret_val.xltype |= xlbitDLLFree; // Always needed regardless of type
    return p_ret_val; // xlAutoFree must free p_ret_val
}

This approach is simpler than the approach outlined in the next example, which relies on the TLS API, but it has a few disadvantages. For one, Excel has to call xlAutoFree whatever the type of the returned XLOPER. And second, if the newly-allocated XLOPER is a string populated in a call to Excel4, there is no easy way to inform xlAutoFree of the need to free the string using xlFree before using delete to free p_ret_val, requiring that the function make a DLL-allocated copy.

A solution that avoids these limitations is to populate and return a thread-local XLOPER, an approach that requires that xlAutoFree does not free the XLOPER pointer itself.

xloper *get_thread_local_xloper(void);

xloper * __stdcall mtr_safe_example_2(xloper *arg)
{
    xloper *p_ret_val = get_thread_local_xloper();
// code sets ret_val to a function of arg setting xlbitDLLFree or
// xlbitXLFree if required
    return p_ret_val; // xlAutoFree must not free this pointer!
}

The next question is how to set up and retrieve the thread-local memory. This is done using the Thread Local Storage (TLS) API. The first step is to obtain a TLS index using TlsAlloc, which must ultimately be released using TlsFree. Both are best accomplished from DllMain:

// This implementation just calls a function to set up thread-local storage
BOOL TLS_Action(DWORD Reason);

__declspec(dllexport) BOOL __stdcall DllMain(HINSTANCE hDll, DWORD Reason, void *Reserved)
{
    return TLS_Action(Reason);
}
DWORD TlsIndex; // only needs module scope if all TLS access in this module

BOOL TLS_Action(DWORD DllMainCallReason)
{
    switch (DllMainCallReason)
    {
    case DLL_PROCESS_ATTACH: // The DLL is being loaded
        if((TlsIndex = TlsAlloc()) == TLS_OUT_OF_INDEXES)
            return FALSE;
        break;

    case DLL_PROCESS_DETACH: // The DLL is being unloaded 
        TlsFree(TlsIndex); // Release the TLS index.
        break;
    }
    return TRUE;
}

After you obtain the index, the next step is to allocate a block of memory for each thread. DllMain in the Dynamic-Link Library Reference recommends doing this every time DllMain is called with a DLL_THREAD_ATTACH event, and freeing the memory on every DLL_THREAD_DETACH, but following this advice would cause your DLL to perform unnecessary allocation for threads that Excel does not use for recalculation. Instead, it is better to use an allocate-on-first-use strategy. First, you need to define a structure that you want to allocate for each thread. For the previous example, the following suffices:

struct TLS_data
{
    xloper xloper_shared_ret_val;
// Add other required thread-local data here...
};

The following function gets a pointer to the thread-local instance, or allocates one if this is the first call:

TLS_data *get_TLS_data(void)
{
// Get a pointer to this thread's static memory
    void *pTLS = TlsGetValue(TlsIndex);
    if(!pTLS) // No TLS memory for this thread yet
    {
        if((pTLS = calloc(1, sizeof(TLS_data))) == NULL)
        // Display some error message (omitted)
            return NULL;
        TlsSetValue(TlsIndex, pTLS); // Associate this this thread
    }
    return (TLS_data *)pTLS;
}

Now you can see how the thread-local XLOPER memory is obtained. First, you get a pointer to the thread's instance of TLS_data, and then you return a pointer to the XLOPER that it contains:

xloper *get_thread_local_xloper(void)
{
    TLS_data *pTLS = get_TLS_data();
    if(pTLS)
        return &(pTLS->xloper_shared_ret_val);
    return NULL;
}

By adding an XLOPER12 to TLS_data and a get_thread_local_xloper12 access function you can write XLOPER12 versions of mtr_safe_example.

As should be clear, mtr_safe_example_1 and mtr_safe_example_2 are thread-safe functions that you can registered as "RP$" when running Excel 2007 and "RP" when running Excel 2003. You can create and register a version of this XLL function that uses an XLOPER12 as "UQ$" when running Excel 2007, but you cannot register it at all in Excel 2003.

Conclusion

This article discussed the development of XLLs in Office Excel 2007. The functionality described in this article is now available in the Excel 2007 XLL Software Development Kit on the Microsoft Download Center.

About the Author

Steve Dalton is founder of Eigensys Ltd. in the UK. Eigensys works in the field of Excel development, specifically applied to financial analytics. Mr. Dalton is the author of Excel Add-in Development in C/C++: Applications in Finance (Wiley, 2004) and Financial Applications Using Excel Add-in Development in C/C++ (Wiley, 2007).

This article was developed in partnership with A23 Consulting.

Additional Resources

To learn more about developing add-ins in Excel 2007, see the following resources: