11 Data Structures

A data structure is a method of organizing and storing data in a computer so that it can be accessed and manipulated efficiently. They provide a level of abstraction, allowing programmers to manage data without needing to worry about the low-level implementation details.

Choosing the appropriate data structure is essential because different structures offer varying performance in terms of memory usage, speed, and ease of use. For example, suppose you are storing a list of customer orders, each containing specific details. If you have n orders, consider the following factors:

Access. How quickly can you find a specific order?
Memory. How much memory is required to store all the orders?
Insertion and deletion. How easy is it to add or remove an order?

The complexity of these operations will depend on the data structure you choose. Different data structures are optimized for specific tasks and involve trade-offs, making it critical to match the structure to the needs of the operations you intend to perform.

Big-O Notation

To compare the performance of different data structures, computer scientists use the Big-O notation. It is a mathematical notation that describes the describes the performance of algorithms and data structures in terms of their time and space complexity when the input size approaches infinity. For example, an algorithm with a time complexity of O(n) means that the time taken by the algorithm grows linearly with the input size n. An algorithm with a time complexity of O(n^2) grows quadratically with the input size. If you are interested in learning more about Big-O notation, check out the Big-O Cheat Sheet.

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.
User-defined types (UDTs): Custom data types created by the programmer.

In this course, we will focus on arrays, which are the most common data structure in VBA. It is highly recommended, however, that you explore the other data structures to understand their advantages and limitations.

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, as shown in Listing 11.1.

Listing 11.1: Example of calculating the average of 5 students’ grades using individual variables passed as arguments. Notice that the function signature has 5 arguments, one for each student’s grade.

Option Explicit

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

    AvgGradesIndividualVariables = ( _
                gradeStudent1 + _
                gradeStudent2 + _
                gradeStudent3 + _
                gradeStudent4 + _
                gradeStudent5 _
                ) / 5
                
End Function

Sub TestAvgGrades()

    Debug.Assert AvgGradesIndividualVariables(10, 20, 30, 40, 50) = 30

End Sub

However, this approach is not scalable: as the number of students increases, the number of arguments grows linearly, resulting in a cumbersome and error-prone function signature. If you have 100 students, you would need to pass 100 arguments, which is impractical. This is where arrays come in handy.

Arrays are a fundamental data structure in programming that allow you to store multiple values of the same data type under a single name. Each value in an array is identified by an index, which is a unique number that represents its position in the array.

In Listing 11.2, instead of passing each grade as a separate argument, we pass a static array of grades gradesStudent to the function and the number of grades n to calculate the average. Notice that the function signature changes to accept an array of doubles. The Sub TestAvgOfGradesArray initializes the array with 5 grades and calls the function to calculate the average. This way, you can pass any number of grades to the function (as long as there is enough memory).

Listing 11.2: Example of calculating the average of 4 students’ grades using a static array passed as an argument. We also pass the number of grades n to calculate the average.

Option Explicit

Function AvgOfGradesArray( _
        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
    
    AvgOfGradesArray = sum / n
               
End Function

Sub  TestAvgOfGradesArray()

    Dim gradesStudent(1 To 5) As Double
    
    'Assign values to the array (populating)
    gradesStudent(1) = 10
    gradesStudent(2) = 20
    gradesStudent(3) = 30
    gradesStudent(4) = 40
    gradesStudent(5) = 50
    
    Debug.Assert AvgOfGradesArray(gradesStudent, 5) = 30
    
End Sub

11.2.1 Array Length

Typically, programming languages provide built-in utility functions that perform frequent array operations. 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. Therefore, the length of an array called myArray can be calculated as (UBound(myArray) - LBound(myArray)) + 1.

In Listing 11.3, we build the utility function getArrayLen that calculates the length of an array and test it with an array of grades.

Listing 11.3: Example of calculating the length of a static array using the getArrayLen function. Notice that the function receives a variant as an argument (see more about passing arrays in VBA in Section 11.2.3.7 and Section 11.2.3.8).

Function getArrayLen(a As Variant) As Integer

    'Calculate the number of elements
    getArrayLen = (UBound(a) - LBound(a)) + 1

End Function

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
    elementCount = getArrayLen(myVector)
    
    'Print the number of elements
    Debug.Print "Number of elements in the array: " & elementCount
    
End Sub

11.2.2 First Element Index

The lower bound of an array is the index of the first element, and the upper bound is the index of the last element. 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). 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)).

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.

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

11.2.3 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. The elements are accessed using a single index and are stored in a contiguous block of memory. “Static” in this context means that the size of the array is fixed and specified at the time of declaration.

The standard syntax for declaring a one-dimensional array is:

Dim myVector(lowerBound To upperBound) As DataType

where:

myVector is the name of the array.
lowerBound specifies the lower index of the array.
upperBound specifies the upper index of the array.
As Integer specifies the data type of the elements in the array.

Examples:

Dim myVector(1 To 5) As Integer (five integers indexed from 1 to 5).
Dim myVector(0 To 4) As Integer (five integers indexed from 0 to 4).
Dim myVector(2 To 5) As Integer (four integers indexed from 2 to 5).
Dim myVector(4) As Integer (five integers indexed from 0 to 4).

11.2.3.1 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).

When you declare an array, VBA initializes it with default values based on the data type. For numeric data types (e.g., Integer, Double), the default value is 0. For strings, the default value is an empty string "". For boolean values, the default value is False. For variant data types, the default value is Empty.

In Listing 11.4, we initialize 4 different arrays with different data types and print their default values.

Listing 11.4: Example of initializing arrays with different data types and printing their default values.

Option Explicit

Sub ArrayDefaultInitialization()

    Dim intArray(1 To 4) As Integer
    Dim doubleArray(1 To 4) As Double
    Dim strArray(1 To 4) As String
    Dim boolArray(1 To 4) As Boolean
    Dim varArray(1 To 4) As Variant
    
    Debug.Print intArray(1)
    Debug.Print doubleArray(1)
    Debug.Print strArray(1)
    Debug.Print boolArray(1)
    Debug.Print varArray(1)
    
End Sub

11.2.3.2 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.3.3 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.3.4 Looping Through an Array

In Listing 11.5, 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.

Listing 11.5

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.3.5 Configuring the Loop Iteration with Lower and Upper Bound Functions

In Listing 11.6, 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 like in Listing 11.5. 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).

Listing 11.6: Example of looping through an array using the LBound and UBound functions to determine the lower and upper bounds. The lower bound is used to configure the initial value of the iteration variable i, and the upper bound is used to set the loop limit.

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

11.2.3.6 Returning an Array From a Function

In Listing 11.7, 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.

Listing 11.7: Example of a function that returns an array of integers with the multiples of a given number. After calling the function, the test function checks if the first, second, and fifth elements of the array are equal to 3, 6, and 15, respectively.

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.3.7 Receiving Arrays with a Specific Data Type

An array can be received with a specific data type, such as integer, double, or string. In Listing 11.8, we create a function ExampleReceivingArray that receives an array of integers and calculates the sum of the elements. Notice that the function signature specifies the argument as an array of integers using anArray() As Integer. The parentheses () after anArray indicate that the function receives an array.

The Sub TestReceivingArray initializes an array of integers with three elements and calls the function ExampleReceivingArray to calculate the sum of the elements. The function will only accept arrays of integers.

Listing 11.8: Example of a function that receives an array of integers. The test function initializes an array with three elements and calls the function to calculate the sum of the elements. Notice that we do not specify the size of the array in the function signature (i.e., we do write ExampleREceivingArray(anArray(1 To 3) As Integer)), as the function can receive arrays of any size.

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.2.3.8 Receving Arrays as Variants

An array can also be received as a Variant data type, which is a generic data type that can store any type of data. In Listing 11.9, we create a function ExampleReceivingArrayAsVariant that receives an array as a Variant and calculates the sum of the elements. The function signature specifies the argument as a Variant using anArray As Variant (without parentheses). If you inspect the anArray argument inside the function using the Locals window, you will see that it is a Variant containing an array of integers (if you pass an array of integers) or an array of doubles (if you pass an array of doubles). In the first case, the value type is Variant/Integer, and in the second case, the value type is Variant/Double.

Listing 11.9: Example of a function that receives an array as a Variant. The test function calls the function with an array of doubles and an array of integers to calculate the sum of the elements. Notice that the function can receive arrays of any size and data type. The return type is a double to accommodate the sum of the elements.

Option Explicit

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

Sub TestReceivingArrayAsVariant()
    
    Dim anArrayOfDoubles(1 To 3) As Double

    anArrayOfDoubles(1) = 1.1
    anArrayOfDoubles(2) = 2.3
    anArrayOfDoubles(3) = 3.1
    
    Debug.Assert ExampleReceivingArrayAsVariant(anArrayOfDoubles) = 6.5
    
    Dim anArrayOfIntegers(1 To 3) As Integer

    anArrayOfIntegers(1) = 1
    anArrayOfIntegers(2) = 2
    anArrayOfIntegers(3) = 3
    
    Debug.Assert ExampleReceivingArrayAsVariant(anArrayOfIntegers) = 6
    
End Sub

11.2.4 Static, Multidimensional Arrays

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.2.4.1 2D Static Array

In Listing 11.10, we declare a two-dimensional array of integers with 3 rows and 4 columns. We then populate the array with values and access an element using its indices.

After assigning values to the array, we would have a matrix like:

\[ \begin{bmatrix} 10 & 20 & 0 & 0 \\ 30 & 40 & 0 & 0 \\ 0 & 0 & 0 & 0 \\ \end{bmatrix} \]

Listing 11.10: Example of declaring and populating a two-dimensional array in VBA with 3 rows and 4 columns. This can be visualized as a 3x4 matrix.

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.2.4.2 3D Static Array

In Listing 11.11, we declare a three-dimensional array of integers with dimensions 2x2x3. We then populate the array with values and access an element using its indices.

After assigning values to the array, we would have a cube like:

\[ \begin{bmatrix} \begin{bmatrix} 0 & 0 & 0 \end{bmatrix} & \begin{bmatrix} 255 & 255 & 255 \end{bmatrix} \\ \begin{bmatrix} 255 & 255 & 255 \end{bmatrix} & \begin{bmatrix} 0 & 0 & 0 \\ \end{bmatrix} \end{bmatrix} \]

Which may represent the RGB channels of a 2x2 image, like a black and white checkerboard, as shown in Figure 11.2.

Figure 11.2: 2x2 image with 4 squares, two black (0,0,0) at positions (0,0) and (1,1), and two white (255,255,255) at position (0,1) and (1,0).

Listing 11.11: Example of declaring and populating a three-dimensional array in VBA with dimensions 2x2x3. This can be visualized as a 2x2x3 cube or, if the third dimension represents color channels, as a 2x2 image with 3 color channels (0=Red, 1=Green, 2=Blue). Using RGB values (numbers from 0 to 255), we can represent any color (e.g., black = 0,0,0 and white = 255,255,255).

Option Explicit

Sub Example3DStaticArray()

    'Declare a 3D array (2x2x3)
    Dim threeDArray(1, 1, 2) As Integer
    
    'Populate elements of the D 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

Changing the Color of Spreadsheet Cells

If you are working with Excel, you can use VBA to change the color of cells in a spreadsheet. In Listing 11.11, you can add the following line to the loop to change the color of the cells in an Excel spreadsheet (replace the For k loop):

Dim r As Integer, g As Integer, b As Integer
r = threeDArray(i, j, 0)
g = threeDArray(i, j, 1)
b = threeDArray(i, j, 2)
Cells(i + 1, j + 1).Interior.Color = RGB(r, g, b)

Interested in learning more about manipulating pixels? Check Excel/VBA Photoshop.

11.3 Exercises (One-Dimensional 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
    
    'Possible output 1: (1, 2, 3, 4, 5)

    'Possible output 2:
    
    '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 Exercises (Multidimensional Arrays)

11.4.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
    
    'Possible output 1:

    'Element (1, 1): 1
    'Element (1, 2): 2
    'Element (1, 3): 3
    'Element (2, 1): 4
    'Element (2, 2): 5
    'Element (2, 3): 6

    'Possible output 2:

    '1 2 3
    '4 5 6

End Sub

11.4.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.4.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.4.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.4.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 to see the result
End Sub

11.5 Dynamic, One-Dimensional Arrays (Vectors)

In VBA, dynamic arrays are arrays with no preset number of elements. They are useful when the number of elements is not known in advance or when the size of the array needs to be adjusted during program execution. Therefore, different from static arrays, dynamic arrays do not have a fixed size at compile time. The size can be adjusted dynamically, at runtime, using the ReDim statement.

Run time vs. Compile time

Compile time: When the code is compiled, the VBA compiler translates the code into machine code to be executed by the computer. When using static arrays, the size of the array is known at compile time whereas dynamic arrays have no specific size at compile time.
Run time: Refers to the time when the code is executed (after being compiled). Dynamic arrays can be resized during run time using the ReDim statement. For example, the user can input the number of elements to be stored in the array, and the array can be resized accordingly.

The standard way to declare a dynamic array is as follows:

Dim dynamicArray() As DataType

where:

dynamicArray: The name of the array.
DataType: The data type of the elements in the array.
() indicates that the array is dynamic (no lower or upper bounds are specified).

In Listing 11.12, we declare a dynamic array, populate it with random values, access, modify, and print the elements. The array is resized using the ReDim statement to have 10 elements (from 0 to 9).

Listing 11.12: Example of declaring a dynamic array, populating it with values, and modifying the odd elements.

Option Explicit

Sub ExampleDynamicArray()
    
    'Declare a dynamic array (size can be changed later)
    Dim dynamicArray() As Integer

    'Ask the user for the number of elements
    Dim nElements As Integer
    
    'Resize the dynamic array to have nElements.
    ReDim dynamicArray(0 To 9) As Integer
    
    Dim i As Integer

    'Populate the array with random values
    For i = LBound(dynamicArray) To UBound(dynamicArray)
        dynamicArray(i) = WorksheetFunction.RandBetween(1, 100)
    Next i

    'Iterate through an array and print the elements
    For i = LBound(dynamicArray) To UBound(dynamicArray)
        Debug.Print dynamicArray(i)
    Next i

    'Iterate through an array transform the odd elements to even
    For i = LBound(dynamicArray) To UBound(dynamicArray)
        If dynamicArray(i) Mod 2 <> 0 Then
            dynamicArray(i) = dynamicArray(i) + 1
        End If
    Next i

    'Iterate through an array and print the elements
    For i = LBound(dynamicArray) To UBound(dynamicArray)
        Debug.Print i, dynamicArray(i)
    Next i

End Sub

In Listing 11.13, we declare a dynamic array and ask the user for the number of elements. The array is then resized at runtime to have nElements elements. Notice that if the array was static, we would have to declare it with a fixed size, for example, Dim dynamicArray(1 To 100) As String.

Listing 11.13: Example of declaring a dynamic array and resizing it based on user input. The array is populated with random substrings from a famous sentence (used for testing purposes in font design since it contains all letters of the alphabet).

Sub ExampleDynamicArray()
    
    'Declare a dynamic array (size can be changed later)
    Dim dynamicArray() As String

    'Ask the user for the number of elements
    Dim nElements As Integer
    nElements = InputBox("Enter the number of elements:")
    
    'Resize the dynamic array to have nElements
    ReDim dynamicArray(1 To nElements) As String
    
    'Iterate through an array using a for loop
    Dim i As Integer
    Dim original As String

    original = "The quick brown fox jumps over the lazy dog"
    
    For i = LBound(dynamicArray) To UBound(dynamicArray)
        
        'Extract a random substring from the original string
        Dim length As Integer
        length = WorksheetFunction.RandBetween(1, Len(original))
        dynamicArray(i) = Mid(original, 1, length)

    Next i

    'Iterate through an array and print the elements
    For i = LBound(dynamicArray) To UBound(dynamicArray)
        Debug.Print dynamicArray(i)
    Next i

End Sub

11.5.1 Returning Dynamic Array from a Function

Similar to static arrays, dynamic arrays can be returned from functions.

In the Listing 11.14, we create a function ExampleReturningDynamicArray that creates a dynamic array and populates it with random integers from 1 to nElements.

The return type of the function is specified as Double() to indicate that the function returns a dynamic array of doubles.

Listing 11.14: Example of a function that returns a dynamic array of integers. The function creates a dynamic array and populates it with random integers then returns the array.

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.5.2 Erasing All Elements

When you are done using a dynamic array, you can free the memory used by the array using the Erase statement. The dimensions of the array are removed, and the memory is released. This behavior is different from static arrays, where erase only sets the elements to their default value.

In the Listing 11.15, we demonstrate how to erase a dynamic array after using it.

Listing 11.15: Example of erasing a dynamic array after using it. The Erase statement sets all elements of the array to their default value (0 for numbers).

Option Explicit

Sub ExampleErasingDynamicArray()
    
    'Declare a dynamic array
    Dim dynamicArray() As Double
    
    'Resize the dynamic array to have 10 elements
    ReDim dynamicArray(1 To 10) As Double
    
    'Populate the array with random values
    Dim i As Integer
    For i = LBound(dynamicArray) To UBound(dynamicArray)
        dynamicArray(i) = Rnd
    Next i
    
    'Print the elements of the array
    For i = LBound(dynamicArray) To UBound(dynamicArray)
        Debug.Print dynamicArray(i)
    Next i
    
    'Erase the dynamic array
    Erase dynamicArray

    'The array is no longer accessible
    'Debug.Print UBound(dynamicArray) 'This would raise an error
    'Debug.Print dynamicArray(1) 'This would raise an error
    
End Sub

11.5.3 Declaring Huge Arrays: Memory Considerations

In the Listing 11.16, 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.

To test the memory usage, you can put breakpoints at each ReDim statement and check the memory usage in the Task Manager (Ctrl + Shift + Esc on Windows) or Activity Monitor (macOS). See how much RAM memory Excel consumes when declaring arrays with a large number of elements.

Out of Memory

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

Listing 11.16: Testing the declaration of arrays with an increasing number of elements. Put breakpoints to check the memory usage at each declaration. You can check if Excel space consumption increases by abou 763MB after declaring an array with 100 million elements (100 million Double elements ~ 763MB, since 1MB = 131,072 Double elements). The Erase statement is used to free the memory used by the array after each test for consistency.

Option Explicit

Sub TestDeclaringHugeArray()
    Dim hugeArray() As Double
    
    '1k values
    ReDim hugeArray(1 To 1000)

    Erase hugeArray
    
    '100k values
    ReDim hugeArray(1 To 100000)
    
    Erase hugeArray

    '10m values
    ReDim hugeArray(1 To 10000000)

    Erase hugeArray
    
    '100m values
    ReDim hugeArray(1 To 100000000)

    Erase hugeArray
    
    '500m values
    ReDim hugeArray(1 To 500000000)

    Erase hugeArray
    
    '1bi values
    ReDim hugeArray(1 To 1000000000)
    
End Sub

11.5.4 Redimensioning Arrays

The ReDim statement can be used to resize an array. The new size can be larger or smaller than the current size. The resized array, however, will lose its current contents.

In the Listing 11.17, we demonstrate how to redimension an array to have 10 elements and then resize it to have 5 elements.

Listing 11.17: Example of resizing an array from 10 to 5 elements using the ReDim statement. After resizing, the values of the array are lost (reset to zero, the default value for integers).

Option Explicit

Sub ExampleRedimensioningArray()
    
    'Declare a dynamic array with 10 elements
    Dim dynamicArray() As Integer
    ReDim dynamicArray(1 To 10) As Integer

    'Add elements
    Dim i As Integer
    For i = 1 To 10
        dynamicArray(i) = i
    Next i
    
    'Resize the array to have 5 elements
    ReDim dynamicArray(2 To 6) As Integer
    
    'The array now has 5 elements
    Debug.Print "Number of elements: " & UBound(dynamicArray) - LBound(dynamicArray) + 1

    'Print the elements of the array
    For i = LBound(dynamicArray) To UBound(dynamicArray)
        Debug.Print i, dynamicArray(i)
    Next i
    
End Sub

11.5.5 Redimensioning Arrays and Preserving Data

If you want to resize an array and preserve its data, you can use the Preserve keyword with the ReDim statement.

In the Listing 11.18, we demonstrate how to resize an array and preserve its data.

Listing 11.18: Example of resizing an array from 10 to 5 elements using the ReDim Preserve statement. The data is preserved, and the array now has 5 elements (from 1 to 6). The lower bound has to coincide with the original lower bound of the array (i.e., 1). Therefore, you cannot change the lower bound of the array when using ReDim Preserve (e.g., from 2 to 6).

Option Explicit

Sub ExampleRedimensioningArrayPreserve()
    
    'Declare a dynamic array with 10 elements
    Dim dynamicArray() As Integer
    ReDim dynamicArray(1 To 10) As Integer

    'Add elements
    Dim i As Integer
    For i = 1 To 10
        dynamicArray(i) = i
    Next i
    
    'Resize the array to have 5 elements and preserve the data
    ReDim Preserve dynamicArray(1 To 6) As Integer
    
    'The array now has 5 elements
    Debug.Print "Number of elements: " & UBound(dynamicArray) - LBound(dynamicArray) + 1
    
    'Print the elements of the array
    For i = LBound(dynamicArray) To UBound(dynamicArray)
        Debug.Print i, dynamicArray(i)
    Next i
    
End Sub

11.6 Dynamic, Multidimensional Arrays

Dynamic arrays can also be multidimensional. In the Listing 11.19, we create a dynamic two-dimensional array of integers with dimensions 3x4.

The array is resized using the ReDim statement to have 3 rows and 4 columns. The array is then populated with random values and printed using nested loops. Then, we iterate through the array and print the elements.

Notice that, UBound(dynamic2DArray, 1) and UBound(dynamic2DArray, 2) are used to get the upper bounds of the first and second dimensions, respectively, whereas LBound(dynamic2DArray, 1) and LBound(dynamic2DArray, 2) are used to get the lower bounds of the first and second dimensions, respectively. The second argument of the LBound and UBound functions specifies the dimension of the array that you want to get the bounds for.

Listing 11.19: Example of declaring a dynamic two-dimensional array of integers with dimensions 3x4. The array is resized using the ReDim statement and populated with random values.

Option Explicit

Sub ExampleDynamic2DArray()
    
    'Declare a dynamic 2D array with 3 rows and 4 columns
    Dim dynamic2DArray() As Integer
    
    'Resize the dynamic 2D array to have 3 rows and 4 columns
    ReDim dynamic2DArray(1 To 3, 1 To 4) As Integer
    
    'Populate the array with random values
    Dim i As Integer, j As Integer
    For i = LBound(dynamic2DArray, 1) To UBound(dynamic2DArray, 1)
        For j = LBound(dynamic2DArray, 2) To UBound(dynamic2DArray, 2)
            dynamic2DArray(i, j) = WorksheetFunction.RandBetween(1, 100)
        Next j
    Next i

    'Iterate through the 2D array and print the elements
    For i = LBound(dynamic2DArray, 1) To UBound(dynamic2DArray, 1)
        For j = LBound(dynamic2DArray, 2) To UBound(dynamic2DArray, 2)
            Debug.Print dynamic2DArray(i, j)
        Next j
    Next i

End Sub

11.6.1 Redimensioning Dynamic, Multidimensional Arrays

Dynamic, multidimensional arrays can also be resized using the ReDim statement. However, the ReDim statement can only change the size of the last dimension of the array. For example, if you have a table where you consider the rows as the first dimension and the columns as the second dimension, you can only change the number of columns (second dimension) using ReDim Preserve. If you wanted to change the number of rows (first dimension), you could:

Create a new array with the desired number of rows and copy the elements. This is not efficient for large arrays.
Consider columns as the first dimension and rows as the second dimension.

In Listing 11.21, we demonstrate how to resize a dynamic two-dimensional array of data where the first dimension represents the attributes (e.g., experiment number, objective function value, runtime) and the second dimension represents the experiments. The array stores the results of an experiment with 3 attributes (rows) and 4 experiments (columns). The array is resized to add a new experiment (column).

Listing 11.20: Function to print a two-dimensional array with a fixed width of 10 characters for each element.

Option Explicit

Sub Print2DArray(arr As Variant)
    
    Dim i As Integer, j As Integer
    For i = LBound(arr, 1) To UBound(arr, 1)
        Dim row As String
        row = ""
        For j = LBound(arr, 2) To UBound(arr, 2)
            'Add spaces until string has width = 10
            Dim paddedValue As String
            paddedValue = Space(10 - Len(str(arr(i, j)))) & str(arr(i, j))
            row = row & paddedValue
        Next j
        
        Debug.Print row

    Next i

End Sub

Listing 11.21: Example of resizing a dynamic two-dimensional array of integers. The array stores the results of an experiment with 3 attributes (rows) and 4 experiments (columns). The array is resized to add a new experiment (column).

Option Explicit

Sub ExampleRedimensioningDynamic2DArray()
    
    'Declare a dynamic 2D array with 3 rows and 4 columns
    ReDim dynamic2DArray(1 To 3, 1 To 4) As Variant

    'Populate the array with random values for each experiment
    Dim i As Integer
    For i = 1 To 4

        'Experiment number
        dynamic2DArray(1, i) = i

        'Objective Function
        dynamic2DArray(2, i) = WorksheetFunction.RandBetween(1, 100)

        'Runtime
        dynamic2DArray(3, i) = WorksheetFunction.RandBetween(1, 1000)

    Next i

    'Print the array before resizing
    Debug.Print "Array before resizing:"
    Print2DArray dynamic2DArray

    'Resize the array to add a new row
    ReDim Preserve dynamic2DArray(1 To 3, 1 To 5)

    Debug.Print "Array after resizing:"
    Print2DArray dynamic2DArray

    'Add more results
    For i = 2 To 5

        'Experiment number
        dynamic2DArray(1, i) = i

        'Objective Function
        dynamic2DArray(2, i) = WorksheetFunction.RandBetween(1, 100)

        'Runtime
        dynamic2DArray(3, i) = WorksheetFunction.RandBetween(1, 1000)

    Next i

    'Print the array after resizing
    Debug.Print "Array after resizing, and adding new results:"
    Print2DArray dynamic2DArray

End Sub

11.7 Exercises (Dynamic Arrays)

In the following exercises, the PrintVector subroutine (see Exercise 11.3.1) can be used to print the 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!
    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

11.7.10 Deleting Columns

Create a function that deletes the odd columns of an array. The function should take a two-dimensional array as input and return a new array where the odd columns are removed.

Option Explicit

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

Your function should use a helper function that generates a random 2D array of numbers.

Option Explicit

Function GenerateRandomMatrix(rows As Long, cols As Long) As Double()
    'Add code here!
End Function

Test your function using the following code:


Sub TestDeleteOddColumns()

    Dim matrix() As Double
    Dim newMatrix() As Double
    
    'Generate a random 2D array
    matrix = GenerateRandomMatrix(3, 4)
    
    'Print the original matrix
    Debug.Print "Original Matrix:"
    PrintMatrix matrix
    
    'Delete odd columns
    newMatrix = DeleteOddColumns(matrix)
    
    'Print the new matrix
    Debug.Print "Matrix after deleting odd columns:"
    PrintMatrix newMatrix

End Sub

Challenge: Use Excel record macro functionality to learn how to create a new spreadsheet, where you will save the new matrix after deleting the odd columns.

11.8 Useful Functions

Some functions make manipulating arrays easier. In the following, we present:

Split: Converts a delimited string into an array.
Join: Concatenates the elements of an array into a single string.
Array: Creates an array with the specified elements.

11.8.1 Convert Delimited String into Array Using Split

In Listing 11.23, we demonstrate how to convert a delimited string into an array using the Split function.

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

Then, we loop through the resulting array to print each element. This can be useful to quickly declare an array from a string with comma-separated values.

Listing 11.22: Example of converting a delimited string into an array using the Split function.

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

11.8.2 Concatenate Array Elements Using Join

The Join function concatenates the elements of an array into a single string using a specified delimiter. This can be useful when you want to display the elements of an array as a single string. For example, you can concatenate the elements of an array with a comma delimiter, as shown in Listing 11.23. This way you can easily debug and display the contents of an array.

Listing 11.23: Example of concatenating the elements of an array into a single string using the Join function. The elements are separated by a comma and space.

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

11.8.3 Dynamic Array Initialization Using Array Literal

In Listing 11.24, we create a dynamic array of integers using an array literal (i.e., a list of elements separated by commas).

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.

Listing 11.24: Example of creating a dynamic array of integers using an array literal. An array literal consists of a list of elements separated by commas. The keyword Array is used to create the 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.9 Spreadsheet-Array Manipulation

In Excel, you can populate an array with values from a range of cells. For example, consider a table with customer order information as shown in Table 11.2.

Table 11.2: Excel spreadsheet with customer order information.

	A	B	C
1	Customer	Order Date	Order Amount
2	C1	2021-01-01	100
3	C2	2021-01-02	200
4	C3	2021-01-03	300
5	C4	2021-01-04	400
6	C5	2021-01-05	500

If we want to populate a matrix with the order amounts from the table, we have to notice the following:

The range of cells containing the order amounts is A2:A6.
The first row contains the headers, so we start from the second row.
The variable type of the matrix should be set to Variant because the type of the values is mixed (strings, dates and numbers).

In the following sections, we show how to populate an array in Excel using the Cells and Range properties. We use the helper Sub PrintMatrix to print the matrix in the Immediate Window (see Listing 11.25).

Listing 11.25: The PrintMatrix subroutine prints the elements of a 2D array in the Immediate Window. Notice that the LBound and UBound functions are used to determine the lower and upper bounds of dimensions 1 (rows) and 2 (columns) of the array.

Option Explicit

Sub PrintMatrix(matrix() As Variant)

    Dim i As Integer, j As Integer
    Dim str As String
    
    For i = LBound(matrix, 1) To UBound(matrix, 1)
        str = ""
        For j = LBound(matrix, 2) To UBound(matrix, 2)
            str = str & matrix(i, j) & ","
        Next j
        Debug.Print str
    Next i
End Sub

11.9.1 Using Cells Property

In Listing 11.26, we show how to populate a matrix with the whole data from the table.

Listing 11.26: The array data is populated with the values from data in the Excel table (see Table 11.2).

Option Explicit

Sub PopulateArrayFromExcel()

    Dim data(1 To 5, 1 To 3) As Variant
    Dim str As String
    Dim i As Integer

    For i = 1 To 5
        data(i, 1) = Cells(i + 1, 1)
        data(i, 2) = Cells(i + 1, 2)
        data(i, 3) = Cells(i + 1, 3)
    Next i

    PrintMatrix data

End Sub

After the data is read from the Excel cells, it is stored in a Variant type array. If you inspect the code in the Locals window, you will see that the data is stored as in Table 11.3.

Table 11.3: Data types of the elements in the data array after populating from Excel cells (see Table 11.2)

Expression	Value	Type
data		Variant(1 to 5, 1 to 3)
data(1)		Variant(1 to 3)
data(1, 1)	“C1”	Variant/String
data(1, 2)	#2021-01-01#	Variant/Date
data(1, 3)	100	Variant/Double
…	…	…
data(5, 1)	“C5”	Variant/String
data(5, 2)	#2021-01-05#	Variant/Date
data(5, 3)	500	Variant/Double

This is because the data in the Excel cells is of mixed types (strings, dates, and numbers). VBA automatically converts the data to the appropriate type when reading from the cells.

11.9.2 Using Range Property

In Listing 11.27, we show how to populate a matrix with the whole data from the table using the Range property.

Listing 11.27: The array data is populated with the values from data in the Excel table (see Table 11.2).

Option Explicit

Sub PopulateArrayFromExcelRange()

    Dim str As String
    Dim i As Integer
    Dim j As Integer
    Dim rng As Range
    Set rng = Range("A1:B2")

    ReDim data( _
        1 To rng.Rows.Count, _
        1 To rng.Columns.Count) As Variant

    For i = 1 To rng.Rows.Count
        For j = 1 To rng.Columns.Count
            data(i, j) = rng.Cells(i, j)
        Next j
    Next i

    'Print the matrix
    PrintMatrix data

End Sub

The Range property is used to define the range of cells containing the data. The Cells property is then used to access the individual cells within the range.

11.9.3 Converting Range to Variant Array

In Listing 11.28, we show how to convert a range of cells to an array. This is a more concise way to populate an array with the values from a range of cells. However, it obscures the array’s dimensions and may be less readable.

Listing 11.28: The Value property of the Range object is used to convert the range to an array. If Value is omitted, it also works, as the default property of the Range object is Value.


Option Explicit

Sub ConvertRangeToArray()

    Dim data As Variant
    Dim str As String
    Dim i As Integer

    Dim rng As Range
    Set rng = Range("A2:C6")

    'Convert the range to a variant array
    data = rng.Value

    'Print the matrix
    Dim j As Integer
    For i = LBound(data, 1) To UBound(data, 1)
        str = ""
        For j = LBound(data, 2) To UBound(data, 2)
            str = str & data(i, j) & ","
        Next j
        Debug.Print str
    Next i
End Sub

11.9.4 Writing Table in Spreadsheet

In Listing 11.29, we show how to write a table to a spreadsheet.

Before executing the code, create a new Excel workbook with two sheets named raw_data and processed_data. In the raw_data sheet, place the data from Table 11.2 in the range A1:C6.

The execute the ExampleWriteTableInSpreadsheet subroutine, which:

Reads a table from sheet raw_data using the CurrentRegion property.
Clears the existing data in the processed_data sheet.
Writes the table to sheet processed_data using two different methods:
- One method writes the table using a range (see WriteAndVerifyTableData). It also verifies that the written data matches the source data.
- The other method writes the table using loops, assigning each cell individually (see WriteTableDataWithLoops).

Listing 11.29: Example of writing a table from one sheet to another in an Excel workbook. The table is read from the raw_data sheet and written to the processed_data sheet using two different methods: one using a range (see WriteAndVerifyTableData) and the other using loops (see WriteTableDataWithLoops).

' Constants defining the first row and column for the table data
Const START_ROW As Integer = 1
Const START_COL As Integer = 1

' Clears all data in the specified range
Sub ClearRangeContents(targetRange As Range)
    targetRange.ClearContents
End Sub

' Writes table data from 'raw_data' to 'processed_data' and verifies the correctness
Sub WriteAndVerifyTableData()

    ' Load data from the 'raw_data' worksheet into a Variant array
    Dim sourceData() As Variant
    sourceData = Worksheets("raw_data").Range("A1").CurrentRegion.Value

    ' Define the worksheet where data will be written
    Dim targetSheet As Worksheet
    Set targetSheet = Worksheets("processed_data")

    ' Clear existing data in the target region before writing new data
    Call ClearRangeContents(targetSheet.Cells(START_ROW, START_COL).CurrentRegion)

    ' Determine the number of rows and columns from sourceData
    Dim totalRows As Long, totalCols As Long
    totalRows = UBound(sourceData, 1)
    totalCols = UBound(sourceData, 2)

    ' Define the target range based on the dimensions of sourceData
    Dim targetRange As Range
    Dim lastRow As Long, lastCol As Long
    lastRow = START_ROW + totalRows - 1
    lastCol = START_COL + totalCols - 1
    Set targetRange = targetSheet.Range(
        targetSheet.Cells(START_ROW, START_COL), _
        targetSheet.Cells(lastRow, lastCol))

    ' Write the source data into the target range
    targetRange.Value = sourceData
    
    ' Verify that the written data matches the source data
    Call VerifyWrittenData(sourceData, targetRange, targetSheet)
End Sub

' Verifies that the data in the target range matches the original source data
Sub VerifyWrittenData( _
        sourceData() As Variant, _
        targetRange As Range, _
        targetSheet As Worksheet)

    Dim rowIndex As Long, colIndex As Long
    For rowIndex = LBound(sourceData, 1) To UBound(sourceData, 1)
        For colIndex = LBound(sourceData, 2) To UBound(sourceData, 2)

            Dim lastRow As Long, lastCol As Long
            targetRow = START_ROW + rowIndex - 1
            targetCol = START_COL + colIndex - 1
            ' Check if data matches between the source array, target sheet, and target range
            Debug.Assert sourceData(rowIndex, colIndex) = targetSheet.Cells(targetRow, targetCol).Value
            Debug.Assert sourceData(rowIndex, colIndex) = targetRange.Cells(rowIndex, colIndex).Value
        Next colIndex
    Next rowIndex
End Sub

' Writes table data from 'raw_data' to 'processed_data' by looping through cells
Sub WriteTableDataWithLoops()

    ' Get the table data from the 'raw_data' worksheet as a Range
    Dim sourceRange As Range
    Set sourceRange = Worksheets("raw_data").Range("A1").CurrentRegion

    ' Define the target worksheet
    Dim targetSheet As Worksheet
    Set targetSheet = Worksheets("processed_data")

    ' Clear existing data in the target region before writing new data
    Call ClearRangeContents(targetSheet.Cells(START_ROW, START_COL).CurrentRegion)

    ' Loop through each cell and write data to 'processed_data' one cell at a time
    Dim rowIndex As Long, colIndex As Long
    For rowIndex = 1 To sourceRange.Rows.Count
        For colIndex = 1 To sourceRange.Columns.Count
            Dim targetRow As Long, targetCol As Long
            targetRow = START_ROW + rowIndex - 1
            targetCol = START_COL + colIndex - 1
            targetSheet.Cells(targetRow, targetCol).Value = sourceRange.Cells(rowIndex, colIndex).Value
        Next colIndex
    Next rowIndex

End Sub