The AddIn object stores The C function name (Procedure), the C signature (TypeText - a character string specifying the return type and C function argument types), the name Excel uses for your function (FunctionText), and the argument prompt (ArgumentText) that shows up after you type the function name and hit Ctrl-Shift-A. You can also specify the category (Category) and help text (FunctionHelp) used in the Function Wizard. This information is supplied to xlfRegister when xlAutoOpen is called by Excel.

Functions

There is more than one way to create AddIn objects. The simplest way is to specify the information Excel needs directly:

static AddIn xai_function(
    "?xll_function", XLL_LPOPER XLL_DOUBLE,
    "XLL.FUNCTION", "Number",
    "My Category", "Description of what the function does."
);

The constructor for AddIn gets called by the linker when the add-in is opened and simply stores the information that will be needed by Register when Excel calls xlAutoOpen. The symbols XLL_LPOPER and XLL_DOUBLE are #define'd to be the character strings "P" and "B", respectively. The C preprocessor concatenates these for you which results in the TypeText argument being "PB". See defines.h for a complete list.

Another way to do this is:

    
static AddIn xai_function(
    Function(XLL_LPOPER, "?xll_function", "XLL.FUNCTION")
    .Arg(XLL_DOUBLE, "Number", "is a number ")
    .Category("My Category")
    .FunctionHelp("Description of what the function does")
);

One slight difference is that the Function Wizard will now tell you that Number "is a number".

You still need to implement the actual function to be called. Here is how to do that:

LPOPER WINAPI
xll_function(double x)
{
#pragma XLLEXPORT
      static OPER oResult;

      oResult = x;

      return &oResult;
}

 

Some things to note: When specifying the procedure in the AddIn object you must prepend a question mark. LPOPER is the same as OPER*. An OPER is a C++ datatype that corresponds to a cell or a two dimensional range of cells. Every function that you register with Excel must be declared WINAPI. The first line of the body of the function has to be #pragma XLLEXPORT. We are returning the address of the OPER so it must be declared static so what is being pointed at will be around when Excel takes a look. Note we are assigning a double to an OPER. I went to a lot of trouble to make OPERs behave just as you would expect.

Macros

Macros are a special kind of function. They take no arguments and return the int 1 on success and 0 if things go pear-shaped. They can be used to do things functions can't: have side effects. They can do anything you can do, change cell values, format cells, insert or delete data in the spreadsheet. Here is a simple example:

static AddIn xai_macro("?xll_macro", "XLL.MACRO");
int WINAPI
xll_macro(void)
{
#pragma XLLEXPORT
    try {
        Excel<XLOPERX>(xlcAlert, "Hello macro!");
    }
    catch (const std::exception& ex) {
        XLL_ERROR(ex.what());

        return 0;
    }

    return 1;
}

The first line can also be replaced by

static AddIn xai_macro(
    Macro("?xll_macro", "XLL.MACRO")
);

to make it clearer that this is a macro.

Documentation

So you built your killer add-in and everybody and their third cousin are now using it, but they have questions about how it works. Now you are spending all your time answering questions instead of working on your next-gen killer app.

Here is the solution: have them select the cell with your function in it, start the Function Wizard (by clicking on the little fx thingy next to the formula bar), then click on Help on this function. Presto! The help file for the function pops up. After that, spin on your heal and walk off with a smug look on your face knowing they will never pester you again before reading your documentation. (Ignore that last sentence.)

Oh, wait. You haven't built that yet.

For every category you create you can associate an overview page that introduces all the functions in your category. To do that you need to create a special AddIn object comme ça

static AddIn xai_category(
    Document(_T("Category"))
    .Documentation(
       _T("This is documentation for ")
       _T("the functions belonging to Category. ")
);

Next, start the add-in and run (Alt-F8) the macro XLL.DOC. This will create a compiled help file (.chm) in the Debug directory of your build. Voilà, now when you click on Help on this function your shiny new help file will appear.

When you distribute your add-in, just be sure to put the help file in the same folder as the add-in.

This works great if your documentation is only a couple of lines. It is also possible to create the documentation in an XML file. You will need to know about MAML in order to create this file. If the file is called category.xml and is located in your project directory, pick it up with

static AddIn xai_category(
    Document(_T("Category"))
    .Documentation(XML_FILE("category.xml"))
);

Note: XML_FILE requires the compiler to use full file names.
Configuration Properties > C/C++ > Advanced > Use Full Paths : Yes

Last edited May 26, 2013 at 8:41 PM by keithalewis, version 30

Comments

No comments yet.