How to: Create a Simple Custom Function

  • 3 min 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.

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