Free Trial
Web API version
Licensing
Request A Quote
HAVE QUESTIONS OR NEED HELP? SUBMIT THE SUPPORT REQUEST FORM or write email to SUPPORT@BYTESCOUT.COM
Set Custom Functions | VB.NET
Module1.vb:
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