Exporting C++ to Excel via the XLL SDK

Setup

Why XLLs?

A production quant desk does not price options in a Python notebook. It prices them in Excel workbooks connected to C++ pricing libraries. The XLL (Excel Link Library) is the mechanism: a 64-bit Win32 DLL with a specific entry point that Excel loads at startup, registers C++ functions as native worksheet functions, and exposes them in the function wizard like built-in Excel functions.

The alternatives — VBA UDFs, COM add-ins, RTD servers — are either too slow, too complex to deploy, or limited in the types they can handle. XLLs are the industry standard for high-performance Excel integration. Banks use them to expose Black-Scholes pricers, yield curve bootstrappers, and risk engines directly to structurers and traders working in Excel.

What this module builds:

1. A minimal XLL from the Excel SDK that exports a scalar function (xMultiply2Numbers) and a range-in/range-out function (xVectorFunction).
2. The xlOper.h utility layer that converts between Excel's XLOPER12 type and C++ std::vector<double> and std::string.
3. The registration mechanism in xlAutoOpen.

Prerequisites:

• 64-bit Microsoft Excel (Office 365 or Excel 2016+).
• Visual Studio 2019+ with MSVC (C++17).
• The Excel SDK (xlcall.h, xlcall.cpp, framework.h, framework.c) — distributed as part of the Microsoft Excel Developer Kit (XDK), available from the Microsoft website.

Platform note: XLLs are Windows-only (Win32 DLL). The code in this module is intentionally not cross-platform. On a quant desk, the production pricing machine runs Windows. This is not a limitation to work around.

Theory

1. What Is an XLOPER12?

The central type in the Excel SDK is XLOPER12 (defined in xlcall.h). It is a tagged union — a C struct that can hold a number, a string, a range (multi-cell array), an error code, or a boolean:

typedef struct xloper12 {
    union {
        double num;              // xltypeNum
        XLWCHAR* str;            // xltypeStr (counted string: str[0] = length)
        BOOL xbool;              // xltypeBool
        int err;                 // xltypeErr
        short int w;             // xltypeInt
        struct { ... } sref;     // xltypeSRef (reference)
        struct {                 // xltypeMulti (2D array)
            LPXLOPER12 lparray;
            RW rows;
            COL columns;
        } array;
        // ... other variants
    } val;
    DWORD xltype;                // tag: xltypeNum, xltypeStr, xltypeMulti, etc.
} XLOPER12;

When Excel calls your function, it passes arguments as LPXLOPER12 (pointer to XLOPER12). Your function inspects the xltype tag to determine what kind of data arrived, then reads the appropriate val field. Your return value is likewise an LPXLOPER12.

Type string codes in xlfRegister tell Excel how to marshal arguments:

The first character in the type string is the return type; subsequent characters are argument types. BBB means: returns double, takes two double arguments.

2. Memory Management in the XLL

XLL functions run on Excel's call stack. They must not return a pointer to a local variable (dangling) or new-allocated memory that Excel can't free. The SDK provides a temporary memory pool via GetTempMemory():

LPXLOPER12 result = (LPXLOPER12)GetTempMemory(sizeof(XLOPER12));

Memory allocated through GetTempMemory is valid for the duration of the Excel formula evaluation and is automatically freed by FreeAllTempMemory() at the end of the call. This is the correct pattern for returning strings and arrays from XLL functions.

For arrays returned as xltypeMulti, each element of the lparray must also be individually allocated (or pointed into the temp pool). The helper functions in xlOper.h handle this for you.

3. xlAutoOpen: The Registration Entry Point

When Excel loads the XLL, it calls xlAutoOpen(). This is where you register every function you want to expose as a worksheet function:

Excel12f(xlfRegister, 0, 11, &xDLL,
    TempStr12("xMultiply2Numbers"),  // function name in DLL
    TempStr12("BBB"),                // type string: returns B, takes B, B
    TempStr12("xMultiply2Numbers"),  // name shown in Excel
    TempStr12("x, y"),               // argument names
    TempStr12("1"),                  // function type: 1 = worksheet function
    TempStr12("myOwnCppFunctions"),  // category in function wizard
    TempStr12(""),                   // shortcut text (optional)
    TempStr12(""),                   // help topic (optional)
    TempStr12("Multiplies 2 numbers"), // description
    TempStr12(""));                  // argument 1 description

The 11 is the number of arguments after the xlfRegister constant. Getting this count wrong is a silent bug — Excel will load the function but call it with wrong arguments. Always count them.

Implementation

framework.h / framework.c (SDK boilerplate)

These files come from the Microsoft XDK and must not be modified. They provide:

• GetTempMemory(size) — allocates from the per-call temp pool
• FreeAllTempMemory() — frees the temp pool (called by Excel internally)
• TempStr12(wchar_t*) — allocates a counted Unicode string in the temp pool
• Excel12f(fn, ...) — variadic wrapper around the Excel12 API call

xlOper.h — C++ / Excel Type Conversions

This header provides helper functions to convert between the C++ world and the XLOPER12 world:

#pragma once
#include "xlcall.h"
#include "framework.h"
#include <string>
#include <vector>
#include <limits>
using namespace std;

// Additional TempStr12 overload accepting std::string
LPXLOPER12 TempStr12(const string str);

// ── Getters (Excel → C++) ───────────────────────────────────────────────────