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.

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;
    }
}