11 Data Structures

Provide a way to organize and manage data efficiently.
Different data structures are optimized for specific operations or offer trade-offs between various operations.
Abstract the underlying representation of data, simplifying data handling for programmers without needing to know low-level details.
Choosing the right data structure is crucial: different structures have varying performance characteristics, impacting memory usage, speed, and ease of use.

11.1 Main VBA data structures

In VBA, there are many ways to store and organize data:

Arrays: Group of variables that share a name (each variable identified by an index)
Collections: Generic containers for storing objects.
Dictionaries: Store key-value pairs.

11.2 Arrays

Sometimes, you need to pass multiple values to a function or return multiple values. Imagine you want to calculate the average of 6 students’ grades.

You could pass each grade as a separate argument:

Option Explicit

Function AvgGradesIndividualVariables( _
    gradeStudent1 As Double, _
    gradeStudent2 As Double, _
    gradeStudent3 As Double, _
    gradeStudent4 As Double, _
    gradeStudent5 As Double, _
    gradeStudent6 As Double) As Double

    AvgGrades = ( _
                gradeStudent1 + _
                gradeStudent2 + _
                gradeStudent3 + _
                gradeStudent4 + _
                gradeStudent5 + _
                gradeStudent6 _
                ) / 6
                
End Function

Sub TestAvgGrades()

    Debug.Assert AvgGradesIndividualVariables(10, 10, 10, 10, 10, 10) = 10
    Debug.Assert AvgGradesIndividualVariables(5, 5, 5, 5, 5, 5) = 5

End Sub

However, this approach is not scalable. If you have 100 students, you would need to pass 100 arguments.

This is where arrays come in handy. In the example below, we pass an array of grades to the function. Notice that the function signature changes to accept an array of doubles.

This way, you can pass any number of grades to the function (as long as there is enough memory).

In the example below, we pass an array of grades gradesStudent to the function and the number of grades n to calculate the average.

Option Explicit

Function AvgGradesWithArrays1( _
        gradeStudent() As Double,
        n As Integer) As Double
    
    Dim i As Integer
    Dim sum As Double
    
    For i = 1 To n
        
        sum = sum + gradeStudent(i)
        Debug.Print "Index:"; i; "="; (gradeStudent(i)); "- sum:"; sum
        
    Next i
    
    AvgGradesWithArrays1 = sum / n
               
End Function

Function AvgGradesWithArrays(gradeStudent() As Double) As Double
    
    Dim i As Integer
    Dim sum As Double
    
    Dim lowerBound As Integer
    Dim upperBound As Integer
    lowerBound = LBound(gradeStudent)
    upperBound = UBound(gradeStudent)
    Dim n As Integer
    n = (upperBound - lowerBound) + 1
    
    'For i = 1 To (UBound(gradeStudent) - LBound(gradeStudent)) + 1
    'For i = LBound(gradeStudent) To UBound(gradeStudent)
    For i = 1 To n
        
        sum = sum + gradeStudent(i)
        Debug.Print "Index:"; i; "="; (gradeStudent(i)); "- sum:"; sum
        
    Next i
    
    AvgGradesWithArrays = sum / n
 
End Function

Sub TestAvgGradesWithArrays()

    Dim grades(1 To 4) As Double
    Dim grades2(1 To 4) As Double
    
    'Value assigment with loop
    Dim i As Integer
    For i = 1 To 4
        grades(i) = 10
        'grades(i) = InputBox("Type a grade:")
        grades2(i) = 5
    Next i

    Debug.Print "Test 1:"
    Debug.Assert AvgGradesWithArrays(grades) = 10
    'Debug.Assert AvgGradesWithArrays1(grades, 4) = 10
    
    Debug.Print "Test 2:"
    Debug.Assert AvgGradesWithArrays(grades2) = 5
    'Debug.Assert AvgGradesWithArrays1(grades2, 4) = 5
End Sub

11.2.1 Array Length and First Element Index

To determine the length of an array, you can use the LBound and UBound functions. The LBound function returns the lower bound of the array, and the UBound function returns the upper bound.

The lower bound of an array is the index of the first element, and the upper bound is the index of the last element.

Table 11.1: Using Option Base configuration to set array lower bound

(a) Array size n and bounds assuming Option Base 0.

n	LBound	UBound
0	0	0
1	0	0
2	0	1
3	0	2
4	0	3
5	0	4

(b) Array size n and bounds assuming Option Base 1.

n	LBound	UBound
0	1	0
1	1	1
2	1	2
3	1	3
4	1	4
5	1	5

As shown in Table 11.1, considering an array with 4 elements (i.e., n = 4):

if the first element of the array is at index 0 (i.e., Option Base 0) the lower bound is 0 and the upper bound is 3 (see Table 11.1 (a)).
if the first element of the array is at index 1 (i.e., Option Base 1) the lower bound is 1 and the upper bound is 4 (see Table 11.1 (b)).

The length of the array is calculated as (UBound - LBound) + 1.

In VBA, the default lower bound for arrays is 0. However, you can change the lower bound to 1 by including Option Base 1 at the top of your code (a.k.a. Declarations section).

Figure 11.1: Starting from 0 or 1? VBA (and many other programming languages, such as Python and C) arrays start at 0 by default. However, you can change the starting index to 1 by including `Option Base 1` at the top of your code. Starting at 1 can make the code more intuitive for some users.

In the example below, we create an array grades with 4 elements and calculate its length.

Option Explicit

Function getArrayLen(a As Variant) As Integer
    getArrayLen = (UBound(a) - LBound(a)) + 1
    'Assuming LBound = 0 and UBound = 3
    'getArrayLen = UBound(a)
End Function

Sub testGetArrayLen()

    Dim grades(1 To 4) As Double
    
    'Value assigment with loop
    Dim i As Integer
    For i = 1 To 4
        grades(i) = 10
    Next i
    
    Debug.Print getArrayLen(grades)
    
End Sub

11.2.2 Static, One-Dimensional Arrays (Vectors)

One-dimensional arrays are the simplest form of arrays. They are commonly referred to as vectors and typically used to store a collection of elements of the same data type.

In VBA, a one-dimensional array has the following characteristics:

Fixed number of elements specified in the declaration.
First and last index are identified in the declaration. For example:
- Dim myVector(4) As Integer (five integers indexed from 0 to 4)¹
- Dim myVector(1 To 5) As Integer (five integers indexed from 1 to 5)
- Dim myVector(2 To 5) As Integer (four integers indexed from 2 to 5)

11.2.2.1 Example - Declaring a Static Array of Different Data Types

You can declare arrays of different data types, such as strings, double-precision numbers, and boolean values. Each array index will store a value of the specified data type.

To improve memory usage and performance, it is recommended to use the most appropriate data type for the elements you are storing (check VBA types in Table 8.1).

In the example below, we declare arrays of different data types.

Sub ExampleDifferentTypesOfStaticArrays()

    'Declare arrays with different data types
    Dim strArray(3) As String 'Array of strings
    Dim dblArray(2) As Double 'Array of double-precision numbers
    Dim boolArray(1) As Boolean 'Array of boolean values
    
End Sub

11.2.2.2 Example - Zero-Based Array

In the example below, we declare an array of integers with 5 elements (indexed from 0 to 4) and populate it with values.

Option Explicit

Sub Example1DStaticZeroBasedArray()
    
    'Declare an array of integers with 5 elements (0 to 4)
    Dim myVector(4) As Integer
    
    'Populate the array with values
    myVector(0) = 10
    myVector(1) = 20
    myVector(2) = 30
    myVector(3) = 40
    myVector(4) = 50
    
    'Access an element of the array using its index
    Dim value As Integer
    value = myVector(2) 'Accesses the third element (30) and assigns it
    Debug.Print value

End Sub

11.2.2.3 Example - One-Based Array

In the example below, we declare an array of integers with 5 elements (indexed from 1 to 5) and populate it with values.

Option Explicit

Sub Example1DStaticOneBasedArray()
    
    'Declare an array of integers with 5 elements (1 to 5)
    Dim myVector(1 To 5) As Integer
    
    'Populate the array with values
    myVector(1) = 10
    myVector(2) = 20
    myVector(3) = 30
    myVector(4) = 40
    myVector(5) = 50
    
    'Access an element of the array using its index
    Dim value As Integer
    value = myVector(3) 'Accesses the third element (30) and assigns it
    
    Debug.Print value

End Sub

11.2.2.4 Example - Looping Through a Static Array

In the example below, we declare an array of integers with 5 elements and populate it with values. We then loop through the array to access and print each element.

In this example, we use a For loop to iterate through the array elements. The loop starts at the lower bound (1) of the array and continues until the upper bound (5).

Notice that both the lower and upper bounds are hardcoded in the loop.

Option Explicit

Sub ExampleArrayLooping()
    
    'Declare an integer vector with 5 elements
    Dim myVector(1 To 5) As Integer
    
    'Initialize the vector with values
    myVector(1) = 10
    myVector(2) = 20
    myVector(3) = 30
    myVector(4) = 40
    myVector(5) = 50
       
    'Access and print elements in the vector
    Dim i As Integer
    Dim output As String
    
    For i = 1 To 5
        output = output & "Element " & i & ": " & myVector(i) & vbCrLf
    Next i
    
    Debug.Print "Vector Elements:" & vbCrLf & output

End Sub

11.2.2.5 Example - Using Lower and Upper Bound Functions

In the example below, we declare an array of integers with 5 elements and populate it with values. We then determine the lower and upper bounds of the array using the LBound and UBound functions.

This is useful when you want to access the array elements without hardcoding the bounds. It makes the code more flexible and easier to maintain (if you need to change the array size, you do not need to update the loop bounds).

Option Explicit

Sub ExampleArrayLoopingUsingLowerAndUpperBoundFunctions()
    
    'Declare an integer vector with 5 elements
    Dim myVector(1 To 5) As Integer
    
    'Initialize the vector with values
    myVector(1) = 10
    myVector(2) = 20
    myVector(3) = 30
    myVector(4) = 40
    myVector(5) = 50
    
    'Determine the lower and upper bounds of the vector
    Dim lowerBound As Integer
    Dim upperBound As Integer
    
    lowerBound = LBound(myVector) ' Get the lower bound (1)
    upperBound = UBound(myVector) ' Get the upper bound (5)
    
    'Display the results
    Debug.Print "Lower Bound: " & lowerBound & vbCrLf & "Upper Bound: " & upperBound
    
    'Access and print elements in the vector
    Dim i As Integer
    Dim output As String
    
    For i = lowerBound To upperBound
        output = output & "Element " & i & ": " & myVector(i) & vbCrLf
    Next i
    
    Debug.Print "Vector Elements:" & vbCrLf & output
End Sub

Sub ExampleCountArrayElements()
    
    'Declare an integer array with 5 elements
    Dim myVector(1 To 5) As Integer
    
    'Determine the number of elements in the array
    Dim elementCount As Integer
    
    ' Calculate the number of elements by subtracting
    'the lower bound from the upper bound
    elementCount = UBound(myVector) - LBound(myVector) + 1
    
    ' Print the number of elements
    Debug.Print "Number of elements in the array: " & elementCount
    
End Sub

11.2.2.6 Returning an Array From a Function

In the example below, we create a function that returns an array of integers containing the multiples of a given number n.

The function ExampleReturningArrayWithMultiplesOf takes an integer n as input and returns an array of integers with the multiples of n from 1 to 10.

Notice that the function signature specifies that the function returns an array of integers using Integer(). The parentheses () after Integer indicate that the function returns an array.

Notice that the Sub TestExampleReturningArrayWith calls the function ExampleReturningArrayWithMultiplesOf and checks if the returned array contains the expected values.

The call to ExampleReturningArrayWithMultiplesOf(3) returns an array of integers with the multiples of 3 from 1 to 10. Then, the Sub checks if the first, second, and fifth elements of the array are equal to 3, 6, and 15, respectively.

The values are accessed using the array index notation (i), where i is the index of the element in the array.

Option Explicit

Function ExampleReturningArrayWithMultiplesOf(n As Integer) As Integer()
    
    Dim anArray(1 To 10) As Integer
    
    Dim i As Integer

    For i = 1 To 10
        anArray(i) = n * i
    Next i
    
    ExampleReturningArrayWithMultiplesOf = anArray
    
End Function


Sub TestExampleReturningArrayWith()

    Debug.Assert ExampleReturningArrayWithMultiplesOf(3)(1) = 3
    Debug.Assert ExampleReturningArrayWithMultiplesOf(3)(2) = 6
    Debug.Assert ExampleReturningArrayWithMultiplesOf(3)(5) = 15

End Sub

11.2.2.7 Passing an Array To a Function

An array can be received as a Variant, which is a generic data type that can hold any type of data.

Notice that anArray is declared as a simple variable, without parentheses ().

Option Explicit

Function ExampleReceivingArrayAsVariant(anArray As Variant) As Integer
    
    Dim i As Integer
    Dim sum As Integer
    For i = LBound(anArray) To UBound(anArray)
        sum = sum + anArray(i)
    Next i
    
    ExampleReceivingArrayAsVariant = sum
    
End Function

Sub TestReceivingArrayAsVariant()
    Debug.Assert ExampleReceivingArrayAsVariant(v1Double) = 6
    Dim anArray(1 To 3) As Integer
    Debug.Assert ExampleReceivingArrayAsVariant(anArray) = 0
End Sub

11.2.2.8 Example - Receiving Arrays with a Specific Data Type

An array can also be received with a specific data type. Notice that anArray is declared with parentheses ().

Option Explicit
Function ExampleReceivingArray(anArray() As Integer) As Integer
    
    Dim i As Integer
    Dim sum As Integer
    For i = LBound(anArray) To UBound(anArray)
        sum = sum + anArray(i)
    Next i
    
    ExampleReceivingArray = sum
    
End Function

Sub TestReceivingArray()
    Dim anArray(1 To 3) As Integer
    anArray(1) = 2
    Debug.Assert ExampleReceivingArray(anArray) = 2
End Sub

11.3 Exercises (Arrays)

In the following exercises, if a vector has to be returned, create a static array sufficiently large (e.g., 1 To 100).

11.3.1 Print Vector

Create a subroutine that takes a 1D array (vector) as input and prints its elements in a human-readable format in the Immediate window.

Tip

Use this sub throughout the module to print the vectors!

Option Explicit

Sub PrintVector(arr() As Double)
    'Add code here!
End Sub

Sub TestPrintVector()

    'Input array
    Dim vector(1 To 5) As Double
    vector(1) = 1
    vector(2) = 2
    vector(3) = 3
    vector(4) = 4
    vector(5) = 5
    
    PrintVector vector
    
    'Result:
    
    'Element 1: 1
    'Element 2: 2
    'Element 3: 3
    'Element 4: 4
    'Element 5: 5

End Sub

11.3.2 Dot Product

Create a function that takes two vectors of the same length as input and returns the dot product of the input vectors (a single number).

Function DotProduct( _
        vector1() As Double, _ 
        vector2() As Double) As Double
    'Add code here!
End Function

Sub TestDotProduct()

    'Input array 1
    Dim vector1(1 To 5) As Double
    vector1(1) = 1
    vector1(2) = 2
    vector1(3) = 3
    vector1(4) = 4
    vector1(5) = 5
    
    'Input array 2
    Dim vector2(1 To 5) As Double
    vector2(1) = 5
    vector2(2) = 4
    vector2(3) = 2
    vector2(4) = 3
    vector2(5) = 1
    
    Debug.Assert DotProduct(vector1, vector2) = 36
    
End Sub

11.3.3 Find Maximum Element

Create a function that takes a vector of numbers as input and returns the maximum element in the vector.

Option Explicit

Function FindMaxElement(vector() As Double) As Double
    'Add code here!
End Function

Sub TestFindMaxElement()
    
    'Input array
    Dim vector(1 To 5) As Double
    vector(1) = 1
    vector(2) = 2
    vector(3) = 3
    vector(4) = 4
    vector(5) = 5
    
    Dim max As Double
    max = FindMaxElement(vector)
    Debug.Assert max = 5

End Sub

11.3.4 Search Element

Create a function that takes a vector of numbers and a target value as input and returns True if the target value is found in the vector, or False otherwise.

Option Explicit

Function SearchElement( _
        vector() As Double, _
        target As Double) As Boolean
    'Add code here!
End Function

Sub TestSearchElement()

    'Input array
    Dim vector(1 To 5) As Double
    vector(1) = 1
    vector(2) = 2
    vector(3) = 3
    vector(4) = 4
    vector(5) = 5

    Debug.Assert SearchElement(vector, 3) = True
    Debug.Assert SearchElement(vector, 6) = False

End Sub

11.3.5 Count Elements

Create a function that takes a vector of numbers and a target value as input and returns the count of occurrences of the target value in the vector.

Option Explicit

Function CountElements(vector() As Double, target As Double) As Integer
    'Add code here!
End Function

Sub TestCountElements()

    'Input array 1
    Dim vector1(1 To 6) As Double
    vector1(1) = 1
    vector1(2) = 2
    vector1(3) = 3
    vector1(4) = 2
    vector1(5) = 4
    vector1(6) = 2
    
    'Input array 2
    Dim vector2(1 To 5) As Double
    vector2(1) = 1
    vector2(2) = 2
    vector2(3) = 3
    vector2(4) = 4
    vector2(5) = 5
    
    Debug.Assert CountElements(vector1, 2) = 3
    Debug.Assert CountElements(vector2, 6) = 0
End Sub

11.3.6 Search Array in Another

Create a function that takes two vectors of numbers as input and returns True if the first vector can be found as a contiguous subsequence within the second vector. Otherwise, it returns False.

Option Explicit

Function SearchArrayInAnother( _
        subArray() As Double, _
        mainArray() As Double) As Boolean
    ' Your code here
End Function

Sub TestSearchArrayInAnother()

    'Main array
    Dim mainArray(1 To 5) As Double
    mainArray(1) = 1
    mainArray(2) = 2
    mainArray(3) = 3
    mainArray(4) = 4
    mainArray(5) = 5
    
    'Sub array 1
    Dim subArray1(1 To 2) As Double
    subArray1(1) = 2
    subArray1(2) = 3
    
    'Sub array 2
    Dim subArray2(1 To 2) As Double
    subArray2(1) = 3
    subArray2(2) = 2
    
    Debug.Assert SearchArrayInAnother(subArray1, mainArray) = True
    Debug.Assert SearchArrayInAnother(subArray2, mainArray) = False

End Sub

11.4 Static Arrays (Multidimensional)

Multidimensional arrays are useful for storing data in multiple dimensions, such as tables, matrices, and cubes.

A dimension is a separate index or range of indices in an array.

Two-dimensional arrays (matrices) are common in spreadsheet applications, where data is organized in rows and columns.
Three-dimensional arrays (cubes) are common in scientific computing and engineering applications.

VBA supports up to 60 dimensions. However, using more than three dimensions can make the code complex and difficult to manage. The first and last index are identified in the declaration per dimension. For example:

Dim myArray(1, 1) As Integer (four integers (2x2): D1=0,1 and D2=0,1)
Dim myArray(1 To 2, 1 To 2) As Integer (four integers (2x2): D1=1,2 and D2=1,2)
Dim myArray(1 To 2, 1 To 2, 1 To 2) As Integer (eight integers (2x2x2): D1=1,2, D2=1,2, and D3=1,2)

11.4.1 Example - 2D Static Array

Option Explicit

Sub Example2DStaticArray()
    

    'Declare a 2D array with 3 rows and 4 columns
    Dim twoDArray(1 To 3, 1 To 4) As Integer
    
    'Populate elements of the 2D array
    twoDArray(1, 1) = 10
    twoDArray(1, 2) = 20
    twoDArray(2, 1) = 30
    twoDArray(2, 2) = 40
    
    'Access an element of the 2D array
    Dim value2D As Integer
    value2D = twoDArray(2, 1) 'Accesses the element (30)

End Sub

11.4.2 Example - 3D Static Array

Option Explicit
Sub Example3DStaticArray()

    'Declare a 3D array (2x2x3)
    Dim threeDArray(1, 1, 2) As Integer
    
    'Populate elements of the 2D array
    threeDArray(0, 0, 0) = 0
    threeDArray(0, 0, 1) = 0
    threeDArray(0, 0, 2) = 0
    threeDArray(0, 1, 0) = 255
    threeDArray(0, 1, 1) = 255
    threeDArray(0, 1, 2) = 255
    threeDArray(1, 0, 0) = 255
    threeDArray(1, 0, 1) = 255
    threeDArray(1, 0, 2) = 255
    threeDArray(1, 1, 0) = 0
    threeDArray(1, 1, 1) = 0
    threeDArray(1, 1, 2) = 0
    
    'Print all values
    Dim i As Integer, j As Integer, k As Integer
    
    For i = 0 To 1
        For j = 0 To 1
            For k = 0 To 2
                Debug.Print _
                    "[" & i & ", " & j & ", " & k & "] = " _ 
                    & threeDArray(i, j, k)
            Next k
        Next j
    Next i

End Sub

11.5 Exercises (Static Arrays)

11.5.1 Print Matrix

Create a subroutine that takes a 2D array (matrix) as input and prints its elements in a human-readable format, such as rows and columns, in the Immediate Window.

’Tip: Use this sub throughout the module to print the matrices!

Option Explicit

Sub PrintMatrix(matrix() As Double)

    Dim u1 As Integer, _
        u2 As Integer, _
        l1 As Integer, _
        l2 As Integer
    
    'Use LBound(matrix, 1) and UBound(matrix, 1) to retrieve
    'the lower and upper bounds of the **first** dimension
    
    'Use LBound(matrix, 2) and UBound(matrix, 2) to retrieve
    'the lower and upper bounds of the **second** dimension
    
    u1 = UBound(matrix, 1)
    u2 = UBound(matrix, 2)
    l1 = LBound(matrix, 1)
    l2 = LBound(matrix, 2)
    
    Dim i As Integer, j As Integer
    
    For i = l1 To u1
        For j = l2 To u2
            'Add code here to print the matrix!
        Next j
    Next i
    
End Sub


Sub TestPrintMatrix()

    Dim matrix(1 To 2, 1 To 3) As Double
    
    ' Initialize the matrix with values
    matrix(1, 1) = 1
    matrix(1, 2) = 2
    matrix(1, 3) = 3
    matrix(2, 1) = 4
    matrix(2, 2) = 5
    matrix(2, 3) = 6
    
    ' Call the PrintMatrix subroutine to display the matrix
    Debug.Print "Matrix:"
    PrintMatrix matrix
    
    'Matrix:
    'Element (1, 1): 1
    'Element (1, 2): 2
    'Element (1, 3): 3
    'Element (2, 1): 4
    'Element (2, 2): 5
    'Element (2, 3): 6

End Sub

11.5.2 Matrix Addition

Create a function that takes two 2D arrays (matrices) as input and returns a new matrix containing the element-wise addition of the input matrices. Ensure that the matrices have the same dimensions.

Option Explicit

Function MatrixAddition(_ 
        matrix1() As Double, _
        matrix2() As Double) As Double()
    'Add code here!
End Function

Sub TestMatrixAddition()

    Dim matrixA(1 To 2, 1 To 2) As Double
    Dim matrixB(1 To 2, 1 To 2) As Double
    
    Dim result() As Double
    
    matrixA(1, 1) = 1
    matrixA(1, 2) = 2
    matrixA(2, 1) = 3
    matrixA(2, 2) = 4
    
    matrixB(1, 1) = 5
    matrixB(1, 2) = 6
    matrixB(2, 1) = 7
    matrixB(2, 2) = 8
    
    result = MatrixAddition(matrixA, matrixB)
    
    ' Display the result
    Debug.Print "Matrix Addition Result:"
    PrintMatrix result

End Sub

11.5.3 Transpose Matrix

Create a function that takes a 2D array (matrix) as input and returns a new matrix that is the transpose of the input matrix. The transpose swaps rows with columns.

Option Explicit

Function TransposeMatrix(matrix() As Double) As Double()
    'Add code here!
End Function

Sub TestTransposeMatrix()

    Dim matrix(1 To 2, 1 To 3) As Double
    Dim transposed() As Double
    
    matrix(1, 1) = 1
    matrix(1, 2) = 2
    matrix(1, 3) = 3
    matrix(2, 1) = 4
    matrix(2, 2) = 5
    matrix(2, 3) = 6
    
    PrintMatrix matrix
    
    transposed = TransposeMatrix(matrix)
    
    ' Display the transposed matrix
    Debug.Print "Transposed Matrix:"
    PrintMatrix transposed
End Sub

11.5.4 Matrix Multiplication

Create a function that takes two 2D arrays (matrices) as input and returns a new matrix that is the result of matrix multiplication. Ensure that the matrices have compatible dimensions for multiplication (e.g., the number of columns in the first matrix must match the number of rows in the second matrix).

Option Explicit

Function MatrixMultiplication(matrix1() As Double, matrix2() As Double) As Double()
    'Add code here!
End Function


Sub TestMatrixMultiplication()

    Dim matrixA(1 To 2, 1 To 2) As Double
    Dim matrixB(1 To 2, 1 To 2) As Double
    Dim result() As Double
    
    matrixA(1, 1) = 1
    matrixA(1, 2) = 2
    matrixA(2, 1) = 3
    matrixA(2, 2) = 4
    
    matrixB(1, 1) = 5
    matrixB(1, 2) = 6
    matrixB(2, 1) = 7
    matrixB(2, 2) = 8
    
    result = MatrixMultiplication(matrixA, matrixB)
    
    'Display the result
    Debug.Print "Matrix Multiplication Result:"
    PrintMatrix result
End Sub

11.5.5 Sum of Matrix Rows

Create a function that takes a 2D array (matrix) as input and returns a 1D array containing the sum of each row in the matrix.

Option Explicit

Function SumMatrixRows(matrix() As Double) As Double()
    'Add code here!
End Function


Sub TestSumMatrixRows()
    Dim matrix(1 To 3, 1 To 4) As Double
    Dim rowSums() As Double
    
    matrix(1, 1) = 1
    matrix(1, 2) = 2
    matrix(1, 3) = 3
    matrix(1, 4) = 4
    matrix(2, 1) = 5
    matrix(2, 2) = 6
    matrix(2, 3) = 7
    matrix(2, 4) = 8
    matrix(3, 1) = 9
    matrix(3, 2) = 10
    matrix(3, 3) = 11
    matrix(3, 4) = 12
    
    rowSums = SumMatrixRows(matrix)
    
    ' Display the row sums
    Debug.Print "Row Sums:"
    PrintVector rowSums 'Create PrintVector (see module Vba6_DataStructArraysStatic1D) to see the result
End Sub

11.6 Dynamic Arrays

No preset number of elements.
Declared with an empty set of parentheses.
Redimensioned on runtime using command ReDim.
In multi-dimension arrays, only last dimension can be redimensioned.

11.6.1 Preserving Elements in Dynamic Arrays

ReDim wipes out any values currently stored in the array elements.
Use ReDim Preserve to preserve the current elements.

Option Explicit

Sub ExampleDynamicArray()
    
    'Declare a dynamic array (size can be changed later)
    Dim dynamicArray() As Integer
    
    'Resize the dynamic array to have 10 elements (0 to 9)
    ReDim dynamicArray(9)
    
    'Determine the lower and upper bounds of an array
    Dim lowerBound As Integer
    Dim upperBound As Integer
    
    lowerBound = LBound(dynamicArray) 'Returns 0 (lower bound)
    upperBound = UBound(dynamicArray) 'Returns 9 (upper bound)
    
    'Iterate through an array using a For loop
    Dim i As Integer
    For i = LBound(dynamicArray) To UBound(dynamicArray)
        Debug.Print dynamicArray(i)
    Next i

End Sub

11.6.2 Examples

11.6.2.1 Example - Dynamic Array Initialization Using Array Literal

In the example below, we create a dynamic array of integers using an array literal. In VBA, you can use the Array function to create an array with a fixed number of elements.

The array myArray is initialized with five integers: 10, 20, 30, 40, and 50. The Array function creates an array with the specified elements.

Notice that the array is declared as a Variant type because the Array function returns a Variant containing an array.

Option Explicit

Sub ExampleCreate1DArrayUsingCommandArray()

    'Create an array of integers using an array literal
    Dim myArray() As Variant 'Type must be Variant
    myArray = Array(10, 20, 30, 40, 50)
    
    'Access and use the array
    Dim i As Integer
    
    'Print the elements of the array
    For i = LBound(myArray) To UBound(myArray)
        Debug.Print "Element " & i + 1 & ": " & myArray(i)
    Next i
End Sub

11.6.2.2 Example - Returning Dynamic Array from a Function

In the example below, we create a function that returns a dynamic array of integers.

The function ExampleReturningDynamicArray creates a dynamic array and populates it with random integers from 1 to nElements.

Option Explicit

Function ExampleReturningArrayWith(nElements As Integer) As Double()
    
    ReDim anArray(1 To nElements) As Double
    
    Dim i As Integer
    'Assign `nElements` random numbers to the array
    For i = 1 To nElements
        anArray(i) = Rnd
    Next i
    
    ExampleReturningArrayWith = anArray
    
End Function


Sub TestExampleReturningArrayWith()

    Dim anArray() As Double
    Dim i As Integer
    anArray = ExampleReturningArrayWith(3)
    Debug.Assert UBound(anArray) - LBound(anArray) + 1 = 3
    
    anArray = ExampleReturningArrayWith(5)
    Debug.Assert UBound(anArray) - LBound(anArray) + 1 = 5
    
End Sub

11.6.2.3 Example - Declaring Huge Arrays

In the example below, we demonstrate declaring arrays with a large number of elements. The array is redimensioned to store 1k, 100k, 10m, 100m, 500m, and 1 billion values.

It is likely that your system may run out of memory when declaring such large arrays. Always consider the memory limitations of your system when working with large arrays.

Option Explicit

Sub TestDeclaringHugeArray()
    Dim hugeArray() As Double
    
    '1k values
    ReDim hugeArray(1 To 1000)
    
    '100k values
    ReDim hugeArray(1 To 100000)
    
    '10m values
    ReDim hugeArray(1 To 10000000)
    
    '100m values
    ReDim hugeArray(1 To 100000000)
    
    '500m values
    ReDim hugeArray(1 To 500000000)
    
    '1bi values
    ReDim hugeArray(1 To 1000000000)
    
End Sub

11.6.2.4 Example - Convert Delimited String into Array Using Split

In the example below, we use the Split function to convert a delimited string into an array.

The text "Apple,Banana,Cherry" is split using the comma , as the delimiter.

Then, we loop through the resulting array to print each element.

Option Explicit

Sub ExampleConvertDelimitedStringIntoArrayUsingSplit()

    'Using the Split function to split a string into an array
    Dim text As String
    text = "Apple,Banana,Cherry"
    
    Dim fruits() As String
    fruits = Split(text, ",")
    
    '`fruits` now contains an array with elements:
    '"Apple", "Banana", "Cherry"
    Dim fruit As Variant
    For Each fruit In fruits
        Debug.Print fruit
    Next
    
End Sub

Option Explicit

Sub ExampleConcatArrayElementsUsingJoin()

    'Declare a string array with 3 elements
    Dim myArray(1 To 3) As String
    
    ' Initialize the array with values
    myArray(1) = "Apple"
    myArray(2) = "Banana"
    myArray(3) = "Cherry"
    
    'Concatenate the array elements with a comma delimiter
    Dim result As String
    result = Join(myArray, ", ") 'Comma and space as the delimiter
    
    'Display the concatenated string
    Debug.Print "Concatenated String: " & result
End Sub

Option Explicit

Sub ExampleRedimensionAndPreserveVector()

    Dim myVector() As Double 'Declare an initial array
    
    'Redimension the vector to make it larger
    ReDim myVector(1 To 5)
    
    'Initialize the array with some values
    myVector(1) = 1
    myVector(2) = 2
    myVector(3) = 3
    myVector(4) = 4
    myVector(5) = 5
    
    'Display the original vector
    Debug.Print "Original Vector:"
    
    'Redimension the vector to make it larger
    ReDim Preserve myVector(1 To 7) ' Preserves existing data
    
    'Add some values to the newly created positions
    myVector(6) = 6
    myVector(7) = 7
    
    'Tip: Check the Locals window to see the results
    
End Sub

11.7 Exercises (Dynamic Arrays)

11.7.1 Dynamic Vector Initialization

Create a function that initializes a dynamic one-dimensional array with a specified size. The function should return the initialized array.

Option Explicit

Function InitializeDynamicVector(size As Long) As Double()
    'Add code here!
End Function


Sub TestInitializeDynamicVector()
    Dim myDynamicArray() As Double
    Dim size As Long

    size = 5
    myDynamicArray = InitializeDynamicVector(size)
    Debug.Assert UBound(myDynamicArray) = size
    
    'Notice that `PrintVector` is a Sub coded by you! (see Vba6_DataStructArraysStatic1D)
    Debug.Print "Dynamic Array Elements:"
    PrintVector myDynamicArray
End Sub

11.7.2 Redimension Dynamic Vector

Create a subroutine that takes a dynamic one-dimensional array as input and re-dimension it to a new specified size. Preserve the existing data while changing the size.

Option Explicit

Sub RedimensionDynamicVector( _
        dynamicArray() As Double, _
        newSize As Long)
    'Add code here!
End Sub


Sub TestRedimensionDynamicVector()
    
    Dim myDynamicArray() As Double
    Dim size As Long
    size = 3
    
    'Initial array
    myDynamicArray = InitializeDynamicVector(size)
    Debug.Assert UBound(myDynamicArray) = size
    
    'Populate initial array
    myDynamicArray(0) = 100
    myDynamicArray(1) = 200
    myDynamicArray(2) = 300
    myDynamicArray(3) = 400
    
    Debug.Print "Original Dynamic Array Elements:"
    PrintVector myDynamicArray
    
    ' Redimension the dynamic array to a new size
    RedimensionDynamicVector myDynamicArray, 7
    Debug.Assert UBound(myDynamicArray) = 7
    
    ' Display the modified elements of the dynamic array
    Debug.Print "Modified Dynamic Array Elements:"
    PrintVector myDynamicArray
End Sub

11.7.3 Vector Filtering

Create a function that takes a vector of numbers and a threshold value as input and returns an array containing the elements from the input vector that are greater than or equal to the threshold.

Option Explicit

Function FilterVector(vector() As Double, threshold As Double) As Double()
    'Add code here!
End Function


Sub TestFilterVector()
    
    'Input array
    Dim vector(1 To 5) As Double
    vector(1) = 1
    vector(2) = 2
    vector(3) = 3
    vector(4) = 4
    vector(5) = 5

    Dim filtered() As Double
    filtered = FilterVector(vector, 3)
    Debug.Assert filtered(1) = 3
    Debug.Assert filtered(2) = 4
    Debug.Assert filtered(3) = 5
    
End Sub

11.7.4 Vector Reversal

Create a function that takes a vector of numbers as input and returns a new vector containing the same numbers in reverse order.

Option Explicit

Function ReverseVector(vector() As Double) As Double()
    'Add code here!
End Function


Sub TestReverseVector()

    'Input array
    Dim vector(1 To 5) As Double
    vector(1) = 1
    vector(2) = 2
    vector(3) = 3
    vector(4) = 4
    vector(5) = 5
    
    Dim reversed() As Double
    
    reversed = ReverseVector(vector)
    Debug.Assert reversed(1) = 5
    Debug.Assert reversed(2) = 4
    Debug.Assert reversed(3) = 3
    Debug.Assert reversed(4) = 2
    Debug.Assert reversed(5) = 1
End Sub

11.7.5 Vector Sorting

Create a function that takes a vector of numbers as input and returns a new vector containing the same numbers sorted in ascending order.

Option Explicit

Function SortVector(vector() As Double) As Double()
    ' Your code here
End Function


Sub TestSortVector()
    
    'Input array
    Dim vector(1 To 5) As Double
    vector(1) = 3
    vector(2) = 1
    vector(3) = 2
    vector(4) = 5
    vector(5) = 4
    
    Dim sorted() As Double
    
    sorted = SortVector(vector)
    Debug.Assert sorted(1) = 1
    Debug.Assert sorted(2) = 2
    Debug.Assert sorted(3) = 3
    Debug.Assert sorted(4) = 4
    Debug.Assert sorted(5) = 5
End Sub

11.7.6 Vector Sum

Create a function that takes two vectors of the same length as input and returns a new vector containing the element-wise sum of the input vectors.

Option Explicit

Function vectorSum( _
        vector1() As Double, _
        vector2() As Double) As Double()
    'Add code here!
End Function


Sub TestVectorSum()
    
    'Input array 1
    Dim vector1(1 To 5) As Double
    vector1(1) = 1
    vector1(2) = 2
    vector1(3) = 3
    vector1(4) = 4
    vector1(5) = 5
    
    'Input array 2
    Dim vector2(1 To 5) As Double
    vector2(1) = 5
    vector2(2) = 4
    vector2(3) = 3
    vector2(4) = 2
    vector2(5) = 1
    
    Dim vectorSumResult() As Double
    vectorSumResult = vectorSum(v1Double, v2Double)
    Debug.Assert vectorSumResult(2) = 6
    Debug.Assert vectorSumResult(3) = 6

End Sub

11.7.7 Element-Wise Multiplication

Create a function that takes two vectors of the same length as input and returns a new vector containing the element-wise multiplication of the input vectors.

Option Explicit

Function ElementwiseMultiply( _
        vector1() As Double, _
        vector2() As Double) As Double()
    'Add code here!
End Function


Sub TestElementwiseMultiply()

    'Input array 1
    Dim vector1(1 To 3) As Double
    vector1(1) = 1
    vector1(2) = 2
    vector1(3) = 3
    
    'Input array 2
    Dim vector2(1 To 3) As Double
    vector2(1) = 4
    vector2(2) = 5
    vector2(3) = 6
    
    Dim elementwiseMultiplyResult() As Double
    elementwiseMultiplyResult = ElementwiseMultiply(vector1, vector2)
    Debug.Assert elementwiseMultiplyResult(1) = 4
    Debug.Assert elementwiseMultiplyResult(2) = 10
    Debug.Assert elementwiseMultiplyResult(3) = 18
    
End Sub

11.7.8 Concatenate Vectors

Create a function that takes two vectors as input and returns a new vector that is the result of concatenating the elements of the second vector to the end of the first vector.

Option Explicit

Function ConcatenateVectors( _
        vector1() As Double, _
        vector2() As Double) As Double()
    'Add code here!
End Function


Sub TestConcatenateVectors()
    
    'Input array 1
    Dim vector1(1 To 5) As Double
    vector1(1) = 1
    vector1(2) = 2
    vector1(3) = 3
    vector1(4) = 4
    vector1(5) = 5
    
    'Input array 2
    Dim vector2(1 To 5) As Double
    vector2(1) = 6
    vector2(2) = 7
    vector2(3) = 8
    vector2(4) = 9
    vector2(5) = 10
    
    Dim concatenated() As Double
    concatenated = ConcatenateVectors(vector1, vector2)
    Debug.Assert UBound(concatenated) = 10
    Debug.Assert concatenated(4) = 4
    Debug.Assert concatenated(5) = 5
    Debug.Assert concatenated(6) = 6
    Debug.Assert concatenated(7) = 7
    
End Sub

11.7.9 Find Intersection of Vectors

Create a function that takes two vectors of numbers as input and returns a new vector containing the elements that are common to both input vectors (the intersection).

Option Explicit

Function FindVectorIntersection( _
        vector1() As Double, _
        vector2() As Double) As Double()
    'Add code here!
End Function


Sub TestFindVectorIntersection()

    'Input array 1
    Dim vector1(1 To 5) As Double
    vector1(1) = 1
    vector1(2) = 2
    vector1(3) = 3
    vector1(4) = 4
    vector1(5) = 5
    
    'Input array 2
    Dim vector2(1 To 5) As Double
    vector2(1) = 3
    vector2(2) = 4
    vector2(3) = 5
    vector2(4) = 6
    vector2(5) = 7
    
    
    'Intersection between vector1 and vector2
    Dim intersection() As Double
    intersection = FindVectorIntersection(vector1, vector2)
    
    Debug.Assert UBound(intersection) = 3
    Debug.Assert intersection(1) = 3
    Debug.Assert intersection(2) = 4
    Debug.Assert intersection(3) = 5

End Sub

0 is the default lower index for arrays. If you want it to be 1, include Option Base 1 at the top of your code (a.k.a. Declarations section).↩︎