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