Skip to content

xlladdins/xll

master
Switch branches/tags

Name already in use

A tag already exists with the provided branch name. Many Git commands accept both tag and branch names, so creating this branch may cause unexpected behavior. Are you sure you want to create this branch?
Code

Files

Permalink
Failed to load latest commit information.
Type
Name
Latest commit message
Commit time
 
 
 
 
 
 
 
 
 
 
 
 
 
 
xll
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 

xll - a library for creating Excel add-ins

build workflow

This library makes it simple to call C, C++, or even Fortran code from Excel. It is much easier to use than the Microsoft Excel Software Development Kit. There are newer technologies available using C# and JavaScript that are appropriate for certain problems, but if you need the highest possible numerical performance from Excel this library is for you.

Plug your code, or a third party library, into Excel by writing a thin wrapper that gathers arguments from Excel, call any function, and return the result. Use the full power of Excel to explore and perfect your code. Anyone can use your handiwork by opening the self-contained .xll file you produce. The xll library can also generate documentation integrated into Excel's help system. Hopefully you will get to the quality problem of others using your product and you can tell them to go to the Function Wizard and click on Help with this function so you can get back to writing more cool stuff.

The major usability enhancement for developers in the latest version is that all strings are now UTF-8. They are a L"ot" nicer to use than wide character strings. The library still provides high performance access to numeric arrays and lets you easily create handles for embedding C++ objects. This lets you use Excel to orchestrate code operating on memory up to machine limits that runs at native speed. You can also use single inheritance to make your software more convenient to use.

Prerequisites

Windows 10 or Windows 11
The Excel SDK is not supported on MacOS. You will have to install a Windows virtual machine on your Mac. I've had success with VirtualBox.

Visual Studio
Use the Community Edition and install the Desktop development with C++ and GitHub Extension for Visual Studio workload.

Microsoft Excel
Install the 64-bit version of Office Microsoft 365 for the best experience.

Get Started

Use this template to generate a new repository.
Open in Visual Studio and double click on the solution (.sln).
Press F5 to build the add-in and start Excel with it loaded in the debugger.

If your add-in does not load you should check your security settings. From the File tab select Options, then Trust Center. Click on the Trust Center Settings... button. Select Macro Settings and choose Enable VBA Macros. Do not open add-ins you do not trust. If you download an xll from the web you may have to right click on it, Properties and choose Unblock at the bottom of the dialog.

AddIn

The xll::AddIn class is used to register Excel functions and macros. AddIn objects should be defined at the global scope so they will be constructed when the xll is loaded. The constructors store information Excel needs when xlAutoOpen is called. The xll library implements this for you.

Function

To register a new C++ add-in function that can be called from Excel create an AddIn object with a Function argument that has information Excel needs to register your function: the return type, the C++ function name, the Excel name, and a list of arguments with their type, name, and short description. You can optionally specify function help for the Function Wizard and the category Excel should use. If you have documentation available at a URL then provide a link to a help topic.

This library uses UTF-8 instead of multibyte character sets and Unicode (UTF-16, UCS-2). The Unicode wars are over, the dust has settled, and UTF-8 is the clear winner. Joel can get you up to speed.

#include <cmath>
// Uncomment to build for versions of Excel prior to 2007.
// #define XLL_VERSION 4
#include "xll/xll.h"

using namespace xll;

AddIn xai_tgamma(
    Function(XLL_DOUBLE, "xll_tgamma", "TGAMMA")
    .Arguments({
        Arg(XLL_DOUBLE, "x", "is the value for which you want to calculate Gamma.")
    })
    .FunctionHelp("Return the Gamma function value.")
    .Category("CMATH")
    .HelpTopic("https://docs.microsoft.com/en-us/cpp/c-runtime-library/reference/tgamma-tgammaf-tgammal")
);
double WINAPI xll_tgamma(double x)
{
#pragma XLLEXPORT
    return tgamma(x);
}

The add-in registers the function TGAMMA with Excel to call the C++ function xll_tgamma that returns a double. It has one argument that is also a double and will show up in the Excel function wizard under the CMATH category with the specified function help. When Help on this function is clicked it will open the help topic for the Microsoft C runtime library reference for tgamma.

Compare this to the built-in Excel functon GAMMA.

All functions called from Excel must be declared WINAPI which is defined to be __stdcall. This is an artifact of the original versions of Excel being written in Pascal. The line #pragma XLLEXPORT causes the function to be exported from the dll so it will be visible to Excel. No need for old-fashioned .DEF files.

The function xll_tgamma calls the tgamma function declared in the <cmath> library. Recall the Gamma function is Γ(x) = ∫0 tx - 1 e-t dt, x > 0. It satisfies Γ(x + 1) = x Γ(x) for x > 0. Since Γ(1) = 1 we have Γ(x + 1) = x! if x is a non-negative integer. For applications you may want to use the lgamma function that returns the natual logarithm of Γ. For x ≥ 171.62, Γ(x) is greater than the largest IEEE 64-bit double value.

Macro

To register a new add-in macro call AddIn with a Macro argument. It takes two string arguments: the name of the C++ function to be called and the name for Excel to use. Macro functions take no arguments and return an int that is non-zero if it succeeds or zero if it fails. Don't forget WINAPI in the function definition and #pragma XLLEXPORT in the function body so Excel can load it.

AddIn xai_macro(Macro("xll_macro", "XLL.MACRO"));
int WINAPI xll_macro(void)
{
#pragma XLLEXPORT
    Excel(xlcAlert, 
        Excel(xlfConcatenate,
            OPER("XLL.MACRO 召唤 with активный  cell: "), // use utf-8!
            Excel(xlfReftext, 
                Excel(xlfActiveCell), 
                OPER(true) // A1 style instead of R1C1
            )
        ),
        OPER(2), // general information
        OPER("https://xlladdins.github.io/Excel4Macros/alert.html")
    );
	
    return TRUE;
}

The Help button in the alert dialog will take you to documentation for the ALERT macro.

The name of the AddIn object is arbitrary. I use xai_ as a prefix for all E‌x‌cel a‌dd-‌i‌n objects as a convention.

The 4 and 12 Suffixes

The Excel SDK has two versions of most data types, ones for pre 2007 Excel and ones for 2007 Excel and later. The new verions allow for large grids and wide character Unicode strings. The new data types have names with the suffix 12. This library makes it possible to write add-ins that work with all version of Excel. It uses a technique similar to the Windows TCHAR technique. That uses the suffix A for char (ANSI) functions and the W suffix for wide character (Unicode) functions. When the suffix is omitted the appropriate function will be called based on macros set before including <tchar.h>. The major difference is that the generic functions always take UTF-8 strings. The xll library converts these to the appropriate string types for Excel.

The xll library controls this by the macro XLL_VERSION. Define it to be 4 for pre 2007 Excel and 12 for Excel 2007 and later before including xll/xll.h. By default it is defined to be 12. The file traits.h uses the traits pattern to parameterize by these two types.

The SDK uses Excel4 (or Excel4v) and Excel12 (or Excel12v) to call Excel functions from your add-in. If you need to know the Excel version at runtime call XLCallVer. It returns 0x500 (oddly enough) for version 4 and 0x0c00 for version 12 (hexidecimal C).

Note the original SDK used Excel4 and Excel4v but not XLOPER4. Obviously.

You can get finer grained information by calling the GET.WORKSPACE function with argument 2. The return value is a string, for example "5.0", with the exact version of Excel. Using the SDK the call would be Excel4(xlfGetWorkspace, &version, 1, &two) where version and two are XLOPERs with two = {.val = {.int = 2}, .xltype = xltypeInt}. You must call Excel4(xlFree, 0, 1, &version) to release the memory Excel allocated for the version string. Using the xll add-in library the call would be OPER version = Excel(xlfWorkspace, OPER(2)). The destructor for version will release the memory when it goes out of scope.

Excel Data Types

At the C SDK level the fundamental data types are the XLOPER and XLOPER12 structs for pre and post Excel 2007 respectively. Each is a discriminated union where the .xltype member determines the type.

/*
** XLOPER12 structure 
**
** Excel 12's fundamental data type: can hold data
** of any type. Use "U" as the argument type in the 
** REGISTER function.
**/

typedef struct xloper12 
{
	union 
	{
		double num;				    /* xltypeNum */
		XCHAR *str;				    /* xltypeStr */
		BOOL xbool;				    /* xltypeBool */
		int err;				    /* xltypeErr */
		int w;
		struct 
		{
			WORD count;			    /* always = 1 */
			XLREF12 ref;
		} sref;						/* xltypeSRef */
		struct 
		{
			XLMREF12 *lpmref;
			IDSHEET idSheet;
		} mref;						/* xltypeRef */
		struct 
		{
			struct xloper12 *lparray;
			RW rows;
			COL columns;
		} array;					/* xltypeMulti */
		struct 
		{
			union
			{
				int level;			/* xlflowRestart */
				int tbctrl;			/* xlflowPause */
				IDSHEET idSheet;	/* xlflowGoto */
			} valflow;
			RW rw;				    /* xlflowGoto */
			COL col;			    /* xlflowGoto */
			BYTE xlflow;
		} flow;						/* xltypeFlow */
		struct
		{
			union
			{
				BYTE *lpbData;	    /* data passed to XL */
				HANDLE hdata;		/* data returned from XL */
			} h;
			long cbData;
		} bigdata;					/* xltypeBigData */
	} val;
	DWORD xltype;
} XLOPER12, *LPXLOPER12;

The burden is on you to set the appropriate .val members for each .xltype. The xll library provides the C++ class OPER to help you with this. Although C++ is strongly typed the OPER class is designed to behave much like a cell in a spreadsheet. E.g., OPER o = 1.23 results in o.xltype == xltypeNum and o.val.num == 1.23. Assigning a string o = "foo" results in a counted string with o.xltype == xltypeStr and o.val.str == "\03foo" (== {3, 'f', 'o', 'o')}). The C++ class for OPER takes care of all memory managment so it acts like a built-in type. If it doesn't 'do the right thing' when you use it let me know because that would be a design flaw on my part.

A cell (or a 2-dimensional row-major range of cells) corresponds to the OPER type defined in the xll namespace. It is a variant type that can be a number, string, boolean, reference, error, multi (if it is a range), missing, nil, simple reference, multiple reference or integer. The xltype member indicates the type and can be one of xltypeNum, xltypeStr, xltypeBool, xltypeRef, xltypeErr, xltypeMulti, xltypeMissing, xltypeNil, xltypeSRef, xltypeRef, or xltypeInt.

Excel cells can be floating point doubles or various integer types. These are indicated by, XLL_DOUBLE, XLL_WORD, ..., XLL_LONG. The corresponding arguments in C functions can be declared as DOUBLE, WORD, ..., LONG but you can use double, unsigned, and int if you prefer. Integer and long types are both 32-bit.

Excel has two flavors of strings: counted Pascal strings and null terminate C strings. Early versions of Excel involved Pascal which uses counted strings where the first character is the length of the string. Excel 2007 introduced wide character strings having 16-bit characters. The old limit was 255 characters to a string. The post Excel 2007 limit is 65535 characters. You can tell Excel to give you a counted or null terminated char* string by specifying the XLL_PSTRING4 of XLL_CSTRING4 data type in the AddIn constructor. Use XLL_PSTRING12 or XLL_CSTRING12 to tell Excel to give you a wchar_t*. For maximum portability use XLL_PSTRINGX or XLL_CSTRINGX with corresponding argument TCHAR*.

OPERs are specializations of the XOPER class which publicly inherits from the XLOPERX struct defined the header file XLCALL.H. More precisely, OPER4 inherits from XLOPER and OPER12 inherits from XLOPER12. These are shorthand for XOPER<XLOPER> and XOPER<XLOPER12>. This permits anOPER to be used anywhere a const XLOPERX& can be used. The Excel SDK structs know nothing about memory management so the OPER constructors make a copy of the data from a XLOPERX.

It is permissable to have multis that contain other multis and can be nested to any depth. Multis having two columns with the first column containg strings are quite similar to JSON objects. You can use the Excel built-in function DGET to access values via DGET(multi, 2, key) to obtain the value (in columns 2) of the multi corresponding to key.

The default constructer of OPER creates an object of type xltypeNil. Do not confuse this with the "#NULL!" error type that indicates an empty intersection of two ranges. The OPER Nil is predefined. Use Nil4 for the XLOPER version and Nil12 for a XLOPER12.

All standard error types are predefined with names corresponding to the error. For example, ErrNull is the OPER with xltype = xltypeErr and val.err == xltypeNull. Both ErrNull4 and ErrNull12 are also predefined.

The missing type is used only for function arguments. It indicates no argument was provided by the calling Excel function. This is predefined as Missing,Missing4 and Missing12. It is an error to return this type from a function.

Specifying Argument Types

A feature of the Excel C SDK is that it allows you to specify more argument types than the .xltype field allows. You can use this to your advantage to have Excel check argument types before calling your function. If someone using your code calls a function with an invalid argument type then Excel returns #VALUE!. On the other hand, it does not allow you to check the argument type and display a more informative error message. If you want to do this, specify the argument as XLL_LPXLOPER to get a pointer to the fundamental XLOPER Excel data type.

Another feature is that you can tell Excel you do not want it to hand you reference types. If you specify the argument type as XLL_LPOPER then Excel will coerce the reference and hand you the value it refers to. If the reference is a 2-dimensional range you will get an xltypeMulti. If the reference is a single cell then you will get an OPER with xltype corresponding to the cell value.

You can also use this to provide pointers to internal Excel data structures that can be passed to C++ functions and avoid copying data.

The C SDK uses a character string to indicate function signatures. The xll library defines XLL_type character strings to make these easier to use.

FP Data Type

The xll::FPX data type is a two dimensional array of floating point numbers. It is the fastest way of interacting with numerical data in Excel. All other APIs require the data to be copied out of Excel then back again. See potrf.cpp for an example of how to use this. It calls the FORTRAN function DPOTRF from the LAPACK library to perform a Cholesky decomposition.(Yes, you can easily call FORTRAN, and C of course, from C++). A 1000 x 1000 matrix takes about 0.3 seconds on my old Surface Pro 4 1GHz laptop.

There are structs defined in XLCALL.H for versions of Excel prior to 2007 as struct _FP and struct _FP12 for later versions. These are typedef'd as FP and FP12 and reside in the global namespace.

The classes xll::FP4 and xll::FP12 make these into well-behaved C++ value types. Use _FPX (or _FP4/_FP12) to get the appropriate C Excel type in the global namespace to use for arguments and return types. Excel doesn't know about anything in the xll namespace.

Since FPX does not inherit from the C structs you must use the FPX::get() member function to get a pointer to the underlying struct. This is used to return arrays to Excel where the return type is XLL_FPX. Since you are returning a pointer you must make sure the memory at which it points continues to exist after the function returns. Typically this is done by declaring a static xll::FPX in the function body.

Use xll::FPX a(2,3) to create a 2 by 3 array of doubles and a(1,0) to access the second row, first column (indexing is 0-based) of a. The same element can be accessed using one-dimesional indexing via a[3] since data are stored in row-major order. Use the member function resize to resize the array.

The FPX data type also has member functions for rows, columns, and size. To be STL friendly the member functions begin and end are provided for both const and non-const iterators over array elements.

String

The xll library uses only UTF-8 strings but Excel (post 2007) uses either 8-bit ASCII unsigned char* or 16-bit wide character unsigned short* strings. The easiest way of getting strings in and out of Excel is to use an OPER of type xltypeStr. Use OPER::is_str() to detect if it is a string. The library will take care of encoding and decoding strings for you.

To get strings directly from Excel specify the argument as XLL_PSTRING, XLL_CSTRING, XLL_PSTRING12, or XLL_PSTRING12. The P indicates the string is counted with the first character being its length. The C indicates the string will be null terminated as is the custom in C. You can return strings directly to Excel by specifying the appropriate return type in Function.

When returning a pointer to an OPER that is a string you must indicate to Excel how the memory was allocated. If the OPER was allocated on the call stack then you must set the xlbitDLLFree bit using o.xltype |= xlbitDLLFree. If Excel allocated the string then you must set the xlbitXLFree bit. An OPER returned from a call to Excel has this bit set already. The easiest method is to declare the OPER as static so the memory will stick around after the function returns, however this is not thread safe.

Multi

The xltypeMulti type is a two dimensional array of OPERs. Specify the type as XLL_LPOPER as the AddIn argument type. It has a val.array.lparray pointer to a row major array of OPERs having dimension val.array.row by val.array.columns. The constructor takes two integers indicating the number of rows and columns. It can be indexed linearly with OPER::operator[](int i) or OPER::operator()(int row, int column) for two dimensional arrays.

If the argument type is specified to be XLL_LPXLOPER and the argument is a reference then Excel with provide you with a single reference type, xltypeSRef, or a multiple reference type, xltypeRef. If you specify XLL_LPOPER and the argument is a reference then Excel calls xlCoerce on the reference to get its value before handing it to you.

When returning a pointer to an OPER that is a multi you must indicate to Excel how the memory was allocated. If the OPER was allocated on the call stack then you must set the xlbitDLLFree bit using o.xltype |= xlbitDLLFree. If Excel allocated the multi then you must set the xlbitXLFree bit. An OPER returned from a call to Excel has this bit set already. The easiest method is to declare the OPER as static so the memory will stick around after the function returns, however this is not thread safe.

Handle

Handles are used to embed C++ objects in Excel. Call xll::handle<T>(T*) using new to create an object of type T. The call xll::handle<T> h(new T(...)) creates a handle h to an object of type T using any constructor for T. If the cell a function is being called from contains a handle returned by a previous call, then the corresponding C++ object is deleted and the new handle is returned to the cell.

Use h.ptr() to get the underlying C++ pointer and h.get() to get the handle to be returned to Excel. The latter has type HANDLEX and is specified in add-in arguments as XLL_HANDLEX.

To access a handle that has been created use xll::handle<T>(HANDLEX); to converts a handle to a pointer. If the pointer was not created by a call to handle<T>(T*) then it is set to nullptr and the value of h.get() is the double 0.

The xll::handle class has a member function operator->() so h->member(...) works as if h were a T*.

For example if we have the class

class base {
    OPER x;
public:
    base(const OPER& x) : x(x) { }
    OPER& get() { return x; }
};

then we can embed base objects in Excel using

AddIn xai_base(
    Function(XLL_HANDLEX, "?xll_base", "XLL.BASE")
    .Arguments({
        Arg(XLL_LPOPER, "x", "is a cell or range of cells")
    })
    .FunctionHelp("Return a handle to a base object.")
    .Uncalced() // Required for functions creating handles!!!
);
HANDLEX WINAPI xll_base(LPOPERX px)
{
#pragma XLLEXPORT
    xll::handle<base> h(new base(*px));

    return h.get();
}

and access them with

AddIn xai_base_get(
    Function(XLL_LPOPER, "?xll_base_get", "XLL.BASE.GET")
    .Arguments({
        Arg(XLL_HANDLEX, "handle", "is a handle returned by XLL.BASE")
    })
    .FunctionHelp("Return the value stored in base.")
);
LPOPERX WINAPI xll_base_get(HANDLEX _h)
{
#pragma XLLEXPORT
    static OPER result; // must be static since we are returning its address
    xll::handle<base> h(_h);

    if (h) {
        result = h->get();
    }
    else {
        result = ErrNA;
    }

    return &result;
}

For a production quality version of this example see handle.cpp. That file also has examples illustrating how single inheritance can be used in Excel using dynamic_cast. The handle class has a member template<class U> U* as() to do this for you and ensure U is derived from T.

When a spreadsheet containing handles is reopened you must 'refresh' the handles using Ctrl-Alt-F9. The old handles that were previously saved are stale.

Excel4 Macro Function

Add-ins can call any Excel function using xll::Excel and the appropriate function number. The function numbers are defined in XLCALL.H and correspond to Excel built-in functions or macros (command equivalents). To determine the approriate arguments for a function number see the Excel 4 Macro documentaton

Function numbers starting with xlf are f‌unctions and can be called from add-in functions. Function numbers starting with xlc are ma‌c‌ros and can only be called from add-in macros. Functions are not allowed to have side-effects, they must be purely functional. Macros can only have side-effects, they take no arguments and return TRUE if they run successfully and FALSE if not.

Some function numbers are special to the C API. For example, xlUDF can be used to call User-Defined Functions.

Debugging

The program that the debugger starts and the arguments to use are specified in project properties. Right click on a project and select Properties (Alt-Enter) at the bottom of the popup menu. Navigate to Debugging in Configuration Properties.

debug properties

The Command $(registry:HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\App Paths\excel.exe) looks in the registry for the full path of the Excel executable. The Command Arguments "$(TargetPath)" /p "$(ProjectDir)" are passed to Excel when the debugger starts. The variable $(TargetPath) is the full path to the xll that was built and is opened by Excel. The /p flag to Excel sets the default directory so Ctrl-O opens to the project directory.

Documentation

You can automatically generate documentation for your add-in by calling Documentation("NAME", "Description...) anywhere in your add-in. This creates an index.html file with links to all the functions and macros that have a .Documentation() argument. The files are created in the directory the add-in is run from and only works when compiled in debug mode. There is an experimental macro called DOC that will create a workbook with one sheet per add-in function and provide an index page.

Math is rendered using KaTeX. Inline formulas use \(...\) and displayed formulas use \[...]\. I like to use raw string literals to keep the documentation next to the code. For example

AddIn xai_foo(
    Function(...)
    .Arguments({...})
    ...
    .Documentation(R"xyzyx(
This is documentation with inline math like this: \(e^{\pi i} + 1 = 0\).
It also has a displayed equation
\[
    \exp(x) = \sum_{n = 0}^\infty \frac{x^n}{n!}.
\]
<p>
You can use any valid HTML in your documentation.
</p>
<aside><p>Any unique string of characters can be used with a raw string literal instead of `xyzyx`.</p></aside>
)xyzyx")) // ← last parentheses closes `.Documentation()`
);

Remarks

Things you should be aware of but don't need to know to use the xll library.

Bits, bits everywhere

At the end of the day computer programs come down to bits. Everything is a collection of bits, it is just a matter of how to interpret them. The Windows 10 operating system comes in two flavors: 32-bit and 64-bit. The abstract data model, how the bits are interpreted, are nearly the same: all basic data types are 32-bit, only the pointer types differ. You may have difficulty locating a 32-bit version of Window 10, which is good. All new developent is happening in the 64-bit world so you should prefer that.

Excel also comes in 32-bit and 64-bit flavors. You can run either (but not both) on 64-bit Windows. You can only run 32-bit Excel on 32-bit Windows. Visual Studio can build both 32-bit and 64-bit add-ins. You should build for the flavor of Excel you have installed. Install 64-bit Excel if you have that option.

Microsoft Office has several flavors. The latest version is Microsoft Office 2019 and only runs on Windows 10 or Mac OS X Sierra and later. There is a free web based version that runs in all modern browsers.

There is also Office 365, now called Microsoft 365, which should not be confused with Office 2019. It has features not available in Office 2019, in particular dynamic arrays. No need for Ctrl-Shift-Enter and trying to guess the size of ranges returned by functions. The entire range gets plopped into the spreadsheet and you will see a #SPILL! error if that would clobber existing cells.

Xll add-ins are not supported on Macs or the web based version. You can write custom functions using JavaScript (or, even better, TypeScript) for Office on Windows, Mac, and online versions.

Memory Leaks

When you build in debug mode the add-in library will check for memory leaks after you run the add-in. It uses the functions in <crtdbg.h> to turn on memory debugging. After you run your add-in the Output window will have information about the location of memory leaks. It will also have a lot of other things such as all the dlls that were loaded and unloaded, and a list of exceptions that were thrown. You can ignore those. Use Ctrl-F and search for the strings matching leak to find the output from the memory debugging routines. Those will tell you the values to set for _crtBreakAlloc so the next time you run the add-in the debugger will stop where the bad allocation was detected. Use the call stack to zero in on the offending allocation.

The cool kids use AddressSanitizer these days.

Handles

Handles are handled by the xll::handle<T> class parameterized by the handle type T. They are similar to std::unique_ptr<T>.

A handle is a double used to represent a pointer. Excel doesn't know about pointers so we use a double to hold a pointer to a C++ object. Use HANDLEX instead of double to make clear when a double is representing a pointer.

If you are worried that the 64-bits in a pointer might represent a double that is a NaN or denormalized value, you should be. Neither of those survive a round trip to Excel and back. Returning a NaN to Excel results in the error type #NUM!.

On 64-bit Windows the first 16-bits of a pointer are zero so all we need are the remaining 48-bits. Doubles can exactly represent integers up to 253 so we have plenty of room to spare. Converting between HANDLEX and void* is just a cast, so no lookup is involved.

The handle<T>(T *) constructor takes a pointer returned by new and behaves like a std::unique_ptr. The member function handle<T>::ptr() returns the T* pointer. The member function handle<T>::operator->() provides syntactic sugar for this. Use handle<T>::get() to return the corresponding HANDLEX to Excel.

When the HANDLEX is passed back from Excel as an add-in function argument the handle<T>(HANDLEX) constructor converts the HANDLEX back to a T* pointer. By default it checks to see if this has been constructed by a call to handle<T>(T*), but this can be turned off. If the check fails a nullptr is returned.

If any arguments to a function call a function that creates a handle, then the handle gets deleted after the outer function returns. To avoid repeated calls to new and delete put handles in their own cell and pass a reference to that as an argument.

If you don't like seeing raw pointer values as doubles then roll your own encoder/decoder to display whatever tickles your fancy.

Uncalced

Functions that are declared .Uncalced() have a limited ability to call command equivalents/macros. You must specify this when writing an add-in that creates a handle. In general, add-in functions cannot have side-effects. They can only call Excel with function numbers starting with xlf (f‌​unctions) and are forbidden to call Excel with function numbers starting with xlc (c‌​ommand equivalents, also known as ma‌c‌ros).

Volatile

Functions that are declared .Volatile() are called on every recalculation. For example, the built-in RAND() function is volatile. It is always recalculated even though it has no dependencies.

Recalculation

In Excel F9 (xlcCalculateNow) recalculates all 'dirty' cells. Use Shift-F9 (xlcCalculateDocument) to recalculate only the dirty cells in the active worksheet. There is no command equivalent in the C API for Ctrl-Alt-F9. Use this when you want Excel to recalculate everything, really, no kidding, just do it.

You should be aware of the 'replace equal by equal' idiom. The key sequence is Ctrl-H, =, <tab>, =, a. This replaces all occurences of the = character in every formula by = and causes each formula to be recalculated without changing the formula.