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 in the same way as you would call any of the built-in functions.

A custom function can have the same name as a built-in function. The custom function takes priority over the built-in function. Custom functions are dynamically linked at evaluation time. Thus, the application can redefine an existing built-in function, if the custom function uses the same name and is added before the formula is parsed.

If a formula attempts to call a custom function with a parameter count outside of the range indicated by the MinArgs property and MaxArgs property 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 GetError() method of the IReadonlyPrimitiveValue interface returns False for that parameter, then the Evaluate() method of the Function class is skipped and the error value is used as the result.

The Evaluate() method evaluates the custom function based on the specified arguments and assigns the evaluated value to the result.

Using Code

1. Define the custom function(s).
2. Register the function(s) in the sheet.
3. Use the custom function(s).

Example

The first step is to create the custom functions. In this example, we will create a Tax Value function that evaluates the tax and returns a numeric value in the specified cells in the spreadsheet.

The following code defines the Tax Value custom function.

public class TaxValueFunction : GrapeCity.CalcEngine.Function
{
    public TaxValueFunction() : base("TAXVALUE", 1, 2, FunctionAttributes.SingleCell | FunctionAttributes.Number) { }
    protected override void Evaluate(IArguments arguments, IValue result)
    {
        IEvaluationContext context = arguments.EvaluationContext;
        double num = arguments[0].GetNumber(context);
        double taxrate = arguments.Count > 1 ? arguments[1].GetNumber() : 0.15;
        result.SetValue(null, num - (num * taxrate));
    }
}

The following code registers the custom functions.

fpSpread1.AddCustomFunction(new TaxValueFunction());

The following code implements the custom functions in formulas.

fpSpread1.ActiveSheet.Cells[1,1].Formula = "TAXVALUE(A1)";

Parameters in Custom Functions

There are two ways to specify arguments in custom functions- passing parameters by value and passing parameters by reference.

By default, parameters are passed by value (if you're using a single cell). A single empty cell is passed as null (Nothing in Visual Basic). A single non-empty cell is passed as a boxed primitive (for example, double, boolean, string, and so on).

If you're using a cell range, parameters are passed by reference.

To work with parameters in custom functions, you can access the methods and properties of the Function class from within a derived class.

The GetValue() method of the IReferenceSource interface and the SetValue() method of the IPrimitiveValue interface can be used to get or set a single value from the reference. The row and the column indexes for the GetValue method and the SetValue method start at the row and the column.

Example

In this example, a function counts the number of cells in a range that are less than a given criteria.

class CountIfLessThanFunction : GrapeCity.CalcEngine.Function
{
    public CountIfLessThanFunction() : base("COUNTIFLESSTHAN", 2, 2, GrapeCity.CalcEngine.FunctionAttributes.Number) { }
    protected override void Evaluate(IArguments arguments, IValue result)
    {
        IValue range = arguments[0];
        if (range.ValueType != GrapeCity.CalcEngine.ValueType.Reference)
        {
            arguments.EvaluationContext.Error = CalcError.Value;
            result.SetValue(CalcError.Value);
        }
        else
        {
            IEvaluationContext evaluationContext = arguments.EvaluationContext;
            double criteria = arguments[1].GetNumber(evaluationContext);
            IReferenceSource referenceSource = range.GetReferenceSource(evaluationContext);
            RangeReference rangeRef = range.GetReference(evaluationContext, 0);
            int count = 0;
            for (int c = rangeRef.Column; c <= rangeRef.Column2; c++)
            {
                for (int r = rangeRef.Row; r <= rangeRef.Row2; r++)
                {
                    referenceSource.GetValue(evaluationContext, r, c, result);
                    double cellValue = result.GetNumber(evaluationContext);
                    if (cellValue < criteria)
                    {
                        count++;
                    }
                }
            }
            result.SetValue(evaluationContext, count);
        }
    }
}