Link Search Menu Expand Document

Set Custom Functions - VB.NET

Spreadsheet SDK sample in VB.NET demonstrating ‘Set Custom Functions’

Module1.vb
Imports Bytescout.Spreadsheet

Module Module1

    ' This example demonstrates the calculation of custom functions.
    Sub Main()

        ' Create Spreadsheet instance

        Dim spreadsheet As New Spreadsheet()
        spreadsheet.RegistrationName = "demo"
        spreadsheet.RegistrationKey = "demo"

        ' Add custom formula handler
        spreadsheet.CustomFunctionsCallback = AddressOf MyFunctions

        ' Load document
        spreadsheet.LoadFromFile("CustomFuncExample.xlsx")

        ' Calculate the first worksheet
        Dim worksheet = spreadsheet.Workbook.Worksheets(0)
        worksheet.Calculate()

        ' Save calculated values to neighbor cells to demonstrate custom functions are calculated
        worksheet("C2").Value = worksheet("B2").Value
        worksheet("C3").Value = worksheet("B3").Value
        worksheet("C4").Value = worksheet("B4").Value

        ' Save modified spreadsheet
        spreadsheet.SaveAs("result.xlsx")

        ' Cleanup
        spreadsheet.Dispose()

        ' Open saved spreadsheet in associated application (for demo purpose)
        Process.Start("result.xlsx")

    End Sub

    Private Function MyFunctions(ByVal sender As Worksheet, ByVal funcName As String, ByVal arguments As Object(), ByRef handled As Boolean) As Object

        ' Handle "CUSTOMFUNC_FACTORIAL" function
        If String.Compare(funcName, "CUSTOMFUNC_FACTORIAL", StringComparison.OrdinalIgnoreCase) = 0 Then

            handled = True

            ' Compute factorial
            If arguments.Length > 0 Then
                Dim value As Integer = arguments(0)

                If value = 0 Or value = 1 Then
                    Return 1
                End If

                Dim f As Integer = 1
                For i As Integer = 1 To value
                    f = f * i
                Next

                Return f
            End If

            Return Nothing

        End If

        ' Handle "CUSTOMFUNC_SUM" function
        If String.Compare(funcName, "CUSTOMFUNC_SUM", StringComparison.OrdinalIgnoreCase) = 0 Then

            handled = True

            ' Compute the sum of values
            If arguments.Length > 0 Then

                Dim sum As Double

                For Each o As Object In arguments
                    sum = sum + o
                Next

                Return sum

            End If

            Return Nothing

        End If

        Return Nothing

    End Function

End Module

Resources.Designer.vb
��'------------------------------------------------------------------------------

' <auto-generated>

'     This code was generated by a tool.

'     Runtime Version:2.0.50727.1433

'

'     Changes to this file may cause incorrect behavior and will be lost if

'     the code is regenerated.

' </auto-generated>

'------------------------------------------------------------------------------



Option Strict On

Option Explicit On





Namespace My.Resources

    

    'This class was auto-generated by the StronglyTypedResourceBuilder

    'class via a tool like ResGen or Visual Studio.

    'To add or remove a member, edit your .ResX file then rerun ResGen

    'with the /str option, or rebuild your VS project.

    '<summary>

    '  A strongly-typed resource class, for looking up localized strings, etc.

    '</summary>

    <Global.System.CodeDom.Compiler.GeneratedCodeAttribute("System.Resources.Tools.StronglyTypedResourceBuilder", "2.0.0.0"),  _

     Global.System.Diagnostics.DebuggerNonUserCodeAttribute(),  _

     Global.System.Runtime.CompilerServices.CompilerGeneratedAttribute(),  _

     Global.Microsoft.VisualBasic.HideModuleNameAttribute()>  _

    Friend Module Resources

        

        Private resourceMan As Global.System.Resources.ResourceManager

        

        Private resourceCulture As Global.System.Globalization.CultureInfo

        

        '<summary>

        '  Returns the cached ResourceManager instance used by this class.

        '</summary>

        <Global.System.ComponentModel.EditorBrowsableAttribute(Global.System.ComponentModel.EditorBrowsableState.Advanced)>  _

        Friend ReadOnly Property ResourceManager() As Global.System.Resources.ResourceManager

            Get

                If Object.ReferenceEquals(resourceMan, Nothing) Then

                    Dim temp As Global.System.Resources.ResourceManager = New Global.System.Resources.ResourceManager("CustomFunctions.Resources", GetType(Resources).Assembly)

                    resourceMan = temp

                End If

                Return resourceMan

            End Get

        End Property

        

        '<summary>

        '  Overrides the current thread's CurrentUICulture property for all

        '  resource lookups using this strongly typed resource class.

        '</summary>

        <Global.System.ComponentModel.EditorBrowsableAttribute(Global.System.ComponentModel.EditorBrowsableState.Advanced)>  _

        Friend Property Culture() As Global.System.Globalization.CultureInfo

            Get

                Return resourceCulture

            End Get

            Set(ByVal value As Global.System.Globalization.CultureInfo)

                resourceCulture = value

            End Set

        End Property

    End Module

End Namespace


Settings.Designer.vb
��'------------------------------------------------------------------------------

' <auto-generated>

'     This code was generated by a tool.

'     Runtime Version:2.0.50727.1433

'

'     Changes to this file may cause incorrect behavior and will be lost if

'     the code is regenerated.

' </auto-generated>

'------------------------------------------------------------------------------



Option Strict On

Option Explicit On





Namespace My



    <Global.System.Runtime.CompilerServices.CompilerGeneratedAttribute(),  _

     Global.System.CodeDom.Compiler.GeneratedCodeAttribute("Microsoft.VisualStudio.Editors.SettingsDesigner.SettingsSingleFileGenerator", "8.0.0.0"),  _

     Global.System.ComponentModel.EditorBrowsableAttribute(Global.System.ComponentModel.EditorBrowsableState.Advanced)>  _

    Partial Friend NotInheritable Class MySettings

        Inherits Global.System.Configuration.ApplicationSettingsBase

        

        Private Shared defaultInstance As MySettings = CType(Global.System.Configuration.ApplicationSettingsBase.Synchronized(New MySettings), MySettings)

        

#Region "My.Settings Auto-Save Functionality"

#If _MyType = "WindowsForms" Then

        Private Shared addedHandler As Boolean



        Private Shared addedHandlerLockObject As New Object



        <Global.System.Diagnostics.DebuggerNonUserCodeAttribute(), Global.System.ComponentModel.EditorBrowsableAttribute(Global.System.ComponentModel.EditorBrowsableState.Advanced)> _

        Private Shared Sub AutoSaveSettings(ByVal sender As Global.System.Object, ByVal e As Global.System.EventArgs)

            If My.Application.SaveMySettingsOnExit Then

                My.Settings.Save()

            End If

        End Sub

#End If

#End Region

        

        Public Shared ReadOnly Property [Default]() As MySettings

            Get

                

#If _MyType = "WindowsForms" Then

                   If Not addedHandler Then

                        SyncLock addedHandlerLockObject

                            If Not addedHandler Then

                                AddHandler My.Application.Shutdown, AddressOf AutoSaveSettings

                                addedHandler = True

                            End If

                        End SyncLock

                    End If

#End If

                Return defaultInstance

            End Get

        End Property

    End Class

End Namespace



Namespace My

    

    <Global.Microsoft.VisualBasic.HideModuleNameAttribute(),  _

     Global.System.Diagnostics.DebuggerNonUserCodeAttribute(),  _

     Global.System.Runtime.CompilerServices.CompilerGeneratedAttribute()>  _

    Friend Module MySettingsProperty

        

        <Global.System.ComponentModel.Design.HelpKeywordAttribute("My.Settings")>  _

        Friend ReadOnly Property Settings() As Global.CustomFunctions.My.MySettings

            Get

                Return Global.CustomFunctions.My.MySettings.Default

            End Get

        End Property

    End Module

End Namespace


Download Source Code (.zip)

Return to the previous page Explore Spreadsheet SDK


Copyright © 2016 - 2021 ByteScout