You can register your own functions for formulas to call. They sit on the same machinery as the built-ins, so dependency recalculation and reference shifting apply unchanged.

The namespace is unvell.ReoGrid.Core.Formula.

Registering one

using unvell.ReoGrid.Core;
using unvell.ReoGrid.Core.Formula;

// an ordinary function whose arguments arrive already evaluated
FunctionRegistry.Default.Eager("TAXINCL", (args, ctx) =>
{
	if (args.Length < 1) return FormulaValue.Err(FormulaError.NA);
	if (args[0].IsError) return args[0];        // let errors propagate

	double rate = args.Length > 1 ? args[1].Num : 0.1;
	return FormulaValue.Number(args[0].Num * (1 + rate));
});

ws.SetFormula(0, 1, "TAXINCL(A1)");
ws.SetFormula(1, 1, "TAXINCL(A2, 0.08)");

Register the name in upper case. Function names in a formula are normalized to upper case before lookup.

Registration happens on FunctionRegistry.Default, so it is shared across the process. Do it once, at application start-up.

FormulaValue

The type of both arguments and return values.

var n = FormulaValue.Number(42);
var t = FormulaValue.Text("hello");
var b = FormulaValue.Logical(true);
var d = FormulaValue.Date(DateTime.Today.ToOADate());
var e = FormulaValue.Err(FormulaError.Value);

Receiving a range

Ranges arrive unexpanded. To read the contents, go through the evaluation context.

FunctionRegistry.Default.Eager("COUNTPOSITIVE", (args, ctx) =>
{
	int count = 0;

	foreach (var a in args)
	{
		if (a.IsError) return a;

		if (a.IsRange)
		{
			for (int r = a.R1; r <= a.R2; r++)
				for (int c = a.C1; c <= a.C2; c++)
				{
					var v = ctx.GetCell(r, c, a.RangeSheet);
					if (v.IsNumberLike && v.Num > 0) count++;
				}
		}
		else if (a.IsNumberLike && a.Num > 0) count++;
	}

	return FormulaValue.Number(count);
});

a.RangeSheet carries the sheet name when the range is on another sheet (null for the same sheet). Pass it to ctx.GetCell(row, col, sheet).

Rules to follow

1. Propagate errors. When an argument is an error, returning it unchanged is the default convention
2. Return #N/A for too few arguments and #VALUE! for a type mismatch
3. Do not throw. An exception during evaluation fails the entire recalculation. Return an error value instead
4. Have no side effects. Neither when nor how often a recalculation happens is guaranteed

Lazy evaluation

Registering with Lazy hands you the arguments’ syntax tree rather than evaluated values. Use it for functions that work with references themselves, like ROW, COLUMN and OFFSET.

FunctionRegistry.Default.Lazy("MYREF", (argNodes, ctx) => { ... });

For ordinary computation, use Eager.

Inspecting the registry

bool exists = FunctionRegistry.Default.Has("TAXINCL");
int total = FunctionRegistry.Default.Count;

foreach (string name in FunctionRegistry.Default.Names)
	Console.WriteLine(name);

Persistence

A formula using a custom function is saved verbatim, as text. If the same function is not registered on the loading side, it becomes #NAME?.

Exported to XLSX, Excel naturally cannot interpret the function. Whether to put custom functions in a file you distribute is a judgement call.

What to read next

• Function Reference
• The Formula Engine