How to: Create a Simple Custom Function
- 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.
Note
A complete sample project is available at https://github.com/DevExpress-Examples/wpf-spreadsheetcontrol-api-part-2-e4944.
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