How to: Create a Simple Custom Function

• Dec 12, 2022
• 4 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.

Note

Custom functions are not saved in workbook files.

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.

View Example

CustomFunctionActions.cs

using DevExpress.Spreadsheet;
using DevExpress.Spreadsheet.Functions;
using DevExpress.XtraSpreadsheet;
//...

public class SphereMassFunction : ICustomFunction
{
    const string functionName = "SPHEREMASS";
    readonly ParameterInfo[] functionParameters;

    public SphereMassFunction()
    {   
        // Missing optional parameters do not result in an 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; } }
    bool IFunction.Volatile { get { return false; } }

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

// Create a custom function and add it to the global scope.
SphereMassFunction customFunction = new SphereMassFunction();
var globalFunctions = workbook.Functions.GlobalCustomFunctions;
if (!globalFunctions.Contains(customFunction.Name))
    globalFunctions.Add(customFunction);


workbook.BeginUpdate();

try
{
    Worksheet worksheet = workbook.Worksheets[0];
    worksheet.Range["A1:H1"].ColumnWidthInCharacters = 12;
    worksheet.Range["A1:H1"].Alignment.Horizontal = SpreadsheetHorizontalAlignment.Center;

    worksheet.DefinedNames.Add("seawater", "1025");

    worksheet["A1"].Value = "Radius, m";
    worksheet["B1"].Value = "Material";
    worksheet["C1"].Value = "Mass, kg";
    worksheet["A2"].Value = 0.1;
    worksheet["B2"].Value = "";
    worksheet["C2"].FormulaInvariant = "=SPHEREMASS(A2)";
    worksheet["C2"].NumberFormat = "#.##";
    worksheet["A3"].Value = 0.1;
    worksheet["B3"].Value = "Seawater";
    worksheet["C3"].FormulaInvariant = "=SPHEREMASS(A3, seawater)";
    worksheet["C3"].NumberFormat = "#.##";
}
finally
{    
    workbook.EndUpdate();
}

CustomFunctionActions.vb

Imports DevExpress.Spreadsheet
Imports DevExpress.Spreadsheet.Functions
Imports DevExpress.XtraSpreadsheet
'...

Public Class SphereMassFunction
    Implements ICustomFunction

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

    Public Sub New()
        ' Missing optional parameters do not result in an 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
        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
    Private ReadOnly Property IFunction_Volatile() As Boolean Implements IFunction.Volatile
        Get
            Return False
        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
'...

' Create a custom function and add it to the global scope.
Private customFunction As New SphereMassFunction()
Private globalFunctions = workbook.Functions.GlobalCustomFunctions
If Not globalFunctions.Contains(customFunction.Name) Then
    globalFunctions.Add(customFunction)
End If


workbook.BeginUpdate()

Try
    Dim worksheet As Worksheet = workbook.Worksheets(0)
    worksheet.Range("A1:H1").ColumnWidthInCharacters = 12
    worksheet.Range("A1:H1").Alignment.Horizontal = SpreadsheetHorizontalAlignment.Center

    worksheet.DefinedNames.Add("seawater", "1025")

    worksheet("A1").Value = "Radius, m"
    worksheet("B1").Value = "Material"
    worksheet("C1").Value = "Mass, kg"
    worksheet("A2").Value = 0.1
    worksheet("B2").Value = ""
    worksheet("C2").FormulaInvariant = "=SPHEREMASS(A2)"
    worksheet("C2").NumberFormat = "#.##"
    worksheet("A3").Value = 0.1
    worksheet("B3").Value = "Seawater"
    worksheet("C3").FormulaInvariant = "=SPHEREMASS(A3, seawater)"
    worksheet("C3").NumberFormat = "#.##"
Finally
    workbook.EndUpdate()
End Try

Tip

Refer to the following article for information on how to create a custom function that uses a cell range as a parameter:

Read Tutorial: How to: Create a Custom Function that Returns an Array