Imports System
Imports SpreadsheetGear
 
Namespace ConsoleApplication
    _
    '/ <summary>
    '/ Demonstrate a custom function which sums the range of cells or values passed to it.
    '/ </summary>
    Class Program
 
        'Entry point which delegates to C-style main Private Function
        Public Overloads Shared Sub Main()
            ' Create a new empty workbook.
            Dim workbookSet As IWorkbookSet = Factory.GetWorkbookSet()
 
            ' Add the custom function to the workbook set. 
            workbookSet.Add(MySum.MySumSingleton)
 
            ' Add a workbook to the workbook set.
            Dim workbook As IWorkbook = workbookSet.Workbooks.Add()
 
            ' Get the first sheet and it's cells
            Dim worksheet As SpreadsheetGear.IWorksheet = workbook.Worksheets(0)
            Dim cells As SpreadsheetGear.IRange = worksheet.Cells
 
            ' Assign values and formulas to cells.
            cells("A1").Value = 1
            ' The OFFSET(...) function enables creation of a range equal to 
            ' the number of rows which have values in column A and one column.
            cells("B1").Formula = "=MYSUM(OFFSET(A1,0,0,COUNT(A:A),1))"
 
            Console.WriteLine(("Result calculated from MYSUM  =  " + cells("B1").Text + " (should be 1)."))
 
            ' Add another value to column A.
            cells("A2").Value = 2
 
            Console.WriteLine(("Result calculated from MYSUM  =  " + cells("B1").Text + " (should be 3)."))
        End Sub 'Main
    End Class 'Program
    _
 
    ' A simple SUM custom function. 
    Public Class MySum
        Inherits SpreadsheetGear.CustomFunctions.Function
        ' Set to the one and only instance of MySum. 
        Public Shared MySumSingleton As New MySum()
 
 
        ' Sum the numbers in a range or array. 
        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 = 1 Then
                Dim rowCount As Integer
                Dim colCount As Integer
                arguments.GetArrayDimensions(0, rowCount, colCount)
                Dim numbers(rowCount, colCount) As Double
                Dim row As Integer
                For row = 0 To rowCount - 1
                    Dim col As Integer
                    For col = 0 To colCount - 1
                        Dim value As SpreadsheetGear.CustomFunctions.IValue = arguments.GetArrayValue(0, row, col)
                        If value.Type = SpreadsheetGear.CustomFunctions.ValueType.Number Then
                            numbers(row, col) = value.Number
                        End If
                    Next col
                Next row ' Get the two arguments as numbers, and sum them. 
                result.Number = SumThem(numbers)
                ' Return ValueError.Value. 
            Else
                result.Error = SpreadsheetGear.ValueError.Value
            End If
        End Sub 'Evaluate
 
        Private Function SumThem(ByVal numbers(,) As Double) As Double
            Dim sum As Double = 0.0
            Dim rowCount As Integer = numbers.GetUpperBound(0) + 1
            Dim colCount As Integer = numbers.GetUpperBound(1) + 1
            Dim row As Integer
            For row = 0 To rowCount - 1
                Dim col As Integer
                For col = 0 To colCount - 1
                    sum += numbers(row, col)
                Next col
            Next row
            Return sum
        End Function 'SumThem
 
 
        ' 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 'New ' For a given set of inputs, this function always returns the same value. 
    End Class 'MySum
End Namespace 'ConsoleApplication ' This function returns a number.