How to: Create a Simple Custom Function

• Jun 07, 2019
• 3 minutes to read

This example demonstrates how to create a custom worksheet function. A custom function is an object that implements the ICustomFunction interface. The IFunction.Evaluate method performs all required calculations. To use a custom function, add it to the IWorkbook.CustomFunctions collection.

The following code sample demonstrates how to implement a custom function called SPHEREMASS, which calculates the mass of a sphere made of a material with a specified density. If the density is not provided, the density of water is used.

Note

Custom functions are not saved in workbook files.

View Example

CustomFunctionActions.vb

Imports DevExpress.Spreadsheet
Imports DevExpress.Spreadsheet.Functions
' Inheritance from Object is required for correct automatic VB.NET conversion
Public Class SphereMassFunction
    Inherits Object
    Implements ICustomFunction

    Private Const functionName As String = "SPHEREMASS"
    Private ReadOnly functionParameters() As ParameterInfo

    Public Sub New()
        ' Missing optional parameters do not result in error message.
        Me.functionParameters = New ParameterInfo() {
            New ParameterInfo(ParameterType.Value, ParameterAttributes.Required),
            New ParameterInfo(ParameterType.Value, ParameterAttributes.Optional)
        }
    End Sub

        Public ReadOnly Property Name() As String Implements ICustomFunction.Name
            Get
                Return functionName
            End Get
        End Property
    Private ReadOnly Property IFunction_Parameters() As ParameterInfo() Implements IFunction.Parameters
        Get
            Return functionParameters
        End Get
    End Property
    Private ReadOnly Property IFunction_ReturnType() As ParameterType Implements IFunction.ReturnType
        Get
            Return ParameterType.Value
        End Get
    End Property
    ' Reevaluate cells on every recalculation.
    Private ReadOnly Property IFunction_Volatile() As Boolean Implements IFunction.Volatile
        Get
            Return True
        End Get
    End Property

    Private Function IFunction_Evaluate(ByVal parameters As IList(Of ParameterValue), ByVal context As EvaluationContext) As ParameterValue Implements IFunction.Evaluate
        Dim radius As Double
        Dim density As Double = 1000
        Dim radiusParameter As ParameterValue
        Dim densityParameter As ParameterValue

        If parameters.Count = 2 Then
            densityParameter = parameters(1)
            If densityParameter.IsError Then
                Return densityParameter
            Else
                density = densityParameter.NumericValue
            End If
        End If

        radiusParameter = parameters(0)
        If radiusParameter.IsError Then
            Return radiusParameter
        Else
            radius = radiusParameter.NumericValue
        End If

        Return (4 * Math.PI) / 3 * Math.Pow(radius,3) * density

    End Function
    Private Function IFunction_GetName(ByVal culture As CultureInfo) As String Implements IFunction.GetName
        Return functionName
    End Function
End Class

CustomFunctionActions.cs

using DevExpress.Spreadsheet;
using DevExpress.Spreadsheet.Functions;
// Inheritance from Object is required for correct automatic VB.NET conversion
public class SphereMassFunction : Object, ICustomFunction
{
    const string functionName = "SPHEREMASS";
    readonly ParameterInfo[] functionParameters;

    public SphereMassFunction()
    {   
        // Missing optional parameters do not result in error message.
        this.functionParameters = new ParameterInfo[] { new ParameterInfo(ParameterType.Value, ParameterAttributes.Required), 
            new ParameterInfo(ParameterType.Value, ParameterAttributes.Optional)};
    }

    public string Name { get { return functionName; } }
    ParameterInfo[] IFunction.Parameters { get { return functionParameters; } }
    ParameterType IFunction.ReturnType { get { return ParameterType.Value; } }
    // Reevaluate cells on every recalculation.
    bool IFunction.Volatile { get { return true; } }

    ParameterValue IFunction.Evaluate(IList<ParameterValue> parameters, EvaluationContext context)
    {
        double radius;
        double density = 1000;
        ParameterValue radiusParameter;
        ParameterValue densityParameter;

        if (parameters.Count == 2)
        {
            densityParameter = parameters[1];
            if (densityParameter.IsError)
                return densityParameter;
            else 
                density = densityParameter.NumericValue;                
        }

        radiusParameter = parameters[0];
        if (radiusParameter.IsError)
            return radiusParameter;
        else
            radius = radiusParameter.NumericValue;

        return (4 * Math.PI) / 3 * Math.Pow(radius,3) * density;

    }
    string IFunction.GetName(CultureInfo culture)
    {
        return functionName;
    }
}