# Creating and Using a Custom Function

Learn how to create and use custom functions in spreadsheets with examples in C# and discover the benefits of defining your own functions for specific tasks.

## Content

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](/spreadnet/api/latest/online-win/GrapeCity.CalcEngine/GrapeCity.CalcEngine.Function.MinArgs.html) property and [MaxArgs](/spreadnet/api/latest/online-win/GrapeCity.CalcEngine/GrapeCity.CalcEngine.Function.MaxArgs.html) property of the function, then the [Evaluate](/spreadnet/api/latest/online-win/FarPoint.CalcEngine/FarPoint.CalcEngine.FunctionInfo.Evaluate.html) 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()](/spreadnet/api/latest/online-win/GrapeCity.CalcEngine/GrapeCity.CalcEngine.IReadonlyPrimitiveValue.GetError.html) method of the [IReadonlyPrimitiveValue](/spreadnet/api/latest/online-win/GrapeCity.CalcEngine/GrapeCity.CalcEngine.IReadonlyPrimitiveValue.html) interface returns False for that parameter, then the **Evaluate()** method of the [Function](/spreadnet/api/latest/online-win/GrapeCity.CalcEngine/GrapeCity.CalcEngine.Function.html) 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.

```csharp
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));
    }
}
```

```vbnet
Public Class TaxValueFunction
    Inherits GrapeCity.CalcEngine.Function
    Public Sub New()
        MyBase.New("TAXVALUE", 1, 2, FunctionAttributes.SingleCell Or FunctionAttributes.Number)
    End Sub
    Protected Overrides Sub Evaluate(arguments As IArguments, result As IValue)
        Dim context As IEvaluationContext = arguments.EvaluationContext
        Dim num As Double = arguments(0).GetNumber(context)
        Dim taxrate As Double
        If arguments.Count > 1 Then
            taxrate = arguments(1).GetNumber()
        Else
            taxrate = 0.15
        End If
        result.SetValue(Nothing, num - (num * taxrate))
    End Sub
End Class
```

The following code registers the custom functions.

```csharp
fpSpread1.AddCustomFunction(new TaxValueFunction());
```

```vbnet
fpSpread1.AddCustomFunction(New TaxValueFunction())
```

The following code implements the custom functions in formulas.

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

```vbnet
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](/spreadnet/api/latest/online-win/GrapeCity.CalcEngine/GrapeCity.CalcEngine.Function.html) class from within a derived class.
The [GetValue()](/spreadnet/api/latest/online-win/GrapeCity.CalcEngine/GrapeCity.CalcEngine.IReferenceSource.GetValue.html) method of the [IReferenceSource](/spreadnet/api/latest/online-win/GrapeCity.CalcEngine/GrapeCity.CalcEngine.IReferenceSource.html) interface and the [SetValue()](/spreadnet/api/latest/online-win/GrapeCity.CalcEngine/GrapeCity.CalcEngine.IPrimitiveValue.SetValue.html) method of the [IPrimitiveValue](/spreadnet/api/latest/online-win/GrapeCity.CalcEngine/GrapeCity.CalcEngine.IPrimitiveValue.html) 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.

```csharp
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);
        }
    }
}
```

```vbnet
Public Class CountIfLessThanFunction
    Inherits GrapeCity.CalcEngine.Function
    Public Sub New()
        MyBase.New("COUNTIFLESSTHAN", 2, 2, GrapeCity.CalcEngine.FunctionAttributes.Number)
    End Sub
    Protected Overrides Sub Evaluate(arguments As IArguments, result As IValue)
        Dim range As IValue = arguments(0)
        If range.ValueType <> GrapeCity.CalcEngine.ValueType.Reference Then
            arguments.EvaluationContext.Error = CalcError.Value
            result.SetValue(CalcError.Value)
        Else
            Dim evaluationContext As IEvaluationContext = arguments.EvaluationContext
            Dim criteria As Double = arguments(1).GetNumber(evaluationContext)
            Dim referenceSource As IReferenceSource = range.GetReferenceSource(evaluationContext)
            Dim rangeRef As RangeReference = range.GetReference(evaluationContext, 0)
            Dim count As Integer = 0
            For c As Integer = rangeRef.Column To rangeRef.Column2
                For r As Integer = rangeRef.Row To rangeRef.Row2
                    referenceSource.GetValue(evaluationContext, r, c, result)
                    Dim cellValue As Double = result.GetNumber(evaluationContext)
                    If cellValue < criteria Then
                        count += 1
                    End If
                Next
            Next
            result.SetValue(evaluationContext, count)
        End If
    End Sub
End Class
```

## See Also

[Formulas in Cells](/spreadnet/docs/latest/online-win/overview/spwin-devguide/spwin-cell-formula)
[Placing a Formula in Cells](/spreadnet/docs/latest/online-win/overview/spwin-devguide/spwin-cell-formula/spwin-formulaplace)
[Specifying a Cell Reference in a Formula](/spreadnet/docs/latest/online-win/overview/spwin-devguide/spwin-cell-formula/spwin-formulacellref)
[Specifying a Sheet Reference in a Formula](/spreadnet/docs/latest/online-win/overview/spwin-devguide/spwin-cell-formula/spwin-formulasheetref)
[Specifying an External Reference in a Formula](/spreadnet/docs/latest/online-win/overview/spwin-devguide/spwin-cell-formula/spwin-externalref)
[Using a Circular Reference in a Formula](/spreadnet/docs/latest/online-win/overview/spwin-devguide/spwin-cell-formula/spwin-formulacircref)
[Nesting Functions in a Formula](/spreadnet/docs/latest/online-win/overview/spwin-devguide/spwin-cell-formula/spwin-formulanested)
[Recalculating and Updating Formulas Automatically](/spreadnet/docs/latest/online-win/overview/spwin-devguide/spwin-cell-formula/spwin-formularecalc)
[Finding a Value Using GoalSeek](/spreadnet/docs/latest/online-win/overview/spwin-devguide/spwin-cell-formula/spwin-formula-goalseek)
[Allowing the User to Enter Formulas](/spreadnet/docs/latest/online-win/overview/spwin-devguide/spwin-cell-formula/spwin-formulaallowuser)
[Creating and Using a Custom Name](/spreadnet/docs/latest/online-win/overview/spwin-devguide/spwin-cell-formula/spwin-formulacustomname)
[Creating and Using External Variable](/spreadnet/docs/latest/online-win/overview/spwin-devguide/spwin-cell-formula/spwin-external-variable)
[Using the Array Formula](/spreadnet/docs/latest/online-win/overview/spwin-devguide/spwin-cell-formula/spwin-arrayformula)
[Working with the Formula Text Box](/spreadnet/docs/latest/online-win/overview/spwin-devguide/spwin-cell-formula/spwin-formulabar)
[Setting up the Name Box](/spreadnet/docs/latest/online-win/overview/spwin-devguide/spwin-cell-formula/spwin-namebox)
[Using Language Package](/spreadnet/docs/latest/online-win/overview/spwin-devguide/spwin-cell-formula/UsingLanguagePackage)
[Accessing Data from Header or Footer](/spreadnet/docs/latest/online-win/overview/spwin-devguide/spwin-cell-formula/spwin-headerfooterformula)
[Managing External Reference](/spreadnet/docs/latest/online-win/overview/spwin-devguide/spwin-cell-formula/spwin-extrenalreference)
[Working With Dynamic Array Formulas](/spreadnet/docs/latest/online-win/overview/spwin-devguide/spwin-cell-formula/WorkingWithDynamicArrayFormulas)