User Defined Types
A user defined type (or data structure) lets you create a single data type that can represent more than one built-in data type.
These are Value Data Types (not Reference).
Often abbreviated to UDTs.
The default value is the default value of the individual elements
You create a user defined type using the Type statement.
A type is a collection of variables (or fields)
This combines multiple data types into a single data type.
Type type_UserDefined
sField1 As String
iField2 As Integer
bField3 As Boolean
End Type
This allows you to define a single variable to access all the fields
You define custom data types outside of procedures at the top of your module. Once you have created your type use the Dim statement to declare a variable of that type.
Dim uMyUserDefinedType As type_UserDefined
uMyUserDefinedType.Field1 = "Tuesday"
uMyUserDefinedType.Field2 = 24
uMyUserDefinedType.Field3 = False
Types can be very useful but they do have a number of limitations:
You can't declare a new instance of a type. They must all be declared at design time
You can't validate or restrict the values that are assigned to the different fields
A type is just a static data structure and cannot have any actions associated with it
Using Classes instead of types can overcome all these limitations
You define user defined data types outside at the very top of your modules before any procedures or functions.
UDTs are far more efficient than using a Collection object
It is important to visually distinguish user defined types from other data types.
For this reason is should always be prefixed with an uppercase T.
Type type_ContactInfo
lContactNo As Long
End Type
Type type_Person
sName As String
sAge As String
lDateBorn As Long
End Type
Once you have declared the user defined type you can use the Dim statement to declare a variable of that type.
Dim uMyVariable As TPerson
Array
A fixed array is an example of a static data structure where you know the total number of elements
Arrays are linear
Arrays can be resized but not very efficiently.
Arrays can be sorted but not very efficiently.
Arrays do not use space very efficiently
If you declare an array to hold 50 elements and then only use 5.
More Details
Dynamic Data Types
These always refer to data in memory.
The size only increases when necessary
Contains at least once to another instance of the same class
The simplest example is a linear data structure
Linear Data Types
This is a dynamic data structure
Each element contains information and a reference to another element of the same type
Easy to add and remove elements in any position
Easy to resize
Typically include at least one header item
VB 6.0
Since VB 6.0 user defined types can be passed as property values.
Since VB 6.0 user defined type can be used in public function declarations
Remember
When defining a user defined type all variables within the type should be in capitals
A UDT is not a variable and does not take up any space.
ArrayList
??
System.Collections.ArrayList
This does not work with Office 365.
This only works if you have .NET 3.5 installed.
Dim oArrayList As Object
Set oArrayList = CreateObject("System.Collections.ArrayList")
Private Sub SortDictionary(ByVal oDict As Scripting.Dictionary)
On Error Resume Next
Dim oArrayList As Object
Dim oNewDict As Scripting.Dictionary
Dim vKeys As Variant
Dim vKey As Variant
Set oArrayList = CreateObject("System.Collections.ArrayList")
' Transpose Keys into ones based array.
vKeys = oDict.Keys
vKeys = Application.WorksheetFunction.Transpose(vKeys)
For Each vKey In vKeys
Call oArrayList.Add(vKey)
Next
oArrayList.Sort
' Create a new dictionary with the same characteristics as the old dictionary.
Set oNewDict = New Scripting.Dictionary
oNewDict.CompareMode = oDict.CompareMode
' Iterate over the array list and transfer values from old dictionary to new dictionary.
For Each vKey In oArrayList
sKey = CStr(vKey)
If oDict.Exists(sKey) Then
Call oNewDict.Add(sKey, oDict.Item(sKey))
End If
Next
' Replace the old dictionary with new sorted dictionary.
Set oDict = oNewDictionary
Set oNewDict = Nothing
Set oArrayList = Nothing
End Sub
Stack
BET_StackItem class
Public Value As Variant
Public NextItem As Variant
Private Sub Class_Initialize()
Set NextItem = Nothing
End Sub
Private Sub Class_Terminate()
Set NextItem = Nothing
End Sub
BET_Stack class
Private _stackTop As BET_StackItem
Public Function Method_Pop() As Variant
If (Property_StackEmpty = False) Then
Method_Pop = _stackTop.NextItem
Set _stackTop = _stackTop.NextItem
End If
End Sub
Public Sub Method_Push(ByVal vText As Variant)
Dim newTop As BET_StackItem
Set _newTop = New BET_StackItem
newTop.Value = vText
Set _newTop.NextItem = _stackTop
Set _stackTop = newTop
End Sub
Public Function Method_Peek() As Variant
If (Property_StackEmpty = True) Then
Method_Peek = Null
Else
Method_Peek = _stackTop.Value
End If
End Function
Public Property Get Property_StackEmpty() As Boolean
Property_StackEmpty = (_stackTop Is Nothing)
End Property
Private Sub Class_Initialize()
Set _topStack = Nothing
End Sub
Private Sub Class_Terminate()
Set _topStack = Nothing
End Sub
Dim myStack As BET_Stack
Dim myStackItem As BET_StackItem
myStack = New BET_Stack
myStackItem = New BET_StackItem
myStackItem.Value = 5
myStack.Method_Push myStackItem
myStackItem = New BET_StackItem
myStackItem.Value = 10
myStack.Method_Push myStackItem
Debug.Print myStack.Method_Pop()
Debug.Print myStack.Method_Pop()
System.Collections.Stack
This does not work with Office 365.
This only works if you have .NET 3.5 installed.
Add a reference to C:\Windows\Microsoft.NET\Framework\ v4.0.30319\mscorlib.dll for early binding.
Public Sub CreateStack()
Dim oStack As Object
Set oStack = CreateObject("System.Collections.Stack")
oStack.Push "Monday"
oStack.Clear
oStack.Push "Tuesday"
oStack.Push "Wednesday"
oStack.Push "Thursday"
oStack.Push "Friday"
Debug.Print oStack.Peek()
If (oStack.Contains("Monday") = True) Then
Debug.Print "Monday was removed"
End If
Debug.Print "Last day added was : " & oStack.Pop()
Set oStack = Nothing
End Sub
Heap
link - github.com/davidwilson3/Custom-Heap-Class-for-VBA/blob/master/ADT_Heap.cls
Queue
BET_QueueItem class
Public Value As Variant
Public NextItem As BET_QueueItem
Private Sub Class_Initialize()
Set NextItem = Nothing
End Sub
Private Sub Class_Terminate()
Set NextItem = Nothing
End Sub
BET_Queue class
Private _Front As BET_QueueItem
Private _Rear As BET_QueueItem
Public Sub Add(NewItem As Variant)
Dim new_QItem As BET_QueueItem
Set new_QItem = New BET_QueueItem
new_QItem.Value = NewItem
If (Me.Property_IsQueueEmpty() = True) Then
Set Me._Front = new_QItem
Set Me._Back = new_QItem
Else
Set Me._Rear.NextItem = new_QItem
Set _Rear = new_QItem
End If
End Sub
Public Function Remove() As Variant
If (Me.Property_IsQueueEmpty() = True) Then
Remove = Nothing
Else
Remove = _Front.Value
If (_Front Is _Rear) Then
Set _Front = Nothing
Set _Rear = Nothing
Else
Set _Front = _Front.NextItem
End If
End If
End Function
Property Get Property_IsQueueEmpty() As Boolean
Property_IsQueueEmpty = ( (_Front Is Nothing) And (_Back is Nothing) )
End Property
Private Sub Class_Initialize()
Set NextItem = Nothing
End Sub
Public Sub Testing_Queue()
Dim myQueue As BET_Queue
myQueue = New BET_Queue
myQueue.Add "Monday"
myQueue.Add "Tuesday"
myQueue.Add "Wednesday"
myQueue.Add "Thursday"
myQueue.Add "Friday"
Do Until (myQueue.Property_IsQueueEmpty() = True)
Debug.Print myQueue.Remove()
Loop
End Sub
System.Collections.Queue
This does not work with Office 365.
This only works if you have .NET 3.5 installed.
Dim oQueue As Object
Set oQueue = CreateObject("System.Collections.Queue")
Linked List
Ordered linked list.
BET_ListItem class
Private TheValue As Variant
Private NextNode As BET_ListItem
BET_LinkedList class
Private head As BET_ListItem
Public Sub Add(value As Variant)
Dim node As BET_ListItem
Dim lCurrent As BET_ListItem
Dim lPrevious As BET_ListItem
Set node = New BET_ListItem
node.TheValue = value
all Fun_Search(value, lCurrent, lPrevious)
Set node.NextNode = head
Set head = node
End Sub
Public Sub Remove(value As Variant)
Dim node As BET_ListItem
Dim prev As BET_ListItem
Set node = head
While Not node Is Nothing
If node.TheValue = value Then
'remove node
If node Is head Then
Set head = node.NextNode
Else
Set prev.nextNode = node.NextNode
End If
Exit Sub
End If
Set prev = node
Set node = node.NextNode
Wend
End Sub
Public Function Exists(value As Variant) As Boolean
Dim node As BET_ListItem
Set node = head
While Not node Is Nothing
If node.TheValue = value Then
Exists = True
Exit Function
End If
Set node = node.NextNode
Wend
End Function
Public Function Count() As Long
Dim node As BET_ListItem
Set node = head
While Not node Is Nothing
Count = Count + 1
Set node = node.NextNode
Wend
End Function
Private Function Fun_Search(ByVal varItem As Variant,
ByRef lCurrent As BET_ListItem, _
ByRef lPrevious As BET_ListItem)
Dim bFound As Boolean
bFound = False
Set lPrevious = Nothing
Set lCurrent = _Head
Do While Not lCurrent Is Nothing
If (varItem > lCurrent.Value) Then
Set lPrevious = lCurrent
Set lCurrent = lCurrent.NetItem
Else
Exit Do
End If
Loop
If Not (lCurrent Is Nothing) Then
bFound = (lCurrent.Value = varItem)
End If
Search = bFound
End Function
Private Function Delete()
End Function
Public Sub Testing_LinkedList()
Dim myLinkedList As BET_LinkedList
Set myLinkedList = New BET_LinkedList
myLinkedList.Add("Mon")
Debug.Print myLinkedList.Count
myLinkedList.Add("Tue")
myLinkedList.Add("Wed")
Debug.Print myLinkedList.Exists("Tue")
myLinkedList.Remove("Tue")
End Sub
Binary Tree
Binary Search Tree
This is a simple unbalanced BST that is implemented with two class modules (TreeNode and BinarySearchTree) and a Random_Main testing module, which contains the macro to run all the BST functions.
The example fills a BST with 10 random integers and completes several functions:
Private Const bstSize As Integer = 10
Private Const upperRnd As Integer = 10
Private Const lowerRnd As Integer = 1
Public WB As Workbook
Public WS As Worksheet
Public outPut As Collection
Public Sub Random_MAIN()
Dim loopCount As Integer
Dim rndNumber As Integer
Dim BST As New BinarySearchTree
Dim root As TreeNode
Set WB = ThisWorkbook
Set WS = WB.Sheets("BST")
Set outPut = New Collection
For loopCount = 1 To bstSize
rndNumber = rndOmizer: Set root = BST.insert(root, rndNumber): outPut.add (rndNumber)
Next loopCount
updateBSTSheet ("A")
myPrint ("Order in which values are inserted.")
myPrint ("")
End Sub
TreeNode Class
Here is the "TreeNode" class module with a variable to hold a value (Value) in the TreeNode, a variable to count duplicates (ValueCount), and links to the left and right child nodes (LeftChild and RightChild).
Everything is declared Public for easy access outside the class.
Public ValueCount As Long
' stores the actual value (integer in this case)
Public Value As Variant
' stores the pointer to the left child node
Public LeftChild As TreeNode
' stores the pointer to the right child node
Public RightChild As TreeNode
BinarySearchTree Class
Here is the "insert" function of the "BinarySearchTree" class module. This function creates the root TreeNode if it doesn't yet exist, adds a ValueCount to existing values instead of allowing duplicates, and recursively goes down to the first left or right Nothing TreeNode to add a new TreeNode then returns a TreeNode.
Recursively insert value with no duplicates
Public Function insert(TN As TreeNode, valToInsert As Variant) As TreeNode
' create node if it doesn't exist
If TN Is Nothing Then
Set TN = New TreeNode
TN.Value = valToInsert
TN.ValueCount = 1
' count duplicates without expanding tree
ElseIf TN.Value = valToInsert Then
TN.ValueCount = TN.ValueCount + 1
' recursively call until empty node is found
Else
' go left or right based on value and recursively call
If valToInsert < TN.Value Then
Set TN.LeftChild = insert(TN.LeftChild, valToInsert)
ElseIf valToInsert > TN.Value Then
Set TN.RightChild = insert(TN.RightChild, valToInsert)
End If
End If
' set the function to the node continually
Set insert = TN
End Function
This function recursively deletes a TreeNode based upon Value, whether that TreeNode is a leaf, has no children, or is a binary tree and then returns a TreeNode.
Public Function delete(TN As TreeNode, valToDelete As Variant) As TreeNode
' go left or right by value to be deleted and reccrsively call
If valToDelete < TN.Value Then
Set TN.LeftChild = delete(TN.LeftChild, valToDelete)
ElseIf valToDelete > TN.Value Then
Set TN.RightChild = delete(TN.RightChild, valToDelete)
' delete node if value is the same
Else
Dim copyNode As TreeNode
' node has no or only one child
If TN.LeftChild Is Nothing Then
Set copyNode = TN.RightChild
Set TN = Nothing
Set delete = copyNode
Exit Function
ElseIf TN.RightChild Is Nothing Then
Set copyNode = TN.LeftChild
Set TN = Nothing
Set delete = copyNode
Exit Function
Else
' node has two children: 1st get inorder successor:
' 2nd copy to this node: 3rd delete inorder successor
Set copyNode = minValueNode(TN.RightChild)
TN.Value = copyNode.Value
Set TN.RightChild = delete(TN.RightChild, copyNode.Value)
End If
End If
' set the function to the node continually
Set delete = TN
End Function
This function loops to find the successor TreeNode to the TreeNode that has two children and is being deleted, then returns a TreeNode.
This function is called by the "delete" function.
Private Function minValueNode(TN As TreeNode) As TreeNode
' loop down to find the leftmost leaf
Dim tempNode As TreeNode: Set tempNode = TN
While Not tempNode.LeftChild Is Nothing
Set tempNode = tempNode.LeftChild
Wend
Set minValueNode = tempNode
End Function
This function recursively finds the Value then returns a boolean.
Public Function search(TN As TreeNode, valToSearch As Variant) As Boolean
Dim searchNode As TreeNode: Set searchNode = TN
' loop until value is found (true) or bst ends (false)
While Not searchNode Is Nothing
If searchNode.Value = valToSearch Then
search = True: Exit Function
ElseIf valToSearch < searchNode.Value Then
Set searchNode = searchNode.LeftChild
Else
Set searchNode = searchNode.RightChild
End If
Wend
End Function
This function loops right to find the maximum Value, then returns a Variant.
Get the max value from right tree recursively
Public Function maxValue(TN As TreeNode) As Variant
Dim maxValueNode As TreeNode: Set maxValueNode = TN
While Not maxValueNode.RightChild Is Nothing
Set maxValueNode = maxValueNode.RightChild
Wend
maxValue = maxValueNode.Value
End Function
This function loops left to find the minmum Value then returns a Variant.
Get the min value from left tree recursively
Public Function minValue(TN As TreeNode) As Variant
Dim minValueNode As TreeNode: Set minValueNode = TN
While Not minValueNode.LeftChild Is Nothing
Set minValueNode = minValueNode.LeftChild
Wend
minValue = minValueNode.Value
End Function
This function recurves the BST in the order of the left TreeNode, then the root TreeNode, then the right TreeNode in order to display the values from smallest to largest.
A Collection is returned but that variable could be an Array.
Display the values in order from lowest to highest (left, root, right) recursively
Public Function InOrder(TN As TreeNode, Optional myCol As Collection) As Collection
If Not TN Is Nothing Then
Call InOrder(TN.LeftChild, myCol)
If myCol Is Nothing Then
Set myCol = New Collection
myCol.add ("#: " & TN.Value & " (" & TN.ValueCount & " Total)")
Else
myCol.add ("#: " & TN.Value & " (" & TN.ValueCount & " Total)")
End If
Call InOrder(TN.RightChild, myCol)
End If
Set InOrder = myCol
End Function
This function recurses the BST in the order of the root TreeNode, then the left TreeNode, then the right TreeNode.
This could be used to get a prefix or to copy the BST but here I am displaying them. A Collection is returned but that variable could be an Array.
Display the values from left to right (root, left, right) recursively
Public Function PreOrder(TN As TreeNode, Optional myCol As Collection) As Collection
If Not TN Is Nothing Then
If myCol Is Nothing Then
Set myCol = New Collection
myCol.add ("#: " & TN.Value & " (" & TN.ValueCount & " Total)")
Else
myCol.add ("#: " & TN.Value & " (" & TN.ValueCount & " Total)")
End If
Call PreOrder(TN.LeftChild, myCol)
Call PreOrder(TN.RightChild, myCol)
End If
Set PreOrder = myCol
End Function
This function recurses the BST in the order of the left TreeNode, then the right TreeNode, then the right root TreeNode.
This could be used to get a postfix or to delete the BST but here I am displaying them. A Collection is returned but that variable could be an Array.
Display the values from right to left (left, right, root) recursively
Public Function PostOrder(TN As TreeNode, Optional myCol As Collection) As Collection
If Not TN Is Nothing Then
Call PostOrder(TN.LeftChild, myCol)
Call PostOrder(TN.RightChild, myCol)
If myCol Is Nothing Then
Set myCol = New Collection: myCol.add ("#: " & TN.Value & " (" & TN.ValueCount & " Total)")
Else
myCol.add ("#: " & TN.Value & " (" & TN.ValueCount & " Total)")
End If
End If
Set PostOrder = myCol
End Function
Hash Table
© 2026 Better Solutions Limited. All Rights Reserved. © 2026 Better Solutions Limited TopPrev