How to: Create a Custom Function that Returns an Array

• Nov 28, 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.

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 IFunction.Name
        Get
            Return Me.name_Renamed
        End Get
    End Property
    Public ReadOnly Property Parameters() As ParameterInfo() Implements IFunction.Parameters
        Get
            Return Me.parameters_Renamed
        End Get
    End Property
    Public ReadOnly Property ReturnType() As ParameterType Implements IFunction.ReturnType
        Get
            Return Me.return_type
        End Get
    End Property
    Public ReadOnly Property Volatile() As Boolean Implements IFunction.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

Form1.cs

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

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

Form1.vb

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

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