Custom Functions in Excel: Part IV - XLLs

Introduction

We’ve looked at VBA, Automation Add-ins, and RTDs in previous posts. XLLs are significantly faster, and allow the developer to define the names/parameters of the functions. They’re also not that difficult to implement, although setting up the definitions of your functions can be a bit tricky. Unlike the other C++ add-ins, the XLL is not a COM server, it uses the Excel C API.

Prerequisites

You’ll need the Excel 2010 XLL SDK or use the “previous version” if you have Excel 2007.

Getting Started

An XLL is just a DLL that exports a set of standard functions that Excel will call. Initially, I was going to create something from scratch for this post but the example included with the SDK already works, so instead I’ll focus on a couple of ways that it can be enhanced. We’ll improve the process for registering functions so the code is easier to write and easier for documenting the functions/parameters so the user will know how to use it.

Framework

Before working with the example, we need to build the Framework project included in the samples directory. Open “Excel2010XLLSDK\SAMPLES\FRAMWRK\Framework.sln”. After it’s converted if it needs to be converted, just build it.

XLL Sample

Open the sample “Excel2010XLLSDK\SAMPLES\EXAMPLE\Example.sln” and if you’re using a newer version of Visual Studio, it will auto-convert it. If you open example.c, you’ll notice a few missing include files.

To fix that, just click on Example project in the Solution Explorer and in the Property Pages, go to Configuration Properties and VC++ Directories and add the include directories and library directories. Add “<BASE_DIR>\Excel2010XLLSDK\INCLUDE” and “<BASE_DIR>\Excel2010XLLSDK\SAMPLES\FRAMWRK” to the Include directories. Add “<BASE_DIR>\Excel2010XLLSDK\LIB” and “<BASE_DIR>\Excel2010XLLSDK\SAMPLES\FRAMWRK\Debug” to the Library directories. In my case, I replace BASE_DIR with “C:\2010 Office System Developer Resource”, but yours might be different.

Now you should be able to build the project.

Easy Function Registration

Right now, the registration of functions looks like this:

static LPWSTR rgFuncs[rgFuncsRows][7] = {
    {L"CallerExample",		L"I", L"CallerExample"},
    {L"debugPrintfExample",	L"I", L"debugPrintfExample"},
    {L"EvaluateExample",		L"I", L"EvaluateExample"},
    {L"Excel12fExample",		L"I", L"Excel12fExample"},
    {L"Excel12Example",		L"I", L"Excel12Example"},

// Then later on...

        for (i=0;i < rgFuncsRows;i++) 
    {
        Excel12f(xlfRegister, 0, 4,
            (LPXLOPER12)&xDLL,
            (LPXLOPER12)TempStr12(rgFuncs[i][0]),
            (LPXLOPER12)TempStr12(rgFuncs[i][1]),
            (LPXLOPER12)TempStr12(rgFuncs[i][2]));
    }

With the helper function, we will be able to register functions with a variable number of arguments and we can define the help associated with the parameters that will show up in the function wizard. The XLLRegisterInfo structure could also be defined dynamically so we could use that to provide localized strings for the descriptions/help.

VOID RegisterHelper(struct XLLRegisterInfo registryInfo)
{
    static XLOPER12 xDLL, xRegId;
    static LPXLOPER12 args[260];
    int count;
    int i;    
    
    count = 10 + registryInfo.argumentCount;

    /*
        Get the name of the DLL.
    */
    Excel12f(xlGetName, &xDLL, 0);    

    if(registryInfo.argumentCount == 0)
    {
        Excel12f(xlfRegister, 0, count,
            (LPXLOPER12)&xDLL, /* moduleText */
            (LPXLOPER12)TempStr12(registryInfo.procedure),
            (LPXLOPER12)TempStr12(registryInfo.typeText),
            (LPXLOPER12)TempStr12(registryInfo.functionText),
            (LPXLOPER12)TempStr12(registryInfo.argumentText),
            (LPXLOPER12)TempInt12
            (registryInfo.macroType), /* function listed in function wizard */
            (LPXLOPER12)TempStr12(registryInfo.categoryName),
            (LPXLOPER12)TempStr12(L""), /* shortcut text */
            (LPXLOPER12)TempStr12(registryInfo.helpTopic),
            (LPXLOPER12)TempStr12(registryInfo.functionHelp));
    }
    else
    {
        count = 0;
        args[count++] = (LPXLOPER12)&xDLL; /* moduleText */
        args[count++] = (LPXLOPER12)TempStr12(registryInfo.procedure);
        args[count++] =    (LPXLOPER12)TempStr12(registryInfo.typeText);
        args[count++] = (LPXLOPER12)TempStr12(registryInfo.functionText);
        args[count++] = (LPXLOPER12)TempStr12(registryInfo.argumentText);
        args[count++] =    
        (LPXLOPER12)TempInt12(registryInfo.macroType); /* function listed in function wizard */
        args[count++] = (LPXLOPER12)TempStr12(registryInfo.categoryName);
        args[count++] =    (LPXLOPER12)TempStr12(L""); /* shortcut text */
        args[count++] =    (LPXLOPER12)TempStr12(registryInfo.helpTopic);
        args[count++] = (LPXLOPER12)TempStr12(registryInfo.functionHelp);
        
        for(i = 0; i < registryInfo.argumentCount; i++)
        {
            args[count++] = (LPXLOPER12)TempStr12(registryInfo.argumentHelp[i]);
        }
 
        Excel12v(xlfRegister, 0, count,
            args);
    }
}

Of course, you might want the definition of XLLRegisterInfo so here it is:

struct XLLRegisterInfo
{
    /**
        Name of the procedure to be registered.
    */
    LPWSTR procedure;
    /** 
        The type text see pxTypeText here: http://msdn.microsoft.com/en-us/library/bb687900.aspx
    */
    LPWSTR typeText;
    /**
        Name of the function in Excel.
    */
    LPWSTR functionText;
    /**
        String of Letters that represent arguments/types. See pxArgumentText
        here: http://msdn.microsoft.com/en-us/library/bb687900.aspx
    */
    LPWSTR argumentText;
    /**
        Type of function. 0 - Macro sheet equivalent, 1 - Worksheet Functions, 2 - Commands.
        When in doubt use 1;
     */
    int macroType;

    /**
        Name of the category to add the function to.
    */
    LPWSTR categoryName;

    /**
        Commands only.
    */
    LPWSTR shortcutText;

    /**
        Reference to a help file (.chm or .hlp). 
        Use form: filepath!HelpContextID or http://address/path_to_file_in_site!0
    */
    LPWSTR helpTopic;
    /**
        String that describes the function in the function wizard.
    */
    LPWSTR functionHelp;

    /**
      * Number of descriptions in argumentHelp.
      */
    int argumentCount;

    /**
        Array of Argument help strings. 
    */
    LPWSTR argumentHelp[15];
};

The argumentHelp field can be anything up to 245 arguments in 2007, in 2003 it’s 20. Pick something appropriate for your add-in.

Once you have that defined, you can replace rgFuncs with an array of these structures.

struct XLLRegisterInfo rgFuncs[] = 
{ 
    {
        L"CalcCircum" /* procedure */, L"BB" /*typeText*/, 
        /* functionText */ L"CalcCircum", 
        /*argumentText*/ L"Radius", 1, L"Examples", 
        L"" /*shortcutText*/, L"" /*helpTopic*/, 
        L"Calculates the circumferance of a circle." 
        /* functionHelp */, 1 /*argCount */,
        {L"Radius of the circle. " 
        /*argumentHelp1 pad with space, Excel bug*/}
    },
    { NULL }
};

I added the NULL at the end to mark the end of the array. This way, I don't need a rgFuncsCount constant that I'll have to update every time I add/remove a function. Also remember to add a space to the last argumentHelp. The reason for that is that there is a bug in Excel that drops the last character of the last argumentHelp. Don't expect that bug to ever be fixed, but just in case I use a space, it looks more professional than a smiley face.

for(i=0;rgFuncs[i].procedure != NULL;i++)
    RegisterHelper(rgFuncs[i]);

Now, I can define functions with multiple arguments and proper help for each argument and all the additional complexity is hidden in RegisterHelper. The code for xlAutoOpen is now a lot more readable too and it handles different functions having different numbers of arguments.