Calling into Excel from the DLL/XLL

This content is outdated and is no longer being maintained. It is provided as a courtesy for individuals who are still using these technologies. This page may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist.

Microsoft Office Excel enables your DLL to access built-in Excel commands, worksheet functions, and macro sheet functions. These are available both from DLL commands and functions called from Visual Basic for Applications (VBA), and from registered XLL commands and functions called directly by Excel.

Excel4, Excel4v, Excel12, and Excel12v Functions

Excel enables this through the callback functions Excel4, Excel4v, Excel12, and Excel12v.

The Excel4 and Excel4v functions were introduced in Excel version 4. They work with the XLOPER data structure. Excel 2007 (version 12) introduces two new callback functions, Excel12 and Excel12v, which work with the XLOPER12 data structure. The Excel4 and Excel4v functions are exported by the library xlcall32.lib, which must be included in your DLL/XLL project. The second two are included in the SDK C++ source file xlcall.cpp, which must be included in your project if you want to access Excel functionality using XLOPER12s.

The function prototypes for these four functions are given here. The first three arguments are the same except that the second argument is a pointer to an XLOPER in the first pair and a pointer to an XLOPER12 in the second pair. The calling convention is _cdecl in Excel4 and Excel12 to permit the variable argument lists. The ellipsis is assumed by the function to be count number of pointers to XLOPERs for Excel4 and count number of pointers to XLOPER12s for Excel12.

All versions of Excel

int _cdecl Excel4(int xlfn, LPXLOPER operRes, int count,... );

int pascal Excel4v(int xlfn, LPXLOPER operRes, int count, LPXLOPER opers[]);

Excel 2007 and later versions only

int _cdecl Excel12(int xlfn, LPXLOPER12 operRes, int count,... );

int pascal Excel12v(int xlfn, LPXLOPER12 operRes, int count, LPXLOPER12 opers[]);

For the DLL to be able to call Excel4, Excel4v, Excel12 or Excel12v, Excel must pass control to the DLL. This means that these C API callbacks can only be called:

• From within an XLL command that Excel has called directly or via VBA.

• From within an XLL worksheet or macro sheet function that Excel has called directly or via VBA.

You cannot call the Excel C API:

• From an operating system event, for example, from DllMain.

• From a background thread that your DLL has created.

Return Values

All four of these functions return an integer value informing the caller whether the function or command was called successfully or not. The values returned can be any of the following:

If the function returns one of the failure codes in the table, that is, it does not return xlretSuccess, the return value XLOPER/XLOPER12 will also be set to #VALUE!. In certain circumstances, checking for this might be a sufficient test of success, but you should note that a call can return both xlretSuccess and #VALUE!.

If a call to the C API results in either xlretUncalced or xlretAbort, your DLL/XLL code should return control to Excel before making any other C API calls other than calls to xlFree to release Excel-allocated memory resources in XLOPERs and XLOPER12s.

Command or Function Enumeration Argument: xlfn

The xlfn argument is the first argument to the callback functions and is a 32-bit signed integer. Its value should be one of the function or command enumerations defined in the SDK header file, xlcall.h, for example:

/* Excel function numbers */
#define xlfCount 0
#define xlfIsna 2
#define xlfIserror 3
#define xlfSum 4
#define xlfAverage 5
#define xlfMin 6
#define xlfMax 7
#define xlfRow 8
#define xlfColumn 9
#define xlfNa 10
...

/* Excel command numbers */
#define xlcBeep (0 | xlCommand)
#define xlcOpen (1 | xlCommand)
#define xlcOpenLinks (2 | xlCommand)
#define xlcCloseAll (3 | xlCommand)
#define xlcSave (4 | xlCommand)
#define xlcSaveAs (5 | xlCommand)
#define xlcFileDelete (6 | xlCommand)
#define xlcPageSetup (7 | xlCommand)
#define xlcPrint (8 | xlCommand)
#define xlcPrinterSetup (9 | xlCommand)
...

All worksheet and macro sheet functions are in the range 0 (xlfCount) to 0x0fff hex, although the highest assigned number in Excel 2007 is 484 decimal, 0x01e4 hex (xlfAverageIfs).

All command functions are in the range 0x8000 hex (xlcBeep) to 0x8fff hex although the highest assigned number in Excel 2007 is 0x8328 hex (xlcHideallInkannots). These are defined in the header file as (n | xlCommand) where n is a decimal number greater or equal to zero and xlCommand is defined as 0x8000 hex.

Invoking Excel Commands with Dialog Boxes

Some of the command codes correspond to actions in Excel that are supported by dialog boxes. For example, xlcFileDelete takes a single argument: a file name or mask. This can be invoked with the dialog box so that the user has the opportunity to cancel or modify the delete operation. It can also be called without the dialog box, in which case the file or files are deleted without any further interaction, assuming they exist and the caller has permission. To call such commands in their dialog box form, the command enumeration must be bit-wise OR’d with 0x1000 (xlPrompt).

This example code deletes files in the current directory matching the mask my_data*.bak, displaying a dialog box only if the argument is true.

bool delete_my_backup_files(bool show_dialog)
{
    XLOPER12 xResult, xFilter;
    xFilter.xltype = xltypeStr;
    xFilter.val.str = L"\014my_data*.bak"; // String length: 14 octal
    int cmd;

    if(show_dialog)
        cmd = xlcFileDelete | xlPrompt;
    else
        cmd = xlcFileDelete;

// xResult should be Boolean TRUE if successful in which
// case return true, otherwise false.
    return (Excel12(cmd, &xResult, 1, &xFilter) == xlretSuccess
        && xResult.xltype == xltypeBool
        && xResult.val.xbool == 1);
}

Calling Functions and Commands in International Versions

You can configure Excel to display functions and XLM command names in a variety of languages. Some C API commands and functions operate on strings that are interpreted as function or command names. For example, xlcFormula takes a string argument that is intended to be placed in a specified cell. For your add-in to work with all language settings, you can supply the English string names and set the bit 0x2000 (xlIntl) in the function or command enumeration.

The following example code places the equivalent of the =SUM(X1:X100) in cell A2 on the active sheet. Note that it uses the Framework function, TempActiveRef, to create a temporary external reference XLOPER. The formula will appear in A2 in the correct locale-determined language, for example, =SOMME(X1:X100) if the language is French.

int WINAPI InternationlExample(void)
{
    XLOPER12 xSum, xResult;
    xSum.xltype = xltypeStr;
    xSum.val.str = L"\015=SUM(X1:X100)";
    Excel12(xlcFormula | xlIntl, &xResult, 2,
        &xSum, TempActiveRef(2,2,1,1));
    return 1;
}

Note
Because the result of the call to Excel12 is not required, 0 (NULL) could be passed as the second argument instead of the address of xResult. This is discussed more in the next section.

DLL-Only Functions and Commands

Excel supports a small number of functions that are only accessible from a DLL/XLL. These are defined in the header file as (n | xlSpecial) where n is a decimal number greater or equal to zero and xlSpecial is defined as 0x4000 hex. These functions are listed in the following table and documented in the API Function Reference.

Return Value XLOPER/XLOPER12: operRes

The operRes argument is the second argument to the callbacks and is a pointer to an XLOPER (Excel4 and Excel4v) or XLOPER12 (Excel12 and Excel12v). After a successful call, it contains the return value of the function or command. operRes can be set to zero (NULL pointer) if no return value is required. The previous contents of operRes are overwritten so that any memory previously pointed to must be freed before to the call to avoid memory leaks.

If the function or command cannot be called, for example, if the arguments are incorrect, operRes is set to the error #VALUE!. A command always returns BooleanTRUE if successful, or FALSE if it failed or was canceled by the user.

Number of Subsequent Arguments: count

The count argument is the third argument to the callbacks and is a 32-bit signed integer. It should be set to the number of subsequent arguments, counting from 1. If a function or command takes no arguments, it should be set to zero. In Microsoft Office Excel 2003, the maximum number of arguments that any function can take is 30, although most take fewer than this. In Excel 2007, the maximum number of arguments that any function can take is increased to 255.

With Excel4 and Excel12, count is the number of pointers to XLOPER/XLOPER12s that are being passed. You should be very careful not to pass fewer arguments than the value that count is set to. This would result in Excel reading ahead into the stack and trying to process invalid XLOPER/XLOPER12s, something which could cause an application crash.

With Excel4v and Excel12v, count is the size of the array of pointers to XLOPER/XLOPER12s that is being passed as the next and final argument. Again, you should be very careful not to pass a smaller array than count elements in size, as this will result in the bounds of the array being overrun.

Passing Arguments to C API Functions

Both Excel4 and Excel12 take variable length argument lists, after count, which are interpreted as pointers to XLOPERs and XLOPER12s respectively. Excel4v and Excel12v take a single argument after count which is a pointer to an array of pointers to XLOPERs in the case of Excel4v and to XLOPER12s in the case of Excel12v.

The array forms, Excel4v and Excel12v, enable you to code a call to the C API cleanly when the number of arguments is variable. The following is a simple example of a function that takes a variable-sized array of numbers and uses Excel worksheet functions, via the C API, to calculate the sum, average, minimum, and maximum.

void Excel12v_example(double *dbl_array, int size, double &sum, double &average, double &min, double &max)
{
// 30 is the limit in Excel 2003. 255 is the limit in Excel 2007.
// Use the lower limit to be safe, although it is better to make
// the function version-aware and use the correct limit.
    if(size < 1 || size > 30)
        return;

// Create an array of XLOPER12s
    XLOPER12 *xOpArray = (XLOPER12 *)malloc(size * sizeof(XLOPER12));
// Create an array of pointers to XLOPER12s
    LPXLOPER12 *xPtrArray =
        (LPXLOPER12 *)malloc(size * sizeof(LPXLOPER12));

// Initialise and populate the array of XLOPER12s
// and set up the pointers in the pointer array
    for(int i = 0; i < size; i++)
    {
        xOpArray[i].xltype = xltypeNum;
        xOpArray[i].val.num = dbl_array[i];
        xPtrArray[i] = xOpArray + i;
    }

    XLOPER12 xResult;
    int retval;
    int fn[4] = {xlfSum, xlfAverage, xlfMin, xlfMax};
    double *result_ptr[4] = {&sum, &average, &min, &max};

    for(i = 0; i < 4; i++)
    {
        retval = Excel12v(fn[i], &xResult, size, xPtrArray);
        if(retval == xlretSuccess && xResult.xltype == xltypeNum)
            *result_ptr[i] = xResult.val.num;
    }
    free(xPtrArray);
    free(xOpArray);
}

Replacing references to XLOPER12s with XLOPER, and Excel12v with Excel4v, in this code would result in a function that would work with all versions of Excel. This operation of the Excel functions SUM, AVERAGE, MIN, and MAX is simple enough that it would be more efficient to code them in C and avoid the overhead of preparing the arguments and calling into Excel. However, many of the functions Excel contains are more complex, making this approach useful in some cases.

The section on xlfRegister also provides another example of working with Excel4v and Excel12v. When registering an XLL worksheet function, you can supply a descriptive string for each argument that is used in the Paste Function dialog box. Therefore, the number of total arguments being supplied to xlfRegister depends on the number of arguments your XLL function takes and will vary from one function to the next.

Where you always call a C API function or command with the same number of arguments, you want to avoid the extra step of creating an array of pointers for those arguments. In those cases it is simpler and cleaner to use Excel4 and Excel12. For example, when registering XLL functions and commands, you need to supply the full path and file name of the DLL/XLL. You can obtain it in a call to xlfGetName and then release it with a call to xlFree, as shown here for both Excel4 and Excel12:

XLOPER xDllName;
if(Excel4(xlfGetName, &xDllName, 0) == xlretSuccess)
{
    // Use the name....
    // Then free the memory that Excel allocated for the string
    Excel4(xlFree, 0, 1, &xDllName);
}

XLOPER12 xDllName;
if(Excel12(xlfGetName, &xDllName, 0) == xlretSuccess)
{
    // Use the name....
    // Then free the memory that Excel allocated for the string
    Excel12(xlFree, 0, 1, &xDllName);
}

In practice, the function, Excel12v_example, could be coded more efficiently by creating a single xltypeMultiXLOPER12 argument, and calling the C API using Excel12, as shown here:

void Excel12_example(double *dbl_array, int size, double &sum, double &average, double &min, double &max)
{
// In this implementation the upper limit is the largest
// single column array = 2^20 = 1048576 rows in Excel 2007
    if(size < 1 || size > 1048576)
        return;

// Create an array of XLOPER12s
    XLOPER12 *xOpArray = (XLOPER12 *)malloc(size * sizeof(XLOPER12));

// Create and initialize an xltypeMulti array
// representing a one-column array.
    XLOPER12 xOpMulti;
    xOpMulti.xltype = xltypeMulti;
    xOpMulti.val.array.lparray = xOpArray;
    xOpMulti.val.array.columns = 1;
    xOpMulti.val.array.rows = size;

// Initialize and populate the array of XLOPER12s.
    for(int i = 0; i < size; i++)
    {
        xOpArray[i].xltype = xltypeNum;
        xOpArray[i].val.num = dbl_array[i];
    }

    XLOPER12 xResult;
    int fn[4] = {xlfSum, xlfAverage, xlfMin, xlfMax};
    double *result_ptr[4] = {&sum, &average, &min, &max};

    for(i = 0; i < 4; i++)
    {
        Excel12(fn[i], &xResult, 1, &xOpMulti);
        if(xResult.xltype == xltypeNum)
            *result_ptr[i] = xResult.val.num;
    }
    free(xOpArray);
}

Note
In this case the return value of Excel12 is ignored. The code instead checks that the returned XLOPER12 is xltypeNum to determine if the call was successful.

XLCallVer

In addition to the callbacks Excel4, Excel4v, Excel12 and Excel12v, Excel exports a function XLCallVer which returns the version of the C API currently running.

The function prototype is:

int pascal XLCallVer(void);

You can call this function from any XLL command or function and is thread safe.

In Excel 97 through Excel 2003, XLCallVer returns 1280 = 0x0500 hex = 5 x 256, which indicates Excel version 5. In Excel 2007, it returns 3072 = 0x0c00 hex = 12 x 256, which similarly indicates version 12.

Although you can use this to determine whether the new C API is available at run time, you might prefer to detect the running version of Excel using Excel4(xlfGetWorkspace, &version, 1, &arg), where arg is a numeric XLOPER set to 2. The function returns a string XLOPER, version, which can then be coerced to an integer. The reason for relying on the Excel version rather than the C API version is that there are differences between Excel 2000, 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.

See Also

Concepts

Creating XLLs and DLLs that Call Back into Excel

Accessing XLL Code in Excel

Excel 2007 XLL SDK API Function Reference

C API Callback Functions Excel4, Excel12

Developing Excel 2007 XLLs