Spread WPF Documentation
Spread WPF Documentation / Developer's Guide / Managing the User Interface / Using Formulas / Creating Custom Formulas
In This Topic
    Creating Custom Formulas
    In This Topic

    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.

    Using Code

    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.

    CS
    Copy Code
    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, bool arrayformulamode)
               
            {
                double num = GrapeCity.CalcEngine.CalcConvert.ToDouble(args[0]);
                return num * num * num;
            }
            public override object Evaluate(object[] args, object context, bool arrayformulamode)
        {
            return Evaluate(args, arrayformulamode);
        }
        }
    private void button1_Click(object sender, RoutedEventArgs e)
            {
                gcSpreadSheet1.AddCustomFunction(new CubeFunctionInfo());          
                gcSpreadSheet1.Sheets[0].SetFormula(1, 1, "CUBE(4)");          
            }
    
    VB.NET
    Copy Code
    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(), Optional arrayformulamode As Boolean = False) 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, Optional arrayformulamode As Boolean = False) As Object
                Return Evaluate(args, arrayformulamode)
            End Function
        End Class
    Private Sub Button1_Click(sender As System.Object, e As System.Windows.RoutedEventArgs) Handles Button1.Click
            GcSpreadSheet1.AddCustomFunction(New CubeFunctionInfo())
            GcSpreadSheet1.Sheets(0).SetFormula(1, 1, "CUBE(4)") 
        End Sub
    
    See Also