Custom functions

Functions written in C# that formulas call by name, in place of VBA functions or add-ins that a workbook expects.

Adding a function

workbook.Functions.Add("VAT", args =>
{
    CellValue amount = args.Number(0);   // converted as Excel converts: "100" is 100
    if (amount.IsError)
        return amount;                  // pass #VALUE! and other errors on
    return amount.AsNumber() * 0.2;
}, new FunctionOptions { MinArguments = 1, MaxArguments = 1, ScalarParameters = [0] });

sheet["A1"].Value = 100;
sheet["A2"].Value = 250;
sheet["B1"].Formula = "=VAT(A1)";      // 20
sheet["C1"].Formula = "=VAT(A1:A2)";   // a scalar parameter given a range: spills 20 and 50
workbook.Recalculate();

Workbook.Functions.Add takes a name, a body and options. Formulas call the function ignoring case, and can pass it as a value where a LAMBDA is expected, as in MAP(A1:A9, VAT). Formulas that were #NAME? because the function was missing are calculated again by the next Recalculate; Remove turns its calls back into #NAME?. Each workbook has its own functions, and Workbook.Clone copies them.

The name has letters, digits, . and _, and starts with a letter or _. The name of a function SharpCell implements is refused: their results are checked against Excel. A name Excel knows but SharpCell does not implement, such as WEBSERVICE, can be added. In files, Excel writes calls of add-in functions as _xll.NAME(...) and calls of functions it does not know as _xludf.NAME(...); both find the function NAME.

Arguments

FunctionArguments gives the body the call's arguments:

• args[i] is the value: a single cell's value, a range as a CellValueKind.Array, an error as an error value (the function decides what to do with it), and CellValue.Missing for an argument left out, as the middle one of F(1,,2).
• Number(i), Text(i) and Boolean(i) convert the argument as Excel does and return either the converted value or the error to return, such as #VALUE! for "abc" read as a number.
• Caller is the cell whose formula makes the call; null for Workbook.Evaluate.
• DateSystem and Culture are the workbook's, for dates and text.

A parameter that is not scalar receives a range as an array of its values:

workbook.Functions.Add("SUMSQUARES", args =>
{
    double total = 0;
    foreach (CellValue value in args[0].Kind == CellValueKind.Array ? args[0].AsArray() : new[,] { { args[0] } })
    {
        if (value.Kind == CellValueKind.Number)
            total += value.AsNumber() * value.AsNumber();
    }

    return total;
}, new FunctionOptions { MinArguments = 1, MaxArguments = 1 });

sheet["B1"].Formula = "=SUMSQUARES(A1:A3)";   // the range arrives as an array: 25

Results and failures

The body returns a CellValue. An array result spills like any dynamic array. Missing and lambda results are #VALUE!.

An exception thrown by the body makes the cell #VALUE! and adds a diagnostic of kind DiagnosticKind.CustomFunctionFailure, naming the function and the exception. The rest of the workbook calculates as usual. An OperationCanceledException cancels the calculation only when its token was cancelled; otherwise it is a failure like any other exception.

Rules for the body

• Read cells only through the arguments. SharpCell records which cells a formula reads, to know what to calculate again. A cell the body reads on its own, such as args.Caller!.Worksheet["Z1"].Value, is not recorded: the formula is not updated when it changes. Pass it as an argument, or make the function volatile.
• Do not change the workbook. Setting cells, defining names, adding sheets or functions, and calling Recalculate or Evaluate from inside a body throw InvalidOperationException, which turns into #VALUE! and a diagnostic.
• Keep the arguments to the call. FunctionArguments is valid only while the body runs. An array it gives is a copy, which the body may change.
• Expect extra calls. When a formula's inputs are out of date, SharpCell may call the body before calculating them, with stand-in values, and throw that result away. A body without side effects does not notice; one that calls a service should cache or tolerate it.
• Stop when cancelled. args.CancellationToken is the token given to Recalculate; a slow body can throw OperationCanceledException through it to cancel the calculation.
• Clones share the body. Workbook.Clone copies the delegate, not what it captures: a body used by clones on several threads must be thread-safe.

A workbook whose formulas use custom functions saves with XlsxWriter.Save like any other: the results are calculated by SharpCell, so KeepUncalculated is not needed.