How to: Create a Custom Function that Returns an Array

• Sep 27, 2023
• 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.

MainWindow.xaml.cs

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))";

MainWindow.xaml.vb

Dim customFunction As New TestArrayCustomFunction()
If Not spreadsheet.Document.Functions.CustomFunctions.Contains(customFunction.Name) Then
    spreadsheet.Document.Functions.CustomFunctions.Add(customFunction)
End If

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

TestArrayCustomFunction.cs

public class TestArrayCustomFunction : ICustomFunction {
    private string name = "TESTARRAY";
    private ParameterInfo[] parameters = new ParameterInfo[] { new ParameterInfo(ParameterType.Array, ParameterAttributes.Required) };
    private ParameterType return_type = ParameterType.Array;

    public string Name { get { return this.name; } }
    public ParameterInfo[] Parameters { get { return this.parameters; } }
    public ParameterType ReturnType { get { return this.return_type; } }
    public bool Volatile { get { return false; } }
    public string GetName(CultureInfo culture) {
        return Name;
    }
    public ParameterValue Evaluate(IList<ParameterValue> parameters, EvaluationContext context) {
        CellValue[,] args = parameters[0].ArrayValue;
        int xDim = args.GetLength(0);
        int yDim = args.GetLength(1);
        CellValue[,] result = new CellValue[xDim, yDim];

        for (int n = 0; n < args.GetLength(0); n++) {
            for (int m = 0; m < result.GetLength(1); m++) {
                double coeff = (args[n, m].NumericValue == 0) ? 1 : args[n, m].NumericValue;
                result[n, m] = (n + 1) * (m + 1) * coeff;
            }
        }

        return result;
    }
}

TestArrayCustomFunction.vb

Public Class TestArrayCustomFunction
    Implements ICustomFunction
    Private name_Renamed As String = "TESTARRAY"
    Private parameters_Renamed() As ParameterInfo = { New ParameterInfo(ParameterType.Array, ParameterAttributes.Required) }
    Private return_type As ParameterType = ParameterType.Array

    Public ReadOnly Property Name() As String Implements ICustomFunction.Name
        Get
            Return Me.name_Renamed
        End Get
    End Property
    Public ReadOnly Property Parameters() As ParameterInfo() Implements ICustomFunction.Parameters
        Get
            Return Me.parameters_Renamed
        End Get
    End Property
    Public ReadOnly Property ReturnType() As ParameterType Implements ICustomFunction.ReturnType
        Get
            Return Me.return_type
        End Get
    End Property
    Public ReadOnly Property Volatile() As Boolean Implements ICustomFunction.Volatile
        Get
            Return False
        End Get
    End Property
    Public Function GetName(ByVal culture As CultureInfo) As String Implements ICustomFunction.GetName
        Return Name
    End Function
    Public Function Evaluate(ByVal parameters As IList(Of ParameterValue), ByVal context As EvaluationContext) As ParameterValue Implements ICustomFunction.Evaluate
        Dim args(,) As CellValue = parameters(0).ArrayValue
        Dim xDim As Integer = args.GetLength(0)
        Dim yDim As Integer = args.GetLength(1)
        Dim result(xDim - 1, yDim - 1) As CellValue

        For n As Integer = 0 To args.GetLength(0) - 1
            For m As Integer = 0 To result.GetLength(1) - 1
                Dim coeff As Double = If(args(n, m).NumericValue = 0, 1, args(n, m).NumericValue)
                result(n, m) = (n + 1) * (m + 1) * coeff
            Next m
        Next n

        Return result
    End Function
End Class