xll

https://github.com/xlladdins/xll
Admin
The xll library simplifies calling C, C++, or Fortran code from Excel, offering high numerical...
Tokens:22,988
Snippets:76
Trust Score:5.5
License:MIT
Update:1 year ago
Show doc for...
Tokens:
Raw
### Basic xll Add-in Structure

Source: https://github.com/xlladdins/xll/blob/master/docs/handles.txt

Demonstrates the fundamental structure of an xll add-in in C++, including necessary headers and namespace usage. This is the starting point for creating any Excel add-in with the xll library.

```cpp
#include "xll.h"

using namespace xll;
```

--------------------------------

### Calling Member Functions with Arguments

Source: https://github.com/xlladdins/xll/blob/master/docs/handles.txt

Illustrates how to call C++ member functions that accept arguments from Excel using the xll library. The example shows calling a `set` method and returning the handle to Excel.

```cpp
// Example of calling a member function with arguments
// Assume 'base::set' takes an OPER and returns void
// Excel function signature might look like: \XLL.BASE.SET(handle, new_value)
// Implementation:
// extern "C" __declspec(dllexport) int WINAPI xll_base_set(int i, OPER* pop) {
//     xll::handle<base> h(pop[0]);
//     h->set(*pop[1]); // Call the C++ member function
//     return 1; // Return handle count
// }
```

--------------------------------

### Detecting Excel Platform (Batch)

Source: https://github.com/xlladdins/xll/blob/master/NOTES.md

A batch script snippet to query the Windows Registry and determine the installed platform (x86 or x64) of Microsoft Office.

```batch
for /F "tokens=3" %A in ('reg query HKLM\SOFTWARE\Microsoft\Office\ClickToRun\Configuration /v Platform') do (echo %A)
```

--------------------------------

### Handle Creation and Verification

Source: https://github.com/xlladdins/xll/blob/master/docs/handles.txt

Focuses on the process of creating a base object with an encoded handle and verifying the decoding process. It highlights the importance of the correct prefix and uses the Excel `xlfLeft` function (aliased as `XLF.LEFT` in Excel) to demonstrate string manipulation related to handle verification. The example includes checking the output of `base.get` to confirm successful decoding.

```cpp
// Assuming handle::codec has been called with the correct prefix.

// Example: Creating a base object and getting its encoded handle
// void* base_ptr = /* ... create base object ... */;
// const char* encoded_handle = handle::encode(base_ptr);

// Example: Using Excel's LEFT function to check the prefix
// XLOPER12 xlEncodedHandleStr;
// xlEncodedHandleStr.xltype = xltypeStr;
// xlEncodedHandleStr.val.str = (LPWSTR)L"MyHandle_1234567890ABCDEF_End"; // Example encoded string
//
// XLOPER12 xlPrefixCheck = Excel(xlfLeft, 2, &xlEncodedHandleStr, 9); // Get first 9 chars
// // Check if xlPrefixCheck.val.str equals L"MyHandle_"

// Example: Calling base.get to verify decoding
// XLOPER12 xlBaseValue = Excel(xlUDF, 0, L"base.get", 1, &xlEncodedHandleStr);
```

--------------------------------

### xll::handle Class Functionality

Source: https://github.com/xlladdins/xll/blob/master/README.md

Explains the core functionalities of the xll::handle class for managing C++ objects in Excel. This includes creating handles with `new`, accessing the underlying pointer with `ptr()`, and obtaining the Excel-compatible handle with `get()`.

```APIDOC
xll::handle<T>(T*)
  - Creates a handle to a C++ object of type T.
  - Usage: `xll::handle<T> h(new T(...));`

xll::handle<T>::ptr()
  - Returns a pointer to the underlying C++ object.

xll::handle<T>::get()
  - Returns the Excel-compatible handle (type HANDLEX).

xll::handle<T>(HANDLEX)
  - Converts an Excel handle back to a pointer to the C++ object.
  - Returns nullptr if the handle was not created by `handle<T>(T*)`.

xll::handle<T>::operator->()
  - Allows direct member access to the underlying C++ object using arrow notation (e.g., `h->member(...)`).

xll::handle<T>::as<U>()
  - Safely casts the handle to a pointer of a derived type U, ensuring U is derived from T.
```

--------------------------------

### Accessing Object Data with xll::handle

Source: https://github.com/xlladdins/xll/blob/master/docs/handles.txt

Shows how to retrieve the C++ object managed by an xll::handle. The `get()` member function returns a handle to the managed object, and the arrow operator (`->`) provides direct access to the object's members.

```cpp
// Assuming 'h' is an xll::handle to a base object
base* obj_ptr = h.get(); // Get a pointer to the base object
obj_ptr->some_member_function(); // Call a member function
```

--------------------------------

### Troubleshooting .xll Add-in Loading Issues

Source: https://github.com/xlladdins/xll/blob/master/NOTES.md

Provides guidance on diagnosing and resolving problems when Excel fails to load .xll add-ins. It covers common causes such as incorrect bitness, security software interference, missing VBA components, and Group Policy restrictions. Includes steps to test native add-ins and use tools like Process Monitor.

```text
Some comments and first steps:
•	When you try to load the wrong bitness add-in, Excel opens the binary file as text, and you get exactly the content you show.
•	When you try to load the wrong bitness add-in, the code in the add-in never executes.
•	It sounds like the problem machines are somehow configured to not load .xll add-ins, hence are falling back to the open-as-text behaviour.
•	In this case Excel-DNA is probably not involved either, and any .xll add-in will fail. You can try these pure native add-ins from the Excel2013 XLL SDK: https://www.dropbox.com/sh/f7v40h7xgf2uj19/AADXCjIlsgQgKI_vpYjRxfFha?dl=0
(If you're not comfortable running the binaries, I can help you download the SDK and rebuild it yourself - there is a bit of fiddling needed.)
•	Once you've established that no .xll add-ins work on these machines, at least Excel-DNA and .NET are out of the way too.
My next suggestions (in decreasing confidence) would be to look for other configuration options that might be clocking add-ins.
•	Temporarily disable the Trend Micro to be quite sure it is not blocking the .xll files being loaded.
•	Check that VBA is installed (just press F11 and confirm, that the VBA IDE is displayed). VBA is an optional component at install time, and is required to be present for any add-ins (including .xll add-ins) to be supported by Excel.
•	Check whether .xla add-ins can be loaded. Maybe there is some setting blocking all add-ins.
•	Try to figure out whether Group Policy settings have been applied. This is not always easy, as Office virtualizes the registry so it can be super confusing. But for example in the past these was a Group Policy options that blocks the COM object model from working (something about Automation being disabled).
•	You can also do a Process Monitor trace to see what registry entries are read by the process as it tries to load the add-in. This is a bit tedious but has pointed me in the right direction once or twice.
Finally my suggestions deteriorate into the usual:
•	Uninstall and reinstall Office.
```

--------------------------------

### Generate Add-in Documentation

Source: https://github.com/xlladdins/xll/blob/master/README.md

This C++ code snippet demonstrates how to automatically generate documentation for an Excel add-in. The `Documentation()` function creates an `index.html` file with links to all functions and macros that have a `.Documentation()` argument. This feature works when compiled in debug mode and creates files in the add-in's run directory. It supports inline and displayed math using KaTeX.

```C++
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()`
);
```

--------------------------------

### Excel Add-in to Get Value from Derived Object

Source: https://github.com/xlladdins/xll/blob/master/talk.html

An Excel add-in function `xll_derived_get2` that retrieves a value from a `derived<OPER>` object using its handle. It performs a dynamic cast to access the `get2()` method.

```C++
#include "derived.h"
#include "xll/xll.h"

using namespace xll;

AddIn xai_derived_get2(
    Function(XLL_LPOPER, "xll_derived_get2, "XLL.DERIVED.GET2")
    .Arguments({
        Arg(XLL_HANDLEX, "handle", "is a handle to a derived<OPER> object.")
    })
);
LPOPER WINAPI xll_derived_get2(HANDLEX h)
{
#pragma XLLEXPORT
    handle<base<OPER>> h_(h);
    if (!h_) {
        return (LPOPER)ErrNA; // #N/A
    }

    auto h2 = h_.as<derived<OPER>>();

    return h2 ? h2->get2() : (LPOPER)ErrValue;
}

```

--------------------------------

### Using xll::FPX for Array Manipulation

Source: https://github.com/xlladdins/xll/blob/master/README.md

Provides guidance on creating, accessing, and resizing `xll::FPX` arrays. It explains how to use the `FPX::get()` member function to obtain a pointer to the underlying struct for returning arrays to Excel and how to manage memory for these pointers.

```cpp
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 it points 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.
```

--------------------------------

### Debugging Excel Add-in Projects

Source: https://github.com/xlladdins/xll/blob/master/README.md

Provides instructions on configuring Visual Studio project properties for debugging Excel add-ins. This includes setting the debugger's command to Excel's executable and specifying command arguments to load the add-in and set the working directory.

```APIDOC
Project Properties -> Debugging:
  Command:
    $(registry:HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\App Paths\excel.exe)
    - Specifies the full path to the Excel executable by looking it up in the Windows registry.
  Command Arguments:
    "$(TargetPath)" /p "$(ProjectDir)"
    - $(TargetPath): The full path to the generated xll file.
    - /p: Sets the default directory for Excel to the project's directory, simplifying file opening (Ctrl-O).
```

--------------------------------

### XLL Add-in Function Declarations

Source: https://github.com/xlladdins/xll/blob/master/README.md

Explains special function declarations for XLL add-ins: `.Uncalced()` and `.Volatile()`. `.Uncalced()` restricts calls to Excel functions starting with 'xlf', while `.Volatile()` ensures the function is called on every recalculation.

```APIDOC
Function Declarations:

.Uncalced():
  - Functions declared with .Uncalced() have limited ability to call command equivalents/macros.
  - They can only call Excel functions starting with 'xlf' (functions).
  - They are forbidden to call Excel functions starting with 'xlc' (command equivalents/macros).
  - Must be specified when writing an add-in that creates a handle.

.Volatile():
  - Functions declared with .Volatile() are called on every recalculation, regardless of dependencies.
  - Example: The built-in RAND() function is volatile.
  - It is always recalculated even though it has no dependencies.
```

--------------------------------

### xlladdins Library API Documentation

Source: https://github.com/xlladdins/xll/blob/master/talk.md

Documentation for core xlladdins concepts including Function, Arg, Macro, OPER, and handle. Explains how to define functions, arguments, and macros for Excel integration, and details the OPER data type for cell representation and the xll::handle for C++ object management.

```APIDOC
AddIn:
  Represents a function or macro to be registered with Excel.

Function(return_type, function_name, excel_function_name):
  Defines a function exposed to Excel.
  - return_type: The data type returned by the function (e.g., XLL_DOUBLE).
  - function_name: The C++ function name.
  - excel_function_name: The name used in Excel.
  .Arguments(args):
    Specifies the arguments for the Excel function.
    - args: An initializer list of Arg objects.
  .FunctionHelp(help_text):
    Sets the help text for the function.
  .Category(category_name):
    Assigns the function to an Excel function wizard category.
  .HelpTopic(url):
    Provides a URL for detailed help.

Arg(type, name, description):
  Defines a single argument for an Excel function.
  - type: The data type of the argument (e.g., XLL_DOUBLE).
  - name: The argument name used in documentation.
  - description: A description of the argument.

Macro(cpp_name, excel_name):
  Defines a macro to be called from Excel.
  - cpp_name: The C++ function name that implements the macro.
  - excel_name: The name used to call the macro from Excel.

OPER:
  A class representing an Excel cell or range. It's a variant type that can hold numbers, strings, booleans, errors, or multi-dimensional arrays.
  - xltype: Member indicating the type of data stored (e.g., xltypeNum, xltypeStr, xltypeMulti).
  - Can be constructed with values like OPER o(1.23) or assigned values like o = "foo".

xll::handle<T>:
  A smart pointer-like class for managing C++ objects within Excel.
  - Constructor: xll::handle<T> h(new T(args...)) stores a pointer to a new object.
  - Behavior: Similar to std::unique_ptr, it manages object lifetime and calls delete when out of scope.
  - ptr(): Returns the raw pointer to the managed object.
  - operator->(): Allows access to members of the managed object.
  - get(): Returns a HANDLEX value usable in Excel.

HANDLEX:
  A 64-bit IEEE double used to represent pointers to C++ objects in Excel.
  - Conversion: Can be converted to and from pointers efficiently.
  - Safety: The constructor xll::handle<T>(HANDLEX) converts a HANDLEX back to a pointer, returning nullptr if the HANDLEX is invalid.
```

--------------------------------

### XLL Function to Get Value from Derived Object

Source: https://github.com/xlladdins/xll/blob/master/talk.md

An XLL function 'XLL.DERIVED.GET2' that retrieves the second value from a 'derived<OPER>' object. It uses `handle::as<U>()` to safely cast the base handle to a derived handle and then calls the 'get2' method.

```C++
AddIn xai_derived_get2(
	Function(XLL_LPOPER, "xll_derived_get2, "XLL.DERIVED.GET2")
	.Arguments({
		Arg(XLL_HANDLEX, "handle", "is a handle to a derived<OPER> object.")
	})
);
LPOPER WINAPI xll_derived_get2(HANDLEX h)
{
#pragma XLLEXPORT
	handle<base<OPER>> h_(h);
	if (!h_) {
		return (LPOPER)ErrNA; // #N/A
	}

	auto h2 = h_.as<derived<OPER>>();
	
	return h2 ? h2->get2() : (LPOPER)ErrValue;
}
```

--------------------------------

### xll::handle for Object Management

Source: https://github.com/xlladdins/xll/blob/master/talk.html

The `xll::handle<T>` class manages pointers to objects of type `T`, similar to `std::unique_ptr`. It ensures automatic memory deallocation when the handle goes out of scope. The `get()` method returns a `HANDLEX` for use within Excel.

```APIDOC
`xll::handle`
=============

A `xll::handle<T>` has a pointer to an object of type `T` and behaves like `std::unique_ptr<T>`. The constructor `xll::handle<T> h(new T(args...))` stores the pointer returned by `new`. It refers to exactly one object and calls `delete` on the object when it goes out of scope. Its `ptr()` and arrow `operator->()` member functions return the pointer to the object. Use the `get()` member function to return a `HANDLEX` value that can be used in Excel.
```

--------------------------------

### Excel Function Registration with AddIn

Source: https://github.com/xlladdins/xll/blob/master/docs/handles.txt

Shows how to use the `AddIn` class to register C++ functions with Excel. This includes specifying function names, arguments, and return types, enabling Excel to call the C++ code.

```cpp
// Example of registering a function
// static AddIn xai_my_function(
//     "MY_FUNCTION", // Excel function name
//     "handle",      // Argument type (handle)
//     "string",      // Return type (string)
//     "My C++ Function", // Description
//     "Category"
// );
// 
// int WINAPI xll_my_function(int i) {
//     // Function implementation
//     return 1;
// }
```

--------------------------------

### XLL Function to Get Value from Base Object

Source: https://github.com/xlladdins/xll/blob/master/talk.md

An XLL function 'XLL.BASE.GET' that retrieves the value from a 'base<OPER>' object using its handle. It takes a handle, casts it to the correct type, and returns the contained value. Handles invalid or incorrect handles by returning an error.

```C++
AddIn xai_base_get(
	Function(XLL_LPOPER, "xll_base_get", "XLL.BASE.GET")
	.Arguments({
		Arg(XLL_HANDLEX, "handle", "is a handle to a base<OPER> object.")
	})
);
LPOPER WINAPI xll_base_get(HANDLEX h)
{
#pragma XLLEXPORT
	handle<base<OPER>> h_(h);
	
	return h_ ? &h_->get() : (LPOPER)ErrValue;
}
```

--------------------------------

### Workbook and Sheet Manipulation

Source: https://github.com/xlladdins/xll/blob/master/test/NOTES.md

Demonstrates creating a new workbook, inserting a new sheet, and selecting a cell range. It also shows how to assign values and move the selection.

```cpp
Workbook wb;
wb.New(); // create a new workbook
Sheet s = wb.Insert("Sheet"); // insert and select sheet
Selection sel = s.Select("A1"); // select cell/range in sheet
sel = 1.23;
sel.Move(Down);
sel = "foo";
sel.Move(Right);
sel.Type("A comment.", 0.1s); // type 1 char every 1/10 seconds
```

--------------------------------

### Excel Function Calls via xll Library

Source: https://github.com/xlladdins/xll/blob/master/README.md

Demonstrates a simplified way to call Excel functions using the xll add-in library, which handles the underlying Excel4/Excel12 calls and memory management. The `Excel` function is a wrapper.

```c++
// Example: Calling the Workspace function to get the Excel version
// OPER version = Excel(xlfWorkspace, OPER(2));
```

--------------------------------

### C++ Handle Creation and Usage in Excel

Source: https://github.com/xlladdins/xll/blob/master/README.md

Demonstrates how to create and manage C++ objects within Excel using the xll::handle class. This includes creating a handle to a C++ object, returning it to Excel, and accessing the object's members from Excel.

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

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();
}
```

```C++
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;
}
```

--------------------------------

### OPER Data Types and Predefined Values

Source: https://github.com/xlladdins/xll/blob/master/README.md

Explains the OPER data type, its default constructor, and predefined values like Nil, Nil4, Nil12, ErrNull, ErrNull4, ErrNull12, Missing, Missing4, and Missing12. It also clarifies the distinction between OPER Nil and the '#NULL!' error.

```cpp
The default constructer of `OPER` creates an object of type `xltypeNil`.
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`.
```

--------------------------------

### xlladdins Debugging and Memory Management

Source: https://github.com/xlladdins/xll/blob/master/README.md

This section details debugging practices for xll add-ins, focusing on memory leak detection using `<crtdbg.h>` and AddressSanitizer. It explains how to interpret memory leak information in the Output window and use `_crtBreakAlloc` to pinpoint allocation errors. It also touches upon the importance of choosing the correct build for Excel's 32-bit or 64-bit versions.

```APIDOC
xlladdins Debugging and Memory Management:

Memory Leak Detection:
  - Uses functions in `<crtdbg.h>` when compiled in debug mode.
  - Checks for memory leaks after add-in execution.
  - Output window provides information on leak locations, loaded/unloaded DLLs, and exceptions.
  - Use Ctrl-F to search for 'leak' to find relevant output.
  - Set `_crtBreakAlloc` to stop the debugger at the point of a bad allocation.
  - Utilize the call stack to identify the offending allocation.

Tools:
  - AddressSanitizer (ASan) for Windows with MSVC is a modern alternative.

Build Considerations:
  - Prefer 64-bit builds for modern development.
  - Build add-ins for the flavor of Excel installed (32-bit or 64-bit).
  - Install 64-bit Excel if available.

Cross-Platform Notes:
  - xll add-ins are not supported on Macs or web-based Excel versions.
  - Custom functions for Office on Windows, Mac, and online can be written using JavaScript or TypeScript.
```

--------------------------------

### Handle Operations with Derived Objects

Source: https://github.com/xlladdins/xll/blob/master/docs/handles.txt

Illustrates advanced handle usage involving derived objects. This includes adding arguments to calls like `XLL.DERIVED`, ensuring `xll.base.get` functions correctly with derived objects, and verifying the functionality of `xll.derived.get2`. This section emphasizes the interoperability of base and derived handle types.

```cpp
// Assuming XLL.DERIVED is a registered Excel function that takes arguments
// and returns a derived handle.

// Example: Calling XLL.DERIVED with arguments and getting its value
// XLOPER12 xlArg1, xlArg2;
// xlArg1.xltype = xltypeNum;
// xlArg1.val.num = 10.0;
// xlArg2.xltype = xltypeNum;
// xlArg2.val.num = 20.0;
//
// XLOPER12 xlDerivedHandle = Excel(xlUDF, 0, L"XLL.DERIVED", 2, &xlArg1, &xlArg2);
//
// // Assuming xll.base.get and xll.derived.get2 are C++ functions exposed to Excel
// // that take a handle and return its value.
// XLOPER12 xlBaseValue = Excel(xlUDF, 0, L"xll.base.get", 1, &xlDerivedHandle);
// XLOPER12 xlDerivedValue = Excel(xlUDF, 0, L"xll.derived.get2", 1, &xlDerivedHandle);
```

--------------------------------

### Core XLL Addin Headers

Source: https://github.com/xlladdins/xll/blob/master/inc.txt

These headers provide fundamental building blocks for creating Excel add-ins. They cover essential functionalities like addin registration, argument handling, Excel data types, and platform-specific adaptations.

```c++
#include <xll/addin.h>
#include <xll/args.h>
#include <xll/auto.h>
#include <xll/codec.h>
#include <xll/concepts.h>
#include <xll/defines.h>
#include <xll/ensure.h>
#include <xll/error.h>
#include <xll/excel.h>
#include <xll/exports.h>
#include <xll/fp.h>
#include <xll/handle.h>
#include <xll/on.h>
#include <xll/oper.h>
#include <xll/platform.h>
#include <xll/register.h>
#include <xll/registry.h>
#include <xll/splitpath.h>
#include <xll/sref.h>
#include <xll/traits.h>
#include <xll/utf8.h>
#include <xll/view.h>
#include <xll/win.h>
#include <xll/xll.h>
#include <xll/xllio.h>
#include <xll/xloper.h>
```

--------------------------------

### Excel Recalculation Methods

Source: https://github.com/xlladdins/xll/blob/master/README.md

Details various methods for triggering recalculation in Excel. This includes keyboard shortcuts like F9 and Shift-F9, and the 'replace equal by equal' idiom for forcing formula recalculation.

```APIDOC
Excel Recalculation:

- F9 (xlcCalculateNow): Recalculates all 'dirty' cells in the workbook.

- Shift-F9 (xlcCalculateDocument): Recalculates only the dirty cells in the active worksheet.

- Ctrl-Alt-F9:
  - No direct command equivalent in the C API.
  - Forces Excel to recalculate everything.

- 'Replace equal by equal' idiom:
  - Key sequence: Ctrl-H, =, <Tab>, =, a
  - Replaces all occurrences of '=' with '=' in formulas.
  - Causes each formula to be recalculated without changing the formula itself.
```

--------------------------------

### Register Excel Functions and Macros with AddIn Class

Source: https://github.com/xlladdins/xll/blob/master/README.md

The `xll::AddIn` class is central to registering functions and macros within Excel add-ins. Instances of this class should be defined at the global scope to ensure they are constructed when the add-in loads, thereby storing necessary information for `xlAutoOpen`.

```C++
class AddIn {
public:
    AddIn(const std::string& category, const std::string& name);
    // ... other methods for registering functions and macros
};

// Example of global scope definition:
xll::AddIn myAddIn("MyCategory", "MyAddInName");
```

--------------------------------

### XLL Handle Class

Source: https://github.com/xlladdins/xll/blob/master/README.md

Demonstrates the usage of the `xll::handle<T>` class for managing pointers within Excel add-ins. It explains how handles are represented as doubles, their conversion to and from pointers, and their behavior similar to `std::unique_ptr`.

```cpp
class handle<T> {
public:
  // Constructor taking a pointer
  handle(T* ptr);

  // Returns the raw pointer
  T* ptr() const;

  // Syntactic sugar for ptr()
  T* operator->() const;

  // Returns the handle as a double (HANDLEX)
  HANDLEX get() const;

  // Constructor taking a HANDLEX
  handle(HANDLEX h);
};

// Example usage:
// Create a handle for an object
xll::handle<MyObject> obj_handle(new MyObject());

// Access the object through the handle
MyObject* obj = obj_handle.ptr();
obj->someMethod();

// Get the HANDLEX to pass to Excel
HANDLEX excel_handle = obj_handle.get();

// Convert HANDLEX back to a handle
xll::handle<MyObject> retrieved_handle(excel_handle);
MyObject* retrieved_obj = retrieved_handle.ptr();
```

--------------------------------

### Retrieving Excel Version Information

Source: https://github.com/xlladdins/xll/blob/master/README.md

Provides methods to determine the Excel version at runtime. XLCallVer returns a specific hexadecimal code, while GET.WORKSPACE(2) returns a string representation of the version.

```c++
// Using XLCallVer
// XLCallVer() returns 0x500 for version 4, 0x0c00 for version 12.

// Using GET.WORKSPACE(2) via Excel4
// XLOPER version;
// OPER two = { .val = { .int = 2 }, .xltype = xltypeInt };
// Excel4(xlfGetWorkspace, &version, 1, &two);
// Excel4(xlFree, 0, 1, &version); // Free memory

// Using GET.WORKSPACE(2) via xll library
// OPER version = Excel(xlfWorkspace, OPER(2)); // Destructor frees memory
```

--------------------------------

### XLL Add-in Function Registration API

Source: https://github.com/xlladdins/xll/blob/master/README.md

Defines the structure for registering custom functions within an Excel add-in. It specifies the return type, C++ function name, Excel-visible name, and a list of arguments, each with its type, name, and description. Optional parameters include function help text, category, and a help topic URL.

```APIDOC
AddIn(Function(return_type, cpp_function_name, excel_function_name))
    .Arguments({ Arg(arg_type, arg_name, arg_description) })
    .FunctionHelp(help_text)
    .Category(category_name)
    .HelpTopic(help_topic_url);

// Parameters:
// return_type: Data type returned by the function (e.g., XLL_DOUBLE, XLL_INT, XLL_STRING).
// cpp_function_name: The name of the C++ function to be called.
// excel_function_name: The name that will be visible and callable in Excel.
// Arguments: An array of Arg objects, each defining an argument for the function.
//   Arg(arg_type, arg_name, arg_description): Defines a single argument.
//     arg_type: Data type of the argument (e.g., XLL_DOUBLE, XLL_INT).
//     arg_name: Name of the argument as it appears in Excel.
//     arg_description: Short description of the argument.
// FunctionHelp: Text displayed in the Excel Function Wizard.
// Category: The category under which the function will be listed in Excel.
// HelpTopic: URL to a help document for the function.
```

--------------------------------

### xlladdins Library API Documentation

Source: https://github.com/xlladdins/xll/blob/master/talk.html

This section details the core components and functionalities provided by the xlladdins library for integrating C++ with Excel. It covers function definition, argument handling, help integration, and implementation guidelines.

```APIDOC
xlladdins Library Overview:

Purpose: Facilitates the integration of C++ code into Microsoft Excel.
Key Features:
- Call C++/C/Fortran functions directly from Excel.
- Enhanced debugging capabilities ('debugger on steroids').
- Support for UTF-8 strings.
- Embedding of objects and use of single inheritance.
- Seamless integration with third-party libraries.
- Integration with Excel's help documentation system.

Core Components:

1.  AddIn Definition (`AddIn` class):
    - Used to register functions with Excel.
    - Requires a `Function` object to specify function details.
    - Example:
      ```cpp
      AddIn xai_function_name(
          Function(return_type, "c_function_name", "ExcelFunctionName")
          .Arguments({ Arg(arg_type, "arg_name", "Argument help text") })
          .FunctionHelp("Help text for the function.")
          .Category("CategoryName")
          .HelpTopic("URL to help documentation")
      );
      ```

2.  Function Definition (`Function` class):
    - Defines the signature and metadata for an Excel-callable function.
    - Parameters:
        - `return_type`: The data type returned by the function (e.g., `XLL_DOUBLE`, `XLL_STRING`).
        - `c_function_name`: The name of the C++ function.
        - `ExcelFunctionName`: The name displayed in Excel's Function Wizard.
    - Methods:
        - `.Arguments({...})`: Specifies the arguments the function accepts. Each argument is defined using `Arg(type, name, help_text)`.
        - `.FunctionHelp("...")`: Provides a brief description of the function.
        - `.Category("...")`: Assigns the function to an Excel function category.
        - `.HelpTopic("URL")`: Links to external help documentation.

3.  Argument Definition (`Arg` struct):
    - Defines an individual argument for an Excel function.
    - Parameters:
        - `type`: The data type of the argument (e.g., `XLL_DOUBLE`, `XLL_LONG`).
        - `name`: The name of the argument as it appears in Excel.
        - `help_text`: A description of the argument.

4.  Function Implementation:
    - The actual C++ function that performs the computation.
    - Must be declared with `WINAPI` for compatibility with Excel's calling conventions.
    - The `#pragma XLLEXPORT` directive is crucial for exporting the function from the DLL so Excel can find it.
    - Example:
      ```cpp
      double WINAPI c_function_name(double arg_name) {
      #pragma XLLEXPORT
          // Function implementation
          return result;
      }
      ```

5.  Macros:
    - Functions that do not return a value and are used for their side effects (e.g., modifying worksheet data, triggering events).
    - They take no arguments.

Error Handling and Best Practices:
- Use `WINAPI` for functions called by Excel to prevent stack corruption.
- Ensure functions intended for Excel are exported using `#pragma XLLEXPORT` to avoid warnings or runtime errors.
- Provide comprehensive help text and documentation links for better usability within Excel.
```

--------------------------------

### Excel API: GET.WORKSPACE Function

Source: https://github.com/xlladdins/xll/blob/master/README.md

The GET.WORKSPACE function, when called with argument 2, returns the specific version of Excel as a string (e.g., "5.0"). This provides more granular version information than XLCallVer.

```APIDOC
GET.WORKSPACE:
  Arguments:
    arg1: Integer, specifies the information to retrieve. Use 2 for the Excel version string.
  Returns: String
    - A string representing the exact version of Excel, e.g., "5.0", "16.0".
  Description: Retrieves various workspace settings from Excel. Argument 2 specifically targets the Excel version.
  Usage:
    Excel4(xlfGetWorkspace, &version, 1, &two) // Using Excel4 API
    OPER version = Excel(xlfWorkspace, OPER(2)) // Using xll library wrapper
  Note: Memory allocated by Excel for the returned string must be freed using Excel4(xlFree, ...).
```

--------------------------------

### Calling Excel Functions with Excel4 and Excel12

Source: https://github.com/xlladdins/xll/blob/master/README.md

Demonstrates how to call Excel functions from an add-in using the Excel4 (or Excel4v) and Excel12 (or Excel12v) functions. These are the primary methods for interacting with Excel's core functionalities.

```c++
// Using Excel4 (for older versions or compatibility)
// Excel4(xlfFunctionName, &returnValue, numArgs, arg1, ...);

// Using Excel12 (for newer versions)
// Excel12(xlfFunctionName, &returnValue, numArgs, arg1, ...);
```

--------------------------------

### WINAPI and XLLEXPORT Directives

Source: https://github.com/xlladdins/xll/blob/master/docs/handles.txt

Highlights essential C++ compiler directives for creating Excel add-ins. `WINAPI` ensures correct calling conventions, and `DLLEXPORT` makes functions visible to Excel. Forgetting these can lead to crashes or load-time warnings.

```cpp
// Ensure functions are declared with WINAPI
// extern "C" __declspec(dllexport) int WINAPI MyExcelFunction(int arg1);
// 
// // Use pragma XLLEXPORT at the start of the function body
// int WINAPI MyExcelFunction(int arg1) {
//     #pragma XLLEXPORT
//     // ... function implementation ...
//     return 0;
// }
```

--------------------------------

### Calling Excel Functions and Macros with xll::Excel

Source: https://github.com/xlladdins/xll/blob/master/README.md

Details how to invoke Excel's built-in functions and macros from within an add-in using the `xll::Excel` class. It references `XLCALL.H` for function numbers and the Excel 4 Macro documentation for argument details.

```APIDOC
xll::Excel(int function_number, ...)
  - Calls an Excel function or macro using its corresponding function number.
  - Function numbers starting with 'xlf' are functions (purely functional).
  - Function numbers starting with 'xlc' are macros (can have side-effects).
  - Arguments must match the expected arguments for the given function number.
  - Refer to XLCALL.H for function number definitions and Excel 4 Macro documentation for argument details.

Example:
  // Calling XLM function XLCALL.H
  xll::Excel(xlfGetCell, 1, 1, 1);

Special Function Numbers:
  - xlUDF: Can be used to call User-Defined Functions.
```

--------------------------------

### Define Excel Function with xlladdins

Source: https://github.com/xlladdins/xll/blob/master/talk.html

This snippet demonstrates how to define a function for Excel using the xlladdins library. It specifies the function's return type, name, arguments, help text, category, and a link to external documentation.

```cpp
#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("MATH")
    .HelpTopic("https://docs.microsoft.com/en-us/cpp/c-runtime-library/reference/tgamma-tgammaf-tgammal")
);
```

--------------------------------

### xll::handle Class Usage

Source: https://github.com/xlladdins/xll/blob/master/talk.html

Demonstrates the usage of the `xll::handle` class for managing C++ objects within Excel. It covers object creation, dynamic casting using `as<U>()`, and the implications for garbage collection and memory management.

```APIDOC
Object Creation and Management:
  - Create a handle to an object `U` derived from `T`: `xll::handle<T> h(new U(args...));`
  - Return the `HANDLEX` using `h.get()`.
  - The handle can call member functions of `T` directly.
  - To call member functions of `U`, use `dynamic_cast` or `xll::handle<T>::as<U>()`.

Garbage Collection and `delete`:
  - `xll::handle` uses the Excel cell containing the handle for garbage collection.
  - `delete` is called on objects created with `xll::handle<T>(T*)` when the handle is in a cell and the constructor is called with new arguments.
  - Moving, copying, or pasting cells with handles does not cause memory leaks.
  - Temporary handles created within functions are deleted when the outer function exits.

Refreshing Handles (`new`):
  - When opening a spreadsheet in a new Excel session, handles need to be refreshed.
  - Use `Ctrl-Alt-F9` to call `new` on all handles.
  - This can also be used for garbage collection if a cell with a handle was deleted.

Excel Integration:
  - Embed C++ objects and functions by specifying return types, C++ functions to call, and Excel function names.
  - Define arguments and their appearance in the Function Wizard using `Arg()`.
  - Use `WINAPI` for functions called by Excel.
  - Optional parameters: `.Category`, `.FunctionHelp`, `.HelpTopic`.

Dynamic Arrays:
  - Excel's dynamic arrays allow entire arrays to be returned as a single value.
  - Refer to dynamic arrays using `A1#`, where `A1` is the top-left cell.
  - Excel passes the current size of the dynamic array to the function.

```

--------------------------------

### Excel Function and Macro Execution

Source: https://github.com/xlladdins/xll/blob/master/talk.html

Explains how to invoke Excel functions and macros using the `Excel` function. `Excel(xlfFunction, args, ...)` calls `=FUNCTION(args, ...)` and `Excel(xlcMacro, args, ...)` executes `MACRO(args, ...)`. Refer to Excel4Macros documentation for argument details.

```APIDOC
`Excel(xlfFunction, args, ...)` calls the Excel **f**unction `=FUNCTION(args, ...)`.

`Excel(xlcMacro, args, ...)` executes the **m**acro `MACRO(args, ...)`. This can only be used from macros, not functions. Consult the [Excel4Macros](https://xlladdins.github.io/Excel4Macros/) documentation to discern the appropriate arguments.
```

--------------------------------

### Excel String Handling with xll

Source: https://github.com/xlladdins/xll/blob/master/README.md

Explains how the xll library handles UTF-8 strings from Excel's 8-bit ASCII or 16-bit wide character formats using OPER type xltypeStr. It covers detecting strings, automatic encoding/decoding, and returning strings to Excel.

```cpp
/*
 * 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.
 */

// Example of checking if an OPER is a string:
// if (oper.is_str()) { ... }

// Example of returning a string OPER:
// Function(..., return_type=xltypeStr)
// OPER my_string_oper = "Hello, Excel!";
// return &my_string_oper; // Requires proper memory management (xlbitDLLFree or xlbitXLFree)

```

--------------------------------

### Embed base<OPER> in Excel

Source: https://github.com/xlladdins/xll/blob/master/talk.html

This C++ code demonstrates embedding a generic `base<OPER>` class into Excel. It defines functions to create a handle to a `base<OPER>` object and retrieve its value using the handle.

```cpp
// base.h
#include <concepts>

template<std::regular T>
class base {
    T t;
public:
    base()
    {
    }
    base(const T& t)
        : t(t)
    {
    }
    virtual ~base()
    {
    }
    T get() const
    {
        return t;
    }
};

// xll_base.cpp
#include "base.h"
#include "xll/xll.h"

using namespace xll;

AddIn xai_base(
    Function(XLL_HANDLEX, "xll_base", "\XLL.BASE")
    .Arguments({
        Arg(XLL_LPOPER, "cell", "is a cell.")
    })
    .Uncalced() // \XLL.BASE has a side effect
);
HANDLEX WINAPI xll_base(LPOPER po)
{
#pragma XLLEXPORT
    handle<base<OPER>> h(new base(*po));
    
    return h.get();
}

AddIn xai_base_get(
    Function(XLL_LPOPER, "xll_base_get", "XLL.BASE.GET")
    .Arguments({
        Arg(XLL_HANDLEX, "handle", "is a handle to a base<OPER> object.")
    })
);
LPOPER WINAPI xll_base_get(HANDLEX h)
{
#pragma XLLEXPORT
    handle<base<OPER>> h_(h);
    
    return h_ ? &h_->get() : (LPOPER)ErrValue;
}
```
Tokens:
Raw