**The calculation engine in EPPlus [supports over 470 functions](<https://epplussoftware.com/docs/formulas-and-filters/formula-calculation/supported-functions/index.md>). For various reasons you might want to add your own custom functions - for example, you could implement functions that corresponds to VBA functions in a workbook. From version 7 that introduces support for dynamic arrayformulas, the interface for creating custom function has changed significantly.**

See [this wiki page](<https://epplussoftware.com/docs/Implementing-missing-or-new-Excel-functions/index.md>) for how to add custom functions in version 6 and below.

## The ExcelFunction class

Every function in Epplus must inherit the abstract `OfficeOpenXml.FormulaParsing.Excel.Functions.ExcelFunction`. Here is an example of how to implement a function, using the existing Cos function in EPPlus:

```csharp
public class Cos : ExcelFunction
{
    public override ExcelFunctionArrayBehaviour ArrayBehaviour => ExcelFunctionArrayBehaviour.FirstArgCouldBeARange;
    public override int ArgumentMinLength => 1;

    public override CompileResult Execute(IList<FunctionArgument> arguments, ParsingContext context)
    {
        var arg = ArgToDecimal(arguments, 0, out ExcelErrorValue e1);
        // If an error occurred when parsing the first argument to a double
        // an error will be provided via the out parameter e1. If so, return the error.
        if (e1 != null) return CompileResult.GetErrorResult(e1.Type);
        return CreateResult(Math.Cos(arg), DataType.Decimal);
    }
}
```

```vbnet
Public Class Cos
    Inherits ExcelFunction

    Public Overrides ReadOnly Property ArrayBehaviour As ExcelFunctionArrayBehaviour
        Get
            Return ExcelFunctionArrayBehaviour.FirstArgCouldBeARange
        End Get
    End Property

    Public Overrides ReadOnly Property ArgumentMinLength As Integer
        Get
            Return 1
        End Get
    End Property

    Public Overrides Function Execute(arguments As IList(Of FunctionArgument), context As ParsingContext) As CompileResult
        Dim e1 As ExcelErrorValue = Nothing
        Dim arg = ArgToDecimal(arguments, 0, e1)
        ' If an error occurred when parsing the first argument to a double
        ' an error will be provided via the out parameter e1. If so, return the error.
        If e1 IsNot Nothing Then Return CompileResult.GetErrorResult(e1.Type)
        Return CreateResult(Math.Cos(arg), DataType.Decimal)
    End Function
End Class
```

### The Execute function

Must be overridden from the `ExcelFunction` base class. This is where you write the actual implementation of your custom function. This function has two arguments:

| Argument | Type | Description |
| :--- | :--- | :--- |
| arguments | `List<FunctionArgument>` | The arguments of the function. The `Value` property of the `FunctionArgument` argument class contains the argument. |
| context | `ParsingContext` | Contains information about the ongoing calculation, such as the address of the cell in which your function is executing. |

### ArrayBehaviour

Can be (virtual) overridden from the `ExcelFunction` base class. Use this property to indicate if your function can return an array. This property can have the following values:

| Value | Description |
| :--- | :--- |
| `None` (default) | The function does not support arrays (returns single values only) |
| `Custom` | The function supports arrays, but not according to any of the options in this enum. If a function returns this value should also implement the `ExcelFunction.ConfigureArrayBehaviour(ArrayBehaviourConfig)` function. |
| `FirstArgCouldBeARange` | The function supports arrays and the first argument could be a range. No further configuration will be needed (as for the Custom option) and if the first argument is a range (ex. A1:C5) of cell values the function will be called once for each cell and the result will be an array of values. |

#### ConfigureArrayBehaviour

Here is an example on how to configure a function where the first three arguments can be ranges/arrays:

```csharp
public override ExcelFunctionArrayBehaviour ArrayBehaviour => ExcelFunctionArrayBehaviour.Custom;

public override void ConfigureArrayBehaviour(ArrayBehaviourConfig config)
{
    config.SetArrayParameterIndexes(0, 1, 2);
}
```

```vbnet
Public Overrides ReadOnly Property ArrayBehaviour As ExcelFunctionArrayBehaviour
    Get
        Return ExcelFunctionArrayBehaviour.Custom
    End Get
End Property

Public Overrides Sub ConfigureArrayBehaviour(config As ArrayBehaviourConfig)
    config.SetArrayParameterIndexes(0, 1, 2)
End Sub
```

### ArgumentMinLength

Must be overridden from the `ExcelFunction` base class. Specifies the minimum number of required arguments. If the function is called with a smaller number of arguments than the specified value a \#VALUE! error will be returned.

### ParametersInfo

You can configure how errors, addresses, conditions, etc, is handled for your function by overriding the `ParametersInfo` property. See this example:

```csharp
// This example shows how to instruct the calculation engine to pass errors (#VALUE!, #REF, etc) on to the function.
public override ExcelFunctionParametersInfo ParametersInfo => new ExcelFunctionParametersInfo(new Func<int, FunctionParameterInformation>((argumentIndex) =>
{
    // since we don't test against the argument index, errors in any argument will be ignored and passed to the function.
    return FunctionParameterInformation.IgnoreErrorInPreExecute;
}));

// This example shows how to instruct the calculation engine to ignore an address 
// in the second function argument (to avoid circular reference checks, etc).
public override ExcelFunctionParametersInfo ParametersInfo => new ExcelFunctionParametersInfo(new Func<int, FunctionParameterInformation>((argumentIndex) =>
{
    if(argumentIndex == 1)
    {
        return FunctionParameterInformation.IgnoreAddress;
    }
    return FunctionParameterInformation.Normal;
}));
```

```vbnet
' This example shows how to instruct the calculation engine to pass errors (#VALUE!, #REF, etc) on to the function.
Public Overrides ReadOnly Property ParametersInfo As ExcelFunctionParametersInfo
    Get
        Return New ExcelFunctionParametersInfo(New Func(Of Integer, FunctionParameterInformation)(
            Function(argumentIndex)
                ' since we don't test against the argument index, errors in any argument will be ignored and passed to the function.
                Return FunctionParameterInformation.IgnoreErrorInPreExecute
            End Function))
    End Get
End Property

' This example shows how to instruct the calculation engine to ignore an address 
' in the second function argument (to avoid circular reference checks, etc).
Public Overrides ReadOnly Property ParametersInfo As ExcelFunctionParametersInfo
    Get
        Return New ExcelFunctionParametersInfo(New Func(Of Integer, FunctionParameterInformation)(
            Function(argumentIndex)
                If argumentIndex = 1 Then
                    Return FunctionParameterInformation.IgnoreAddress
                End If
                Return FunctionParameterInformation.Normal
            End Function))
    End Get
End Property
```

### NamespacePrefix

Should only be used if Excel (or whatever spreadsheet application you are using) requires a [namespace prefix](<https://epplussoftware.com/docs/formulas-and-filters/formula-calculation/function-prefixes/index.md>).

```csharp
public override string NamespacePrefix => "_xlfn.";
```

```vbnet
Public Overrides ReadOnly Property NamespacePrefix As String
    Get
        Return "_xlfn."
    End Get
End Property
```

## Returning arrays/ranges from functions

As shown in the [previous chapter](<#arraybehaviour>) you can configure your function to return arrays based on input parameters. In this case EPPlus will calculate the size of the result range and fill it by calling the function once for each cell in the range. However, you can also let the function return an array of any height/with by using the new `OfficeOpenXml.FormulaParsing.Ranges.InMemoryRange` class.

```csharp
var range = new InMemoryRange(nRows, nCols);
// set a value
var v = 1;
var row = 0;
var col = 0;
range.SetValue(row, col, v);
```

```vbnet
Dim range = New InMemoryRange(nRows, nCols)
' set a value
Dim v = 1
Dim row = 0
Dim col = 0
range.SetValue(row, col, v)
```

### Returning an InMemoryRange from a function

Use the `CreateDynamicArrayResult` for dynamic array formulas. This will make sure the result will be a dynamic array formula even if the return array is empty or only covers one cell.

```csharp
return CreateDynamicArrayResult(range, DataType.ExcelRange);
```

```vbnet
Return CreateDynamicArrayResult(range, DataType.ExcelRange)
```

You can also use the `CreateResult` method, but then empty/single cell results will not be stored as a dynamic array.

```csharp
return CreateResult(range, DataType.ExcelRange);
```

```vbnet
Return CreateResult(range, DataType.ExcelRange)
```

## Add a new function in runtime

Here is how you can add a custom function to your ExcelPackage instance in runtime.

```csharp
using (var package = new ExcelPackage())
{
    package.Workbook.FormulaParserManager.AddOrReplaceFunction("cos", new Cos());
}
```

```vbnet
Using package As New ExcelPackage()
    package.Workbook.FormulaParserManager.AddOrReplaceFunction("cos", New Cos())
End Using
```

If you have created many functions you can bundle them together into a module by inheriting the `OfficeOpenXml.FormulaParsing.Excel.Functions.FunctionsModule` class:

```csharp
class MyFunctionModule : FunctionsModule
{
    public MyFunctionModule()
    {
        base.Functions.Add("sum.addtwo", new SumAddTwo());
        base.Functions.Add("shoesizeconvert", new ShoeSizeConvert());
    }
}

/// <summary>
/// Adds two to all numbers in the supplied range and calculates the sum.
/// </summary>
class SumAddTwo : ExcelFunction
{
    public override int ArgumentMinLength => 1;

    public override CompileResult Execute(IEnumerable<FunctionArgument> arguments, ParsingContext context)
    {            
        // Helper method that converts function arguments to an enumerable of doubles
        var numbers = ArgsToDoubleEnumerable(arguments, context, out ExcelErrorValue e1);
        if(e1 != null) return CompileResult.GetErrorResult(e1.Type); 

        // Do the work
        var result = 0d;
        numbers.ToList().ForEach(x => result += (x + 2));

        // return the result
        return CreateResult(result, DataType.Decimal);
    }
}
/// <summary>
/// This function converts EU shoe size to US shoe size and vice versa.
/// </summary>
class ShoeSizeConvert : ExcelFunction
{
    public override int ArgumentMinLength => 2;

    public override CompileResult Execute(IEnumerable<FunctionArgument> arguments, ParsingContext context)
    {
        // The first argument is the shoe size to convert
        var size = ArgToDecimal(arguments, 0, out ExcelErrorValue e1);
        if(e1 != null) return CompileResult.GetErrorResult(e1.Type); 

        // The second argument is convert type, an optional parameter.
        // 1 is the default value.
        var convertType = 1;
        if(arguments.Count() > 1)
        {
            convertType = ArgToInt(arguments, 1, out ExcelErrorValue e3);
            if(e3 != null) return CompileResult.GetErrorResult(e3.Type);
        }

        // Calculate the result
        double result;
        switch(convertType)
        {
            case 1:
                // EU to US
                result = size - 33;
                break;
            case 2:
                // US to EU
                result = size + 33;
                break;
            default:
                // not supported conversion type, return a #VALUE! error.
                return CreateResult(eErrorType.Value);
        }
        return CreateResult(result, DataType.Decimal);
    }
}
```

```vbnet
Friend Class MyFunctionModule
    Inherits FunctionsModule

    Public Sub New()
        MyBase.Functions.Add("sum.addtwo", New SumAddTwo())
        MyBase.Functions.Add("shoesizeconvert", New ShoeSizeConvert())
    End Sub
End Class

''' <summary>
''' Adds two to all numbers in the supplied range and calculates the sum.
''' </summary>
Friend Class SumAddTwo
    Inherits ExcelFunction

    Public Overrides ReadOnly Property ArgumentMinLength As Integer
        Get
            Return 1
        End Get
    End Property

    Public Overrides Function Execute(arguments As IEnumerable(Of FunctionArgument), context As ParsingContext) As CompileResult
        ' Helper method that converts function arguments to an enumerable of doubles
        Dim e1 As ExcelErrorValue = Nothing
        Dim numbers = ArgsToDoubleEnumerable(arguments, context, e1)
        If e1 IsNot Nothing Then Return CompileResult.GetErrorResult(e1.Type)

        ' Do the work
        Dim result = 0.0R
        numbers.ToList().ForEach(Sub(x) result += (x + 2))

        ' return the result
        Return CreateResult(result, DataType.Decimal)
    End Function
End Class

''' <summary>
''' This function converts EU shoe size to US shoe size and vice versa.
''' </summary>
Friend Class ShoeSizeConvert
    Inherits ExcelFunction

    Public Overrides ReadOnly Property ArgumentMinLength As Integer
        Get
            Return 2
        End Get
    End Property

    Public Overrides Function Execute(arguments As IEnumerable(Of FunctionArgument), context As ParsingContext) As CompileResult
        ' The first argument is the shoe size to convert
        Dim e1 As ExcelErrorValue = Nothing
        Dim size = ArgToDecimal(arguments, 0, e1)
        If e1 IsNot Nothing Then Return CompileResult.GetErrorResult(e1.Type)

        ' The second argument is convert type, an optional parameter.
        ' 1 is the default value.
        Dim convertType = 1
        If arguments.Count() > 1 Then
            Dim e3 As ExcelErrorValue = Nothing
            convertType = ArgToInt(arguments, 2, e3)
            If e3 IsNot Nothing Then Return CompileResult.GetErrorResult(e3.Type)
        End If

        ' Calculate the result
        Dim result As Double
        Select Case convertType
            Case 1
                ' EU to US
                result = size - 33
            Case 2
                ' US to EU
                result = size + 33
            Case Else
                ' not supported conversion type, return a #VALUE! error.
                Return CreateResult(eErrorType.Value)
        End Select
        Return CreateResult(result, DataType.Decimal)
    End Function
End Class
```

Then you can add the module to EPPlus in runtime:

```csharp
package.Workbook.FormulaParserManager.LoadFunctionModule(new MyFunctionModule());
```

```vbnet
package.Workbook.FormulaParserManager.LoadFunctionModule(New MyFunctionModule())
```

### See also

[Formula calculation](<https://epplussoftware.com/docs/formulas-and-filters/formula-calculation/index.md>)