Table of Contents

Excel user defined functions (UDF) (VB.Net / netframework)

Note

This demo is available in your FlexCel installation at <FlexCel Install Folder>\samples\vb\VS2022\netframework\10.API\78.Excel User Defined Functions and also at https:​//​github.​com/​tmssoftware/​TMS-​FlexCel.​NET-​demos/​tree/​master/​vb/​VS2022/​netframework/​Modules/​10.​API/​78.​Excel User Defined Functions

Overview

Here we will explore how to handle Excel files with UDFs. FlexCel has full support for adding Excel UDFs to cells, retrieving UDFs from cells or recalculating files containing UDFs. But you need to create .NET functions that will mimic the UDF behavior, and add them to the FlexCel recalculation engine.

Make sure you read Using Excel's User-defined Functions (UDF) in the API developers guide for a conceptual explanation of what we are doing here.

Concepts

  • How to recalculate a sheet containing User Defined Functions (UDfs).

  • How to read and write UDFs from and to an Excel file.

  • In order to compare the results calculated by Excel and By FlexCel, this demo will save two files: one pdf (that will not use Excel recalculation) and one xls (that will be recalculated by Excel when opened).

Files

AssemblyInfo.vb

Imports System.Reflection
Imports System.Runtime.CompilerServices

'
' General Information about an assembly is controlled through the following 
' set of attributes. Change these attribute values to modify the information
' associated with an assembly.
'
<Assembly: AssemblyTitle("")>
<Assembly: AssemblyDescription("")>
<Assembly: AssemblyConfiguration("")>
<Assembly: AssemblyCompany("")>
<Assembly: AssemblyProduct("")>
<Assembly: AssemblyCopyright("(c) 2002 - 2014 TMS Software")>
<Assembly: AssemblyTrademark("")>
<Assembly: AssemblyCulture("")>

'
' Version information for an assembly consists of the following four values:
'
'      Major Version
'      Minor Version 
'      Build Number
'      Revision
'
' You can specify all the values or you can default the Revision and Build Numbers 
' by using the '*' as shown below:

<Assembly: AssemblyVersion("6.2.1.0")>

'
' In order to sign your assembly you must specify a key to use. Refer to the 
' Microsoft .NET Framework documentation for more information on assembly signing.
'
' Use the attributes below to control which key is used for signing. 
'
' Notes: 
'   (*) If no key is specified, the assembly is not signed.
'   (*) KeyName refers to a key that has been installed in the Crypto Service
'       Provider (CSP) on your machine. KeyFile refers to a file which contains
'       a key.
'   (*) If the KeyFile and the KeyName values are both specified, the 
'       following processing occurs:
'       (1) If the KeyName can be found in the CSP, that key is used.
'       (2) If the KeyName does not exist and the KeyFile does exist, the key 
'           in the KeyFile is installed into the CSP and used.
'   (*) In order to create a KeyFile, you can use the sn.exe (Strong Name) utility.
'       When specifying the KeyFile, the location of the KeyFile should be
'       relative to the project output directory which is
'       %Project Directory%\obj\<configuration>. For example, if your KeyFile is
'       located in the project directory, you would specify the AssemblyKeyFile 
'       attribute as [assembly: AssemblyKeyFile("..\\..\\mykey.snk")]
'   (*) Delay Signing is an advanced option - see the Microsoft .NET Framework
'       documentation for more information on this.
'
<Assembly: AssemblyDelaySign(False)>
<Assembly: AssemblyKeyFile("")>
<Assembly: AssemblyKeyName("")>

Form1.Designer.vb

Imports System.Collections
Imports System.ComponentModel
Imports FlexCel.Core
Imports FlexCel.XlsAdapter
Imports System.IO
Imports System.Reflection
Imports System.Text
Imports FlexCel.Render
Namespace ExcelUserDefinedFunctions
	Partial Public Class mainForm
		Inherits System.Windows.Forms.Form

		''' <summary>
		''' Required designer variable.
		''' </summary>
		Private components As System.ComponentModel.Container = Nothing

		''' <summary>
		''' Clean up any resources being used.
		''' </summary>
		Protected Overrides Sub Dispose(ByVal disposing As Boolean)
			If disposing Then
				If components IsNot Nothing Then
					components.Dispose()
				End If
			End If
			MyBase.Dispose(disposing)
		End Sub

		#Region "Windows Form Designer generated code"
		''' <summary>
		''' Required method for Designer support - do not modify
		''' the contents of this method with the code editor.
		''' </summary>
		Private Sub InitializeComponent()
			Me.button1 = New System.Windows.Forms.Button()
			Me.saveFileDialog1 = New System.Windows.Forms.SaveFileDialog()
			Me.label1 = New System.Windows.Forms.Label()
			Me.SuspendLayout()
			' 
			' button1
			' 
			Me.button1.Anchor = System.Windows.Forms.AnchorStyles.Bottom
			Me.button1.Location = New System.Drawing.Point(132, 73)
			Me.button1.Name = "button1"
			Me.button1.TabIndex = 0
			Me.button1.Text = "GO!"
'			Me.button1.Click += New System.EventHandler(Me.button1_Click)
			' 
			' saveFileDialog1
			' 
			Me.saveFileDialog1.Filter = "Pdf Files|*.pdf"
			Me.saveFileDialog1.RestoreDirectory = True
			Me.saveFileDialog1.Title = "The Application will save BOTH AN XLS AND A PDF file in this folder"
			' 
			' label1
			' 
			Me.label1.Anchor = (CType((((System.Windows.Forms.AnchorStyles.Top Or System.Windows.Forms.AnchorStyles.Bottom) Or System.Windows.Forms.AnchorStyles.Left) Or System.Windows.Forms.AnchorStyles.Right), System.Windows.Forms.AnchorStyles))
			Me.label1.BackColor = System.Drawing.Color.FromArgb((CByte(255)), (CByte(255)), (CByte(192)))
			Me.label1.BorderStyle = System.Windows.Forms.BorderStyle.FixedSingle
			Me.label1.Location = New System.Drawing.Point(16, 16)
			Me.label1.Name = "label1"
			Me.label1.Size = New System.Drawing.Size(312, 32)
			Me.label1.TabIndex = 1
			Me.label1.Text = "A  demo on how to handle Excel UDFs with the API."
			' 
			' mainForm
			' 
			Me.AutoScaleDimensions = New System.Drawing.SizeF(6F, 13F)
			Me.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font
			Me.ClientSize = New System.Drawing.Size(336, 110)
			Me.Controls.Add(Me.label1)
			Me.Controls.Add(Me.button1)
			Me.Name = "mainForm"
			Me.Text = "Excel User Defined Functions"
			Me.ResumeLayout(False)

		End Sub
		#End Region

		Private WithEvents button1 As System.Windows.Forms.Button
		Private saveFileDialog1 As System.Windows.Forms.SaveFileDialog
		Private label1 As System.Windows.Forms.Label
	End Class
End Namespace

Form1.vb

Imports System.Collections
Imports System.ComponentModel
Imports FlexCel.Core
Imports FlexCel.XlsAdapter
Imports System.IO
Imports System.Reflection
Imports System.Text

Imports FlexCel.Render

Namespace ExcelUserDefinedFunctions
	''' <summary>
	''' An example on how to recalculate user defined functions.
	''' </summary>
	Partial Public Class mainForm
		Inherits System.Windows.Forms.Form

		Public Sub New()
			InitializeComponent()
		End Sub

		Private ReadOnly Property PathToExe() As String
			Get
				Return Path.Combine(Path.Combine(Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().Location), ".."), "..") & Path.DirectorySeparatorChar
			End Get
		End Property

		''' <summary>
		''' Loads the user defined functions into the Excel recalculating engine.
		''' </summary>
		''' <param name="Xls"></param>
		Private Sub LoadUdfs(ByVal Xls As ExcelFile)
			Xls.AddUserDefinedFunction(TUserDefinedFunctionScope.Local, TUserDefinedFunctionLocation.Internal, New SumCellsWithSameColor())
			Xls.AddUserDefinedFunction(TUserDefinedFunctionScope.Local, TUserDefinedFunctionLocation.Internal, New IsPrime())
			Xls.AddUserDefinedFunction(TUserDefinedFunctionScope.Local, TUserDefinedFunctionLocation.Internal, New BoolChoose())
			Xls.AddUserDefinedFunction(TUserDefinedFunctionScope.Local, TUserDefinedFunctionLocation.Internal, New Lowest())
		End Sub

		Private Sub AddData(ByVal Xls As ExcelFile)
			LoadUdfs(Xls) 'Register our custom functions. As we are using a local scope, we need to register them each time.

			Xls.Open(Path.Combine(PathToExe, "udfs.xls")) 'Open the file we want to manipulate.

			'Fill the cell range with other values so we can see how the sheet is recalculated by FlexCel.
			Dim Data As TXlsCellRange = Xls.GetNamedRange("Data", -1)
			For r As Integer = Data.Top To Data.Bottom - 1
				Xls.SetCellValue(r, Data.Left, r - Data.Top)
			Next r

			'Add an UDF to the sheet. We can enter the fucntion "BoolChoose" here because it was registered into FlexCel in LoadUDF()
			'If it hadn't been registered, this line would raise an Exception of an unknown function.
			Dim FmlaText As String = "=BoolChoose(TRUE,""This formula was entered with FlexCel!"",""It shouldn't display this"")"
			Xls.SetCellValue(11, 1, New TFormula(FmlaText))

			'Verify the UDF entered is correct. We can read any udf from Excel, even if it is not registered with AddUserDefinedFunction.
			Dim o As Object = Xls.GetCellValue(11, 1)
			Dim fm As TFormula = TryCast(o, TFormula)
			Debug.Assert(fm IsNot Nothing, "The cell must contain a formula")
			If fm IsNot Nothing Then
				Debug.Assert(fm.Text = FmlaText, "Error in Formula: It should be """ & FmlaText & """ and it is """ & fm.Text & """")
			End If

			'Recalc the sheet. As we are not saving it yet, we ned to make a manual recalc.
			Xls.Recalc()

			'Export the file to PDF so we can see the values calculated by FlexCel without Excel recalculating them.
			Using pdf As New FlexCelPdfExport(Xls, True)
				pdf.Export(saveFileDialog1.FileName)
			End Using

			'Save the file as xls too so we can compare.
			Xls.Save(Path.ChangeExtension(saveFileDialog1.FileName, "xls"))
		End Sub


		Private Sub button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles button1.Click
			AutoRun()
		End Sub

		Public Sub AutoRun()
			If saveFileDialog1.ShowDialog() <> System.Windows.Forms.DialogResult.OK Then
				Return
			End If
			Dim Xls As ExcelFile = New XlsFile(True)
			AddData(Xls)
			If MessageBox.Show("Do you want to open the generated files (PDF and XLS)?", "Confirm", MessageBoxButtons.YesNo) = System.Windows.Forms.DialogResult.Yes Then
				Process.Start(saveFileDialog1.FileName)
				Process.Start(Path.ChangeExtension(saveFileDialog1.FileName, "xls"))
			End If

		End Sub

		''' <summary>
		''' This is the method that will be called by the ASP.NET front end. It returns an array of bytes 
		''' with the report data, so the ASP.NET application can stream it to the client.
		''' </summary>
		''' <returns>The generated file as a byte array.</returns>
		Public Function WebRun() As Byte()
			Dim Xls As ExcelFile = New XlsFile(True)
			AddData(Xls)

			Using OutStream As New MemoryStream()
				Xls.Save(OutStream)
				Return OutStream.ToArray()
			End Using
		End Function
	End Class

	#Region "UDF definitions"
	''' <summary>
	''' Implements a custom function that will sum the cells in a range that have the same
	''' color of the source cell. This function mimics the VBA macro in the example, so when
	''' recalculating the sheet with FlexCel you will get the same results as with Excel.
	''' </summary>
	Public Class SumCellsWithSameColor
		Inherits TUserDefinedFunction

		''' <summary>
		''' Creates a new instance and registers the class in the FlexCel recalculating engine as "SumCellsWithSameColor".
		''' </summary>
		Public Sub New()
			MyBase.New("SumCellsWithSameColor")
		End Sub

		''' <summary>
		''' Returns the sum of cells in a range that have the same color as a reference cell.
		''' </summary>
		''' <param name="arguments"></param>
		''' <param name="parameters">In this case we expect 2 parameters, first the reference cell and then
		''' the range in which to sum. We will return an error otherwise.</param>
		''' <returns></returns>
		Public Overrides Function Evaluate(ByVal arguments As TUdfEventArgs, ByVal parameters() As Object) As Object
'			#Region "Get Parameters"
			Dim Err As TFlxFormulaErrorValue
			If Not CheckParameters(parameters, 2, Err) Then
				Return Err
			End If

			'The first parameter should be a range
			Dim SourceCell As TXls3DRange = Nothing
			If Not TryGetCellRange(parameters(0), SourceCell, Err) Then
				Return Err
			End If

			'The second parameter should be a range too.
			Dim SumRange As TXls3DRange = Nothing
			If Not TryGetCellRange(parameters(1), SumRange, Err) Then
				Return Err
			End If
'			#End Region

			'Get the color in SourceCell. Note that if Source cell is a range with more than one cell,
			'we will use the first cell in the range. Also, as different colors can have the same rgb value, we will compare the actual RGB values, not the ExcelColors
			Dim fmt As TFlxFormat = arguments.Xls.GetCellVisibleFormatDef(SourceCell.Sheet1, SourceCell.Top, SourceCell.Left)
			Dim SourceColor As Integer = fmt.FillPattern.FgColor.ToColor(arguments.Xls).ToArgb()

			Dim Result As Double = 0
			'Loop in the sum range and sum the corresponding values.
			For s As Integer = SumRange.Sheet1 To SumRange.Sheet2
				For r As Integer = SumRange.Top To SumRange.Bottom
					For c As Integer = SumRange.Left To SumRange.Right
						Dim XF As Integer = -1
						Dim val As Object = arguments.Xls.GetCellValue(s, r, c, XF)
						If TypeOf val Is Double Then 'we will only sum numeric values.
							Dim sumfmt As TFlxFormat = arguments.Xls.GetCellVisibleFormatDef(s, r, c)
							If sumfmt.FillPattern.FgColor.ToColor(arguments.Xls).ToArgb() = SourceColor Then
								Result += CDbl(val)
							End If
						End If
					Next c
				Next r
			Next s
			Return Result
		End Function
	End Class


	''' <summary>
	''' Implements a custom function that will return true if a number is prime.
	''' This function mimics the VBA macro in the example, so when
	''' recalculating the sheet with FlexCel you will get the same results as with Excel.
	''' </summary>
	Public Class IsPrime
		Inherits TUserDefinedFunction

		''' <summary>
		''' Creates a new instance and registers the class in the FlexCel recalculating engine as "IsPrime".
		''' </summary>
		Public Sub New()
			MyBase.New("IsPrime")
		End Sub

		''' <summary>
		''' Returns true if a number is prime.
		''' </summary>
		''' <param name="arguments"></param>
		''' <param name="parameters">In this case we expect 1 parameter with the number. We will return an error otherwise.</param>
		''' <returns></returns>
		Public Overrides Function Evaluate(ByVal arguments As TUdfEventArgs, ByVal parameters() As Object) As Object
'			#Region "Get Parameters"
			Dim Err As TFlxFormulaErrorValue
			If Not CheckParameters(parameters, 1, Err) Then
				Return Err
			End If

			'The parameter should be a double or a range.
			Dim Number As Double
			If Not TryGetDouble(arguments.Xls, parameters(0), Number, Err) Then
				Return Err
			End If
'			#End Region

			'Return true if the number is prime.
			Dim n As Integer = Convert.ToInt32(Number)
			If n = 2 Then
				Return True
			End If
			If n < 2 OrElse n Mod 2 = 0 Then
				Return False
			End If
			For i As Integer = 3 To Convert.ToInt32(Fix(Math.Sqrt(n))) Step 2
				If n Mod i = 0 Then
					Return False
				End If
			Next i
			Return True
		End Function
	End Class

	''' <summary>
	''' Implements a custom function that will choose between two different strings.
	''' This function mimics the VBA macro in the example, so when
	''' recalculating the sheet with FlexCel you will get the same results as with Excel.
	''' </summary>
	Public Class BoolChoose
		Inherits TUserDefinedFunction

		''' <summary>
		''' Creates a new instance and registers the class in the FlexCel recalculating engine as "BoolChoose".
		''' </summary>
		Public Sub New()
			MyBase.New("BoolChoose")
		End Sub

		''' <summary>
		''' Chooses between 2 different strings.
		''' </summary>
		''' <param name="arguments"></param>
		''' <param name="parameters">In this case we expect 3 parameters: The first is a boolean, and the other 2 strings. We will return an error otherwise.</param>
		''' <returns></returns>
		Public Overrides Function Evaluate(ByVal arguments As TUdfEventArgs, ByVal parameters() As Object) As Object
'			#Region "Get Parameters"
			Dim Err As TFlxFormulaErrorValue
			If Not CheckParameters(parameters, 3, Err) Then
				Return Err
			End If

			'The first parameter should be a boolean.
			Dim ChooseFirst As Boolean
			If Not TryGetBoolean(arguments.Xls, parameters(0), ChooseFirst, Err) Then
				Return Err
			End If

			'The second parameter should be a string.
			Dim s1 As String = Nothing
			If Not TryGetString(arguments.Xls, parameters(1), s1, Err) Then
				Return Err
			End If

			'The third parameter should be a string.
			Dim s2 As String = Nothing
			If Not TryGetString(arguments.Xls, parameters(2), s2, Err) Then
				Return Err
			End If
'			#End Region

			'Return s1 or s2 depending on ChooseFirst
			If ChooseFirst Then
				Return s1
			Else
				Return s2
			End If
		End Function
	End Class

	''' <summary>
	''' Implements a custom function that will choose the lowest member in an array.
	''' This function mimics the VBA macro in the example, so when
	''' recalculating the sheet with FlexCel you will get the same results as with Excel.
	''' </summary>
	Public Class Lowest
		Inherits TUserDefinedFunction

		''' <summary>
		''' Creates a new instance and registers the class in the FlexCel recalculating engine as "Lowest".
		''' </summary>
		Public Sub New()
			MyBase.New("Lowest")
		End Sub

		''' <summary>
		''' Chooses the lowest element in an array.
		''' </summary>
		''' <param name="arguments"></param>
		''' <param name="parameters">In this case we expect 1 parameter that should be an array. We will return an error otherwise.</param>
		''' <returns></returns>
		Public Overrides Function Evaluate(ByVal arguments As TUdfEventArgs, ByVal parameters() As Object) As Object
'			#Region "Get Parameters"
			Dim Err As TFlxFormulaErrorValue
			If Not CheckParameters(parameters, 1, Err) Then
				Return Err
			End If

			'The first parameter should be an array.
			Dim SourceArray(,) As Object = Nothing
			If Not TryGetArray(arguments.Xls, parameters(0), SourceArray, Err) Then
				Return Err
			End If
'			#End Region

			Dim Result As Double = 0
			Dim First As Boolean = True
			For Each o As Object In SourceArray
				If TypeOf o Is Double Then
					If First Then
						First = False
						Result = CDbl(o)
					Else
						If CDbl(o) < Result Then
							Result = CDbl(o)
						End If
					End If
				Else
					Return TFlxFormulaErrorValue.ErrValue
				End If
			Next o

			Return Result
		End Function

	End Class
	#End Region

End Namespace

Program.vb

Namespace ExcelUserDefinedFunctions
	Friend NotInheritable Class Program

		Private Sub New()
		End Sub

		''' <summary>
		''' The main entry point for the application.
		''' </summary>
		<STAThread> _
		Shared Sub Main()
			Application.EnableVisualStyles()
			Application.SetCompatibleTextRenderingDefault(False)
			Application.Run(New mainForm())
		End Sub
	End Class
End Namespace