The XLL Add-In Library

This library has been moved to http://xll.codeplex.com. Please use that.

This document describes how to create Excel add-ins using the Excel 2007 SDK that exposes the big grid, wide-character strings, and thread-safe functions. The library can also be used to create add-ins that work with earlier versions of Excel. This link is to Microsoft's documentation.

Getting Started

The code for the XLL Add-in Library resides in a Subversion repository at http://xll.kalx.net/trunk. TortoiseSVN is a Windows client for Subversion that is integrated into Windows explorer. Use that or your preferred Subversion client to download the source code using the URL above and open the solution xll/9/xlladdins.sln or xll/10/xlladdins.sln. Only Visual C++ 2008 or Visual C++ 2010 are supported.

You can also download and unzip xll20101211.zip if you don't want to use subversion.

Creating a New Project

In Solution Explorer right click on the 'xlladdins' solution and select Add > New Project.... Select a Visual C++ Project type of Win32 and Template Win32 Project. Type a Name for your project and click OK. On the Application Settings tab select Application type DLL and under Additional options choose Empty Project, then click Finish.

Setting Properties

In the Solution Explorer right click on Source files in the new project and select Add > New Item. Select Code under Categories and C++ File(.cpp) under Templates. Type the Name of your .cpp file (often the same as the name of the project) and remove the 9 [or 10] folder from the Location. (This is in case later you want to build add-ins for other version of Visual C++.) Click Add.

In the Solution Explorer right click on the new project and select Properties. Set Configuration to All Configurations. Under Configuration Properties|C/C++|General set Additional Include Directories to ../.. and Warning Level to Level 4 (/W4). Under C/C++|Language set Default Char is Unsigned to Yes [or Command Line|Additional Options type in /J for VC++ 2010].

Set Configuration to Debug. Under C/C++|Code Generation set Runtime Library to Multi-threaded Debug (/MTd). Set Configuration to Release. Under C/C++|Code Generation set Runtime Library to Multi-threaded (/MT).

Finally, right click on the new project and select Project Dependencies... Choose the xll project and click OK. [In VC++ 2010 you will also need to add the xll library as a reference. Right click on the project, choose References... then click Add New Reference... and add the xll project.]

Building your Project

You can cut and paste the code below to use as templates for creating macros and functions.

    #include "xll/xll.h"

    using namespace xll;

    // macro - run in Excel by pressing Alt-F8 and typing XLL.HELLO
    static AddIn xai_hello("?xll_hello", "XLL.HELLO");
    int WINAPI
    xll_hello()
    {
    #pragma XLLEXPORT
    	ExcelX(xlcAlert, OPER("Hello"));

    	return 1;
    }

    // function - call from excel by typing =XLL.DOUBLE(1.23)
    static AddIn xai_double(
    	"?xll_double", XLL_DOUBLE XLL_DOUBLE,
    	"XLL.DOUBLE", "Number",
        "My Category", "Doubles Number."
    );
    double WINAPI
    xll_double(double num)
    {
    #pragma XLLEXPORT
    	return 2*num;
    }

Right click on the project and select Build. If all goes well you should see something like below in your Output window.

    1>------ Build started: Project: example, Configuration: Debug Win32 ------
    1>Compiling...
    1>example.cpp
    1>Linking...
    1>Embedding manifest...
    1>Build log was saved at "file://c:\Users\kal\Code\xll.kalx.net\xll\9\example\Debug\BuildLog.htm"
    1>example - 0 error(s), 0 warning(s)
    ========== Build: 1 succeeded, 0 failed, 1 up-to-date, 0 skipped ==========

Running your Project

Right click on the project and select Properties. Be sure the Configuration is Debug. In Configuration Properties|Debugging click the dropdown for Command, select Browse... and locate the Excel executable on your machine. Often it is C:\Program Files\Microsoft Office\OFFICE<ver>\EXCEL.EXE, where <ver> is the Excel version. For Command Arguments use "$(TargetPath)". Include the quotation marks in case the path has space characters.

Right click on the project and select Debug > Start new instance. If the project is Set as StartUp Project then pressing F5 will also start the debugger. There will be a pop-up box indicating the Excel executable was not built with debug information. Say 'duh' while selecting the checkbox and then click OK.

To run the macro, press Alt-F8, type XLL.HELLO and press the Return key. You should see an informational pop-up box that says Hello. To run the function, type =XLL.DOUBLE in a cell then Ctrl-Shift-A. you should see (Number) appear after the function name with Number selected. Type in any number you please. You can also find the function in the Function Wizard. For the example above, select the categroy My Category and you should see XLL.DOUBLE with the description Doubles Number.

To debug code, simply put breakpoints where you want execution to stop by clicking to the left of the line in Visual Studio.

The AddIn Class

The constructor for objects of type xll::AddIn specifies all the information required by Excel to register an add-in. Here is the first example in example.cpp from the example project.


    #include "xll/xll.h"
    
    using namespace xll;
    
    // Add-in function for all versions of Excel.
    static AddIn xai_square_root(
        // C function name, C function signature,
        "?xll_square_root", XLL_LPOPER XLL_DOUBLE,
        // Excel function text, Excel function argument text,
        "SQUARE.ROOT", "Number",
        // Category, Function help,
        "Example", "Compute the square root of Number.."
    );
		

This will register the C++ function xll_square_root, that returns an LPOPER and has one argument which is a double as SQUARE.ROOT in Excel. The argument prompt (which appears when Ctrl-Shift-A is pressed in Excel after typing the function name) will be Number, and the function will be registered in the Example category with the description Compute the square root of Number. The last two arguments are optional. Note the C function being registered needs to be prepended with a question mark. If you know something about the Excel SDK, XLL_LPOPER is defined to be "P" and XLL_DOUBLE is defined to be "B". The preprocessor pastes these strings together to give the signature "PB" for this function.

Here is the function being registered.

    LPOPER WINAPI
    xll_square_root(double x)
    {
    #pragma XLLEXPORT
        static OPER y;

        if (x <  0)
            y = Err(xlerrNum); // y acts like xltypeErr
        else
            y = sqrt(x); // y acts like a xltypeNum

        return &y;
    }
		

All Excel add-in functions must be declared WINAPI (__stdcall). Note that the first line in the function body is #pragma XLLEXPORT. This is required in order for the function to be exported from the dll. There is no need for DEF files if you use this. Just link with xll.lib and you will have a working XLL.

The OPER Class

The OPER type is a variant type that corresponds to the contents of a spreadsheet cell. It can be a number (xltypeNum), string (xltypeStr), boolean (xltypeBool), error (xltypeErr), two dimensional array of OPERs (xltypeMulti), a missing argument (xltypeMissing), an empty cell (xltypeNil), a simple reference (xltypeSRef), or an integer (xltypeInt ).

Considerable effort has gone into making OPERs behave like true variants, even though C++ is a strongly typed language, as illustrated in the code below from test.cpp in the test project. An OPERX is either an OPER or an OPER12 depending on whether EXCEL12 is undefined or defined. More about this below.

    OPERX o;
    assert (o.xltype == xltypeNil); // empty cell

    o = 1.23;
    assert (o.xltype == xltypeNum);
    assert (o.val.num == 1.23);
    assert (o == 1.23 && 1.23 == o);

    o = _T("foo");
    assert (o.xltype == xltypeStr);
    assert (o.val.str[0] == 3);
    assert (0 == traits<XLOPERX>::xstrnicmp(o.val.str + 1, _T("foo"), o.val.str[0]));
    assert (o == OPERX(_T("foo"))); // need OPERX

    o = true;
    assert (o.xltype == xltypeBool);
    assert (o == TRUE);
#pragma warning(push)
#pragma warning(disable: 4805)
    assert (o == true);
#pragma warning(pop)

    o = ErrX(xlerrNA);
    assert (o.xltype == xltypeErr);
    assert (o.val.err == xlerrNA);

    o.Resize(2, 3);
    assert (o.xltype == xltypeMulti);
    assert (o.val.array.rows == 2);
    assert (o.val.array.columns == 3);
    assert (o[0] == ErrX(xlerrNA));
    assert (o(0, 0) ==  ErrX(xlerrNA));

    o = NilX();
    assert (o.xltype == xltypeNil);
    
    o = MissingX();
    assert (o.xltype == xltypeMissing);

The AddIn12 Class

The Excel 2007 SDK defines new data structures and functions to accomodate the the big grid, wide-character strings, and thread-safe functions. The XLOPER12 structure and the Excel12 function replace XLOPER and Excel4. The XLL library adopts a similar convention. Here is how to write the example given above for Excel 2007.

    static AddIn12 xai_square_root12(
        L"?xll_square_root12", XLL_LPOPER12 XLL_DOUBLE12,
        L"SQUARE.ROOT12", L"Number",
        L"Example", L"Compute the square root of Number.."
    );
    LPOPER12 WINAPI
    xll_square_root12(double x)
    {
    #pragma XLLEXPORT
        static OPER12 y;

        if (x <  0)
            y = Err12(xlerrNum);
        else
            y = sqrt(x);

        return &y;
    }

The AddInX Class

It is inconvenient to have to write all your add-in functions twice, so the XLL Library provides a means of writing a single set of source code that can be compiled for either Excel 2007 or for earlier versions of Excel. Here is how to write the function above in a version independent manner.

    // #define EXCEL12 // uncomment to build Excel 2007 specific version
	
    static xcstr xav_square_rootx[] = {
        _T("is the Number for which you want the square root..")
    };
    static AddInX xai_square_rootx(
        _T("?xll_square_rootx"), XLL_LPOPERX XLL_DOUBLEX,
        _T("SQUARE.ROOTX"), _T("Number"),
        _T("Example"), _T("Compute the square root of Number."),
        dimof(xav_square_rootx), xav_square_rootx
    );
    LPOPERX WINAPI
    xll_square_rootx(double x)
    {
    #pragma XLLEXPORT
        static OPERX y;

        if (x <  0)
            y = ErrX(xlerrNum);
        else
            y = sqrt(x);

        return &y;
    }

This example extends the previous examples by showing how to add individual argument help that will be visible in the Function Wizard.

Single Add-in for All Versions

It is possible to create a single add-in that works in all versions of Excel and takes advantage of the new functionality when run in Excel 2007. If we registered xll_square_root12 as L"SQUARE.ROOT" instead of as L"SQUARE.ROOT12 then the registration for the "SQUARE.ROOT" that called xll_square_root would be replaced by the new definition.

The drawback of this is having to write each function twice. One approach to cutting down the amount of code to be written is to use template functions.

    template<class X>
    X* WINAPI
    squarex_root(double x)
    {
        static XOPER<X> y;

        if (x < 0)
            y = XErr<X>(xlerrNum);
        else
            y = sqrt(x);

        return &y;
    }
    static AddIn xai_squarex_root(
        "?xll_squarex_root", XLL_LPOPER XLL_DOUBLE,
        "SQUAREX.ROOT", "Number",
        "Example", "Compute the square root of Number."
    );
    LPXLOPER WINAPI
    xll_squarex_root(double x)
    {
    #pragma XLLEXPORT
        return squarex_root<XLOPER>(x);
    }
    // Ignored if not running in Excel 2007, otherwise this
    // overrides the SQUAREX.ROOT function previously registered.
    static AddIn12 xai_square_root12_(
        L"?xll_squarex_root12", XLL_LPOPER12 XLL_DOUBLE12,
        L"SQUAREX.ROOT", L"Number",
        L"Example", L"Compute the square root of Number."
    );
    LPXLOPER12 WINAPI
    xll_squarex_root12(double x)
    {
    #pragma XLLEXPORT
        return squarex_root<XLOPER12>(x);
    }

This is still somewhat tedious, so another mechanism is provided that uses the AddinX typedefs and requires no code duplication whatsoever.

    // sqrt.cpp - compute square root of a number with error checking
    #include "xll/xll.h"

    using namespace xll;

    static AddInX X_(xai_square_root)(
        TX_("?xll_square_root"), XLL_LPOPERX XLL_DOUBLEX,
        // use TX_("SQUARE.ROOT") to create both SQUARE.ROOT and SQUARE.ROOT12
        _T("SQUARE.ROOT"), _T("Number"),
        _T("Example"), _T("Compute the square root of Number..")
    );
    LPOPERX WINAPI
    X_(xll_square_root)(double x)
    {
    #pragma XLLEXPORT
        static OPERX y;

        if (x <  0)
            y = ErrX(xlerrNum);
        else
            y = sqrt(x);

        return &y;
    }

To create the Excel 2007 version add the following file to the project.

    // sqrt12.cpp - Excel 2007 version of sqrt.cpp
    // EXCEL4  EXCEL12 builds
    // ------  ------- ------
    // undef   undef   both
    // undef   defined Excel12
    // defined undef   Excel4
    // defined defined Excel12
    #if !defined(EXCEL12) && !defined(EXCEL4)
    #define EXCEL12
    #include "sqrt.cpp"
    #endif 	

It is possible to control what is built at compile time using appropriately defined macros as described in the comments above.

Macros

A macro function takes no arguments and returns no values. Macros are executed purely for their side effects. They are also created using the AddIn class(es), but take only two arguments: the C function to be called and the Excel macro name.

    static AddInX xai_now(_T("?xll_now"), _T("XLL.NOW"));
    void WINAPI
    xll_now(void)
    {
    #pragma XLLEXPORT

        // ALERT(CONCATENATE("The time is now ", TEXT(NOW(), "mmmm d, yyyy h:mm AM/PM")), 2)
        ExcelX(xlcAlert
            ,ExcelX(xlfConcatenate
                ,OPERX(_T("The time is now ")) 
                ,ExcelX(xlfText
                    ,ExcelX(xlfNow)
                    ,OPERX(_T("mmmm d, yyyy h:mm AM/PM"))
                )
            )
            ,OPERX(2)
        );
    }
 

If you are familiar with using the SDK, you might think this would leak memory. It does not. see the file xll/lxoper.h for how this is achieved.

You will want to obtain Macrofun.hlp if you write macros.

Frequently Asked Questions

When I open my .xll file in Excel nothing happens.
You need to set macro security to low in order for Excel to register your functions and macros. For Excel 2003 and earlier, go to Tools|Options... and click on Macro Security... in the Security tab. For Excel 2007 from the Office Button click on Excel Options next to Exit Excel on the bottom right. On the Trust Center tab click on Trust Center Settings... On the Macro Settings tab choose Enable all macros.
Why am I seeing a message box claiming Register failed for: X., where X is the Excel name of one of my functions when I open my add-in?
You forgot to insert #pragma XLLEXPORT as the first line in the body of the corresponding C/C++ function.
Why is malloc failing while trying to allocate large amounts of memory?
You must compile with default char unsigned. Properties|C/C++|Language|Default Char Unsigned: Yes(/J)
How come the last arguments in the constructor of AddIn end with two periods?
It is a known feature that persists in the Excel 2007 SDK that the last character gets truncated.
I'm using Vista/Windows 7 and can't veiw the Macrofun.hlp file.
The first question wasn't a question either. You need to install the Windows Help program
Contact KALX