If you have functions that you use on a regular basis that are not in the built-in functions or if you wish to combine some of the built-in functions into a single function, you can do so by defining your own custom functions. They can be called as you would call any of the built-in functions.

A custom function can have the same name as a built-in function. Built-in functions take priority over custom functions. Custom functions are dynamically linked at evaluation time.

If a formula attempts to call a custom function with a parameter count outside of the range indicated by the MinArgs and MaxArgs properties of the function, then the Evaluate method of the function is skipped and the #VALUE! error value is used as the result.

Also, if a formula attempts to call a custom function with a parameter that is an error value (for example, #NUM!, #VALUE!, #REF!) and the AcceptsError method of the function returns false for that parameter, then the Evaluate method of the function is skipped and the error value is used as the result.

The custom function's Evaluate method does not receive any information regarding the location (or context) in which the formula is being evaluated. If your custom function needs the row and column in which it is being evaluated then you must add extra parameters to your custom function and manually pass the row and column coordinates in the extra parameters.

The following example uses the AddCustomFunction method to add the custom function and uses the SetFormula method to assign the custom function to a cell.

Using Code

This example creates a custom function.

public class CubeFunctionInfo : GrapeCity.CalcEngine.Functions.CalcFunction
        {
            public override string Name { get { return "CUBE"; } }
            public override int MinArgs { get { return 1; } }
            public override int MaxArgs { get { return 1; } }
            public override object Evaluate(object[] args)
            {
                double num = GrapeCity.CalcEngine.CalcConvert.ToDouble(args[0]);
                return num * num * num;
            }
            public override object Evaluate(object[] args, object context)
            {
                return Evaluate(args, context);
            }
        }
       
        public MainPage()
        {
            this.InitializeComponent();
            gcSpreadSheet1.AddCustomFunction(new CubeFunctionInfo());
            gcSpreadSheet1.Sheets[0].SetFormula(1, 1, "CUBE(4)");
        }

Public Class CubeFunctionInfo
        Inherits GrapeCity.CalcEngine.Functions.CalcFunction
        Public Overrides ReadOnly Property Name() As String
            Get
                Return "CUBE"
            End Get
        End Property
        Public Overrides ReadOnly Property MinArgs() As Integer
            Get
                Return 1
            End Get
        End Property
        Public Overrides ReadOnly Property MaxArgs() As Integer
            Get
                Return 1
            End Get
        End Property
        Public Overrides Function Evaluate(args As Object()) As Object
            Dim num As Double = GrapeCity.CalcEngine.CalcConvert.ToDouble(args(0))
            Return num * num * num
        End Function
        Public Overrides Function Evaluate(args As Object(), context As Object) As Object
            Return Evaluate(args, context)
        End Function
    End Class   
    Private Sub MainPage_Loaded(sender As Object, e As RoutedEventArgs) Handles Me.Loaded
        gcSpreadSheet1.AddCustomFunction(New CubeFunctionInfo())
        gcSpreadSheet1.Sheets(0).SetFormula(1, 1, "CUBE(4)")      
    End Sub

Copyright © GrapeCity, inc. All rights reserved.