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.
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
OPER
s behave just as you would expect.
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.
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