Building an Excel XLL in C/C++ with VS 2008

As part of a little investigation I'm doing at present, I needed to build some simple native XLLs for testing purposes. This was the first time I had a need to build XLLs since the Excel XLL SDK was updated back in September. The updated Excel 2007 XLL SDK is available for free download here. The online documentation is here. There's also an old support article on how to build an XLL with C++ here – this refers to the Excel 97 SDK, but the information is still valid (although it doesn't use any of the new types such as XLOPER12). The significant changes between the Excel 97 SDK and the Excel 2007 SDK are described here. In building my simple test XLLs, I based my code on the sample code in the SDK. I didn't find any startling new information beyond the documentation, but I did encounter a few interesting issues which I thought I'd share here.

For my purposes, I wanted the simplest possible project. To start off, I created a new Visual C++ | Win32 Application project (called SimpleXll2007), and in the Win32 Application project wizard I changed the Application Type to DLL. When the wizard had generated the starter project, I deleted all the .H and .CPP source files from the project. Then I set the following project settings:

· C++ | Additional include directories: "C:\Program Files (x86)\Excel2007XllSdk\include"

· C++ | Precompiled Headers: Not Using Precompiled Headers

· Linker | General | Output file: $(OutDir)\$(ProjectName).xll

· Linker | General | Additional library directories: "C:\Program Files (x86)\Excel2007XllSdk\lib"

· Linker | Input | Additional dependencies: xlcall32.lib frmwrk32.lib

· Linker | Input | Module definition file: $(ProjectDir)$(ProjectName).def

· Debugging | Command: C:\Program Files (x86)\Microsoft Office\Office12\EXCEL.EXE

 

To explain these: I needed to specify additional search paths for #includes and static linked libs because an XLL project will want to use the Excel XLL SDK headers and libraries (primarily, xlcall.h/xlcall32.lib which essentially constitute the Excel C API, and framewrk.h/frmwrk32.lib which provide additional helper functions). On my Vista machine, I had installed the SDK to C:\Program Files(x86)\Excel2007XllSdk. I had to specify that the target output should have an XLL extension not the default DLL. As one of the standard options for exporting functions, I chose to implement a DEF file. For debugging, I wanted to be able to just press F5 on the project and have VS launch Excel for me.

Next, I added the following new (empty) files to the project:

· SimpleXll2007.def – lists the exported functions.

· UDFs.h – defines the metadata for my UDFs (function type, signature, helpstrings, etc).

· UDFs.c – implementation of my UDFs.

· xlFunctions.c – implementation of the standard XLL exports that Excel will call back on.

 

The DEF file is pretty simple. It lists the standard XLL exports (xlAutoOpen, etc) and the custom UDF exports (SumTwo, etc):

LIBRARY SimpleXll2007.xll

EXPORTS

      ; Standard XLL functions

       xlAutoOpen

       xlAutoClose

       xlAutoRegister12

       xlAutoAdd

       xlAutoRemove

       xlAddInManagerInfo12

      

       ; UDFs

       SumTwo

       GetString

       GetChars

     

The header file (UDFs.h) defines the UDF metadata. As this defines values, not just types, this should probably be in a .C file, but the data is used when registering the UDFs, and the UDF-registering functions are part of the standard XLL exports (xlAutoOpen, etc) – and I wanted to separate out these generic functions from the metadata for my specific UDFs. For each function, I set up an array of strings for the metadata, and put all these arrays into an outer array – this enables me to register the UDF metadata in a simple loop later on. The only downside is that I need the arrays to be regular – that is, each sub-array must have the same number of elements. As some of the elements are for helpstrings for the UDF arguments, and as the number of UDF arguments varies, this forces me to define empty strings for some of my UDF argument help metadata values. The metadata values will eventually be passed to the Excel4/Excel12 function and its variants when the UDFs are registered in my implementation of xlAutoOpen and xlAutoRegister12.

// UDFs.h

#define g_rgNumUDFs 5

#define g_rgUDFdata 11

static LPWSTR g_rgUDFs

[g_rgNumUDFs][g_rgUDFdata] =

{

       {

              L"SumTwo", // Function name/ordinal

              L"JJJ", // Func signature type

              L"SumTwo", // Func name in Func wizard

              L"Arg1, Arg2", // Arg name in Func wizard

              L"1", // Function type

              L"SimpleXll2007", // Category in Func wizard

              L"", // Shortcut (commands only)

              L"", // Help topic

              L"SumTwo function help", // Func help in Func wizard

              L"Help for Arg1", // Arg help in Func wizard

       L"Help for Arg2" // Arg help in Func wizard

       },

       {

              L"GetString",

              L"U",

              L"GetString",

              L"",

              L"1",

              L"SimpleXll2007",

              L"",

              L"",

              L"GetString function help",

              L"",

              L""

       },

       {

              L"GetChars",

              L"C",

              L"GetChars",

              L"",

              L"1",

              L"SimpleXll2007",

              L"",

              L"",

              L"GetChars function help",

              L"",

              L""

       },

};

As you can see, the UDFs are pretty simple. The GetString and GetChars function signatures are almost identical – but I specifically wanted to test a UDF that returns a string as simple type such as a char* (specified as "C" when registering with Excel) as well as one that returns a string as an XLOPER/XLOPER12 ("U"). The SumTwo function returns a long ("J") and takes 2 long args ("JJ"). Descriptions of the function signature identifiers are here.

These functions are implemented in the file UDFs.c (see comments inline below):

// UDFs.c

#include <windows.h>

#include <xlcall.h>

#include <framewrk.h>

#include <stdio.h>

// Example UDF that adds two numbers passed as longs, and

// returns a long.

__declspec(dllexport) long SumTwo(long arg1, long arg2)

{

       return arg1 + arg2;

}

// Example UDF that simply returns a string as an XLOPER12.

// Note that the string is length-prefixed in octal.

__declspec(dllexport) LPXLOPER12 WINAPI GetString (void)

{

       static XLOPER12 xResult;

       xResult.xltype = xltypeStr;

       xResult.val.str = L"\024Hello from GetString";

       return(LPXLOPER12) &xResult;

}

// Example UDF that returns a null terminated string in a char*.

__declspec(dllexport) char* WINAPI GetChars(void)

{

       char* pszNewString = NULL;

       size_t cBytes;

       cBytes = strlen("Hello from GetChars") + 1;

       pszNewString = (char*)malloc(cBytes);

       sprintf_s(pszNewString, cBytes, "Hello from GetChars");

       return pszNewString;

}

 

Finally, my xlFunctions.c file contains the standard XLL exports (xlAutoOpen, etc). This is very generic, and I pretty much just cut+pasted from the SDK sample code for these, with a few minor tweaks. I did remove the sample code for command and menu functions, because I only needed to test worksheet functions. I also sprinkled a few debugPrintfs around for good measure.

// xlFunctions.c

#include <windows.h>

#include <xlcall.h>

#include <framewrk.h>

#include "UDFs.h"

// Compares two strings for equality (case-insensitive).

int lpwstricmp(LPWSTR s, LPWSTR t)

{

       int i;

       if (wcslen(s) != *t)

       {

              return 1;

       }

       for (i = 1; i <= s[0]; i++)

       {

              if (towlower(s[i-1]) != towlower(t[i]))

              {

                     return 1;

              }

       }

       return 0;

}

BOOL APIENTRY DllMain( HANDLE hDLL, DWORD dwReason, LPVOID lpReserved )

{

       switch (dwReason)

       {

       case DLL_PROCESS_ATTACH:

       case DLL_PROCESS_DETACH:

       case DLL_THREAD_ATTACH:

       case DLL_THREAD_DETACH:

       default:

              break;

       }

       return TRUE;

}

// Excel calls xlAutoOpen when it loads the XLL.

__declspec(dllexport) int WINAPI xlAutoOpen(void)

{

       static XLOPER12 xDLL; // The filename of this XLL.

       int i;

       debugPrintf("xlAutoOpen\n");

       // Fetch the name of this XLL. This is used as the first arg

       // to the REGISTER function to specify the name of the XLL.

       Excel12f(xlGetName, &xDLL, 0);

       debugPrintf("XLL Name: %S\n", xDLL.val.str);

       // Loop through the g_rgUDFs[] table, registering each

       // function in the table using xlfRegister.

       for (i = 0; i < g_rgNumUDFs; i++)

       {

              Excel12f(xlfRegister, 0, 1 + g_rgUDFdata,

                     (LPXLOPER12) &xDLL,

                     (LPXLOPER12) TempStr12(g_rgUDFs[i][0]),

                     (LPXLOPER12) TempStr12(g_rgUDFs[i][1]),

                     (LPXLOPER12) TempStr12(g_rgUDFs[i][2]),

                     (LPXLOPER12) TempStr12(g_rgUDFs[i][3]),

                     (LPXLOPER12) TempStr12(g_rgUDFs[i][4]),

                     (LPXLOPER12) TempStr12(g_rgUDFs[i][5]),

                     (LPXLOPER12) TempStr12(g_rgUDFs[i][6]),

                     (LPXLOPER12) TempStr12(g_rgUDFs[i][7]),

                     (LPXLOPER12) TempStr12(g_rgUDFs[i][8]),

                     (LPXLOPER12) TempStr12(g_rgUDFs[i][9]),

                     (LPXLOPER12) TempStr12(g_rgUDFs[i][10])

                     );

       }

       // Free the XLL filename.

       Excel12f(xlFree, 0, 1, (LPXLOPER12) &xDLL);

       return 1;

}

// Excel calls xlAutoClose when it unloads the XLL.

__declspec(dllexport) int WINAPI xlAutoClose(void)

{

       int i;

       debugPrintf("xlAutoClose\n");

       // Delete all names added by xlAutoOpen or xlAutoRegister.

       for (i = 0; i < g_rgNumUDFs; i++)

       {

              Excel12f(xlfSetName, 0, 1, TempStr12(g_rgUDFs[i][2]));

       }

       return 1;

}

// Excel calls xlAutoRegister12 if a macro sheet tries to register

// a function without specifying the type_text argument.

__declspec(dllexport) LPXLOPER12 WINAPI xlAutoRegister12(LPXLOPER12 pxName)

{

       static XLOPER12 xDLL, xRegId;

       int i;

       debugPrintf("xlAutoRegister12\n");

       xRegId.xltype = xltypeErr;

       xRegId.val.err = xlerrValue;

       for (i = 0; i < g_rgNumUDFs; i++)

       {

              if (!lpwstricmp(g_rgUDFs[i][0], pxName->val.str))

              {

                     Excel12f(xlfRegister, 0, 1 + g_rgUDFdata,

                           (LPXLOPER12) &xDLL,

                           (LPXLOPER12) TempStr12(g_rgUDFs[i][0]),

                           (LPXLOPER12) TempStr12(g_rgUDFs[i][1]),

                           (LPXLOPER12) TempStr12(g_rgUDFs[i][2]),

                           (LPXLOPER12) TempStr12(g_rgUDFs[i][3]),

                           (LPXLOPER12) TempStr12(g_rgUDFs[i][4]),

                           (LPXLOPER12) TempStr12(g_rgUDFs[i][5]),

                           (LPXLOPER12) TempStr12(g_rgUDFs[i][6]),

                           (LPXLOPER12) TempStr12(g_rgUDFs[i][7]),

                           (LPXLOPER12) TempStr12(g_rgUDFs[i][8]),

                           (LPXLOPER12) TempStr12(g_rgUDFs[i][9]),

                           (LPXLOPER12) TempStr12(g_rgUDFs[i][10])

                           );

                     // Free the oper returned by Excel.

                     Excel12f(xlFree, 0, 1, (LPXLOPER12) &xDLL);

                     return(LPXLOPER12) &xRegId;

              }

       }

       return(LPXLOPER12) &xRegId;

}

// When you add an XLL to the list of active add-ins, the Add-in

// Manager calls xlAutoAdd() and then opens the XLL, which in turn

// calls xlAutoOpen.

__declspec(dllexport) int WINAPI xlAutoAdd(void)

{

       debugPrintf("xlAutoAdd\n");

       return 1;

}

// When you remove an XLL from the list of active add-ins, the

// Add-in Manager calls xlAutoRemove() and then

// UNREGISTER("SimpleXll2007.XLL").

__declspec(dllexport) int WINAPI xlAutoRemove(void)

{

       debugPrintf("xlAutoRemove\n");

       return 1;

}

// The Excel Add-in Manager calls xlAddInManagerInfo12 function

// to find the long name of the add-in.

__declspec(dllexport) LPXLOPER12 WINAPI xlAddInManagerInfo12(LPXLOPER12 xAction)

{

       static XLOPER12 xInfo, xIntAction;

       debugPrintf("xlAutoAddInManagerInfo12\n");

       // This code coerces the passed-in value to an integer.

       Excel12f(xlCoerce, &xIntAction, 2, xAction,

              TempInt12(xltypeInt));

       if (xIntAction.val.w == 1)

       {

              // Note that the string is length-prefixed in octal.

              xInfo.xltype = xltypeStr;

              xInfo.val.str = L"\016SimpleXll2007";

       }

       else

       {

              xInfo.xltype = xltypeErr;

              xInfo.val.err = xlerrValue;

       }

       // Word of caution: returning static XLOPERs/XLOPER12s is

       // not thread-safe. For UDFs declared as thread-safe, use

       // alternate memory allocation mechanisms.

       return(LPXLOPER12) &xInfo;

}

After building the project, F5 runs Excel. You still need to register the XLL with Excel, and you can do this in the UI by going to File | Excel Options | Add-ins. Drop down the Manage listbox, and select Excel Add-ins to display the Excel Add-ins dialog (not the COM Add-ins dialog). Click the Browse button and navigate to the SimpleXll2007.xll to register this XLL with Excel:

 

After this, you can go to the Formulas tab and click Insert Function – you can see some of the UDF metadata surfaced in the Insert Function wizard dialog: