How to: Create a Custom Function that Returns an Array

  • 3 minutes to read

This example implements a custom function (UDF) that returns an array. The TESTARRAY function gets a range of cells, multiplies relative indexes of a cell in the range by the cell value and returns the resulting array.

To display the result in cells, each cell should contain an array formula with that function. To insert an array formula, the example uses the cell's CellRange.ArrayFormulaInvariant property.

You can insert array formulas using the keyboard shortcut: select a range for the output array, enter a formula, and press Ctrl+Shift+Enter.

View Example

TestArrayCustomFunction customFunction = new TestArrayCustomFunction();
if (!spreadsheet.Document.Functions.CustomFunctions.Contains(customFunction.Name))
    spreadsheet.Document.Functions.CustomFunctions.Add(customFunction);

spreadsheet.ActiveWorksheet.Range["$A$4:$E$5"].ArrayFormulaInvariant = "TESTARRAY(A1:E2)";
spreadsheet.ActiveWorksheet.Range["E7"].Formula = "SUM(TESTARRAY(A1:E2))";