SpreadsheetGear 2023
Function Class
Members  Example 


SpreadsheetGear.CustomFunctions Namespace : Function Class
Override this class to implement a SpreadsheetGear custom function.
Object Model
Function Class
Syntax
'Declaration
 
Public MustInherit Class Function 
'Usage
 
Dim instance As Function
public abstract class Function 
Remarks

Function.Evaluate must not use any API involving the workbook set containing the instance of the custom function being evaluated. However, custom functions may use one or more separate workbook sets without limitation.

Create a default custom function by specifying "*" as the name parameter to Function.Function Constructor. Use IArguments.CurrentFunctionName to retrieve the name of the function being evaluated.

Example
class Program
{
    // Demonstrate the use of custom functions in SpreadsheetGear.
    static void Main()
    {
        // Create a new empty workbook.
        SpreadsheetGear.IWorkbook workbook = SpreadsheetGear.Factory.GetWorkbook();
        // Add the custom functions to the workbook set.
        workbook.WorkbookSet.Add(MyAdd.MyAddSingleton);
        workbook.WorkbookSet.Add(MySum.MySumSingleton);
        // Get Sheet1 and its cells.
        SpreadsheetGear.IWorksheet sheet1 = workbook.Worksheets[0];
        SpreadsheetGear.IRange cells = sheet1.Cells;
        // Assign values and formulas to cells.
        cells["A1"].Value = 1.1;
        cells["B1"].Value = 1.2;
        cells["C1"].Formula = "=MYADD(A1,B1)";
        cells["A2"].Value = 2.1;
        cells["B2"].Value = 2.2;
        cells["C2"].Formula = "=MYADD(A2,B2)";
        cells["C3"].Formula = "=MYSUM(C1:C2)";
        // Output the result of the custom functions.
        System.Console.WriteLine("1.1+1.2+2.1+2.2={0}", cells["C3"].Text);
    }
 
    // A simple addition custom function.
    public class MyAdd : SpreadsheetGear.CustomFunctions.Function
    {
        // Set to the one and only instance of MyAdd.
        public static readonly MyAdd MyAddSingleton = new MyAdd();
 
        // Add two numbers.
        public override void Evaluate(
            SpreadsheetGear.CustomFunctions.IArguments arguments,
            SpreadsheetGear.CustomFunctions.IValue result)
        {
            // Verify that there are two arguments.
            if (arguments.Count == 2)
                // Get the two arguments as numbers, and add them.
                result.Number = arguments.GetNumber(0) + arguments.GetNumber(1);
            else
                // Return ValueError.Value.
                result.Error = SpreadsheetGear.ValueError.Value;
        }
 
        // Singleton class - so make the constructor private.
        private MyAdd()
            : base(
            // The name of the custom function.
            "MYADD",
            // For a given set of inputs, this function always returns the same value.
            SpreadsheetGear.CustomFunctions.Volatility.Invariant,
            // This function returns a number.
            SpreadsheetGear.CustomFunctions.ValueType.Number)
        {
        }
    }
 
    // A custom function which sums any number of simple or complex values.
    public class MySum : SpreadsheetGear.CustomFunctions.Function
    {
        // Set to the one and only instance of MySum.
        public static readonly MySum MySumSingleton = new MySum();
 
        // Sums any number of values or ranges.
        public override void Evaluate(
            SpreadsheetGear.CustomFunctions.IArguments arguments,
            SpreadsheetGear.CustomFunctions.IValue result)
        {
            // Initialize the sum to 0.
            double sum = 0.0;
            // for each argument.
            for (int i = 0; i < arguments.Count; i++)
            {
                int rows;
                int cols;
                // Get the dimensions (returns 1x1 for simple values).
                arguments.GetArrayDimensions(i, out rows, out cols);
                // for each row.
                for (int row = 0; row < rows; row++)
                {
                    // for each column.
                    for (int col = 0; col < cols; col++)
                    {
                        // Get the value.
                        SpreadsheetGear.CustomFunctions.IValue val = arguments.GetArrayValue(i, row, col);
                        // Is it a number?
                        if (val.IsNumber)
                            // Add it to the sum.
                            sum += val.Number;
                            // Is it an error?
                        else if (val.IsError)
                        {
                            // Return an error immediately.
                            result.Error = val.Error;
                            return;
                        }
                    }
                }
            }
            // Return the sum.
            result.Number = sum;
        }
 
        // Singleton class - so make the constructor private.
        private MySum()
            // The name of the custom function.
            : base("MYSUM",
            // For a given set of inputs, this function always returns the same value.
            SpreadsheetGear.CustomFunctions.Volatility.Invariant,
            // This function returns a number.
            SpreadsheetGear.CustomFunctions.ValueType.Number)
        {
        }
    }
}
Class Program
    ' Demonstrate the use of custom functions in SpreadsheetGear.
    Shared Sub Main()
        ' Create a new empty workbook.
        Dim workbook As SpreadsheetGear.IWorkbook = SpreadsheetGear.Factory.GetWorkbook()
        ' Add the custom functions to the workbook set.
        workbook.WorkbookSet.Add(MyAdd.MyAddSingleton)
        workbook.WorkbookSet.Add(MySum.MySumSingleton)
        ' Get Sheet1 and its cells.
        Dim sheet1 As SpreadsheetGear.IWorksheet = workbook.Worksheets(0)
        Dim cells As SpreadsheetGear.IRange = sheet1.Cells
        ' Assign values and formulas to cells.
        cells("A1").Value = 1.1
        cells("B1").Value = 1.2
        cells("C1").Formula = "=MYADD(A1,B1)"
        cells("A2").Value = 2.1
        cells("B2").Value = 2.2
        cells("C2").Formula = "=MYADD(A2,B2)"
        cells("C3").Formula = "=MYSUM(C1:C2)"
        ' Output the result of the custom functions.
        System.Console.WriteLine("1.1+1.2+2.1+2.2={0}", cells("C3").Text)
    End Sub 'Main
 
    ' A simple addition custom function.
    Public Class MyAdd
        Inherits SpreadsheetGear.CustomFunctions.Function
        ' Set to the one and only instance of MyAdd.
        Public Shared MyAddSingleton As New MyAdd
        ' Add two numbers.
        Public Overrides Sub Evaluate(ByVal arguments As SpreadsheetGear.CustomFunctions.IArguments, ByVal result As SpreadsheetGear.CustomFunctions.IValue)
            ' Verify that there are two arguments.
            If arguments.Count = 2 Then
                ' Get the two arguments as numbers, and add them.
                result.Number = arguments.GetNumber(0) + arguments.GetNumber(1)
            Else
                ' Return ValueError.Value.
                result.Error = SpreadsheetGear.ValueError.Value
            End If
        End Sub 'Evaluate
 
        ' Singleton class - so make the constructor private.
        Private Sub New()
            ' The name of the custom function.
            MyBase.New("MYADD", SpreadsheetGear.CustomFunctions.Volatility.Invariant, SpreadsheetGear.CustomFunctions.ValueType.Number)
        End Sub 'New
    End Class 'MyAdd
 
    ' A custom function which sums any number of simple or complex values.
    Public Class MySum
        Inherits SpreadsheetGear.CustomFunctions.Function
        ' Set to the one and only instance of MySum.
        Public Shared MySumSingleton As New MySum
 
 
        ' Sums any number of values or ranges.
        Public Overrides Sub Evaluate(ByVal arguments As SpreadsheetGear.CustomFunctions.IArguments, ByVal result As SpreadsheetGear.CustomFunctions.IValue)
            ' Initialize the sum to 0.
            Dim sum As Double = 0.0
            ' for each argument.
            Dim i As Integer
            For i = 0 To arguments.Count - 1
                Dim rows As Integer
                Dim cols As Integer
                ' Get the dimensions (returns 1x1 for simple values).
                arguments.GetArrayDimensions(i, rows, cols)
                ' for each row.
                Dim row As Integer
                For row = 0 To rows - 1
                    ' for each column.
                    Dim col As Integer
                    For col = 0 To cols - 1
                        ' Get the value.
                        Dim val As SpreadsheetGear.CustomFunctions.IValue = arguments.GetArrayValue(i, row, col)
                        ' Is it a number?
                        If val.IsNumber Then
                            ' Add it to the sum.
                            sum += val.Number
                            ' Is it an error?
                        Else
                            If val.IsError Then
                                ' Return an error immediately.
                                result.Error = val.Error
                                Return
                            End If
                        End If
                    Next col
                Next row
            Next i
            ' Return the sum.
            result.Number = sum
        End Sub 'Evaluate
 
        ' Singleton class - so make the constructor private.
        Private Sub New()
            ' The name of the custom function.
            MyBase.New("MYSUM", SpreadsheetGear.CustomFunctions.Volatility.Invariant, SpreadsheetGear.CustomFunctions.ValueType.Number)
        End Sub
    End Class 'MySum
End Class 'Program
Inheritance Hierarchy

System.Object
   SpreadsheetGear.CustomFunctions.Function

Requirements

Target Platforms: Windows 7, Windows Vista SP1 or later, Windows XP SP3, Windows Server 2008 (Server Core not supported), Windows Server 2008 R2 (Server Core supported with SP1 or later), Windows Server 2003 SP2

See Also

Reference

Function Members
SpreadsheetGear.CustomFunctions Namespace