VBA Course: Arrays (Part 2)

The 2-Dimensional Array

To store multiple columns of data, an additional dimension is required.

Here's an example:

Storing data in a 2-dimensional array:

'Declarations
Dim myArray(10, 2) 'Array of 11 x 3 "cells"
Dim i As Integer

'Storing values in the array
For i = 0 To 10
    myArray(i, 0) = Range("A" & i + 2)
    myArray(i, 1) = Range("B" & i + 2)
    myArray(i, 2) = Range("C" & i + 2)
Next

And some examples of values:

MsgBox myArray(0, 0) 'Returns: 3/11/2026
MsgBox myArray(0, 1) 'Returns: 24
MsgBox myArray(9, 2) 'Returns: NO
MsgBox myArray(10, 2) 'Returns: YES

The Dynamic Array

Let's imagine that this same database is regularly updated and therefore we cannot enter fixed values at declaration... In this case, the dynamic array will be very useful.

To find the number of the last row in our database, use the following formula (which we saw in the previous exercise):

lastDataRow = Cells(Rows.Count, 1).End(xlUp).Row

If you enter a variable during the declaration, Excel will not accept it.

Declare a dynamic array (empty parentheses), then define its dimensions using ReDim:

Dim myArray()
ReDim myArray(lastDataRow - 2, 2)

This way, you will automatically store all the rows of the database in the array:

Sub example()

    'Declarations
    Dim myArray(), lastDataRow As Integer, i As Integer
    
    'Last row of the database
    lastDataRow = Cells(Rows.Count, 1).End(xlUp).Row

    'Redimensioning
    ReDim myArray(lastDataRow - 2, 2)
    
    'Storing values in the array
    For i = 0 To lastDataRow - 2
        myArray(i, 0) = Range("A" & i + 2)
        myArray(i, 1) = Range("B" & i + 2)
        myArray(i, 2) = Range("C" & i + 2)
    Next

End Sub

Ubound

In the above example, the last number of our array was equal to lastDataRow - 2:

For i = 0 To lastDataRow - 2

A solution to know this number (if this information is not already available) is to use the Ubound function:

For i = 0 To UBound(myArray)

This function returns the highest number for a chosen dimension (by default the first one).

Here are a few examples to better understand:

Sub example()

    Dim myArray(10, 2)
    
    MsgBox UBound(myArray) 'Returns: 10
    MsgBox UBound(myArray, 1) 'Returns: 10
    MsgBox UBound(myArray, 2) 'Returns: 2

End Sub

Storing a Range of Cells

It is possible to store a range of cells in an array without using a loop.

'Declarations
Dim myArray(10, 2) 'Array of 11 x 3 "cells"
Dim i As Integer

'Storing values in the array
For i = 0 To 10
    myArray(i, 0) = Range("A" & i + 2)
    myArray(i, 1) = Range("B" & i + 2)
    myArray(i, 2) = Range("C" & i + 2)
Next

The above code can be replaced with:

'Declaration
Dim myArray()

'Storing values in the array
myArray = Range("A2:C12")

Although the second method may seem appealing at first, it can often be more time-consuming than the first method...

By storing your data in the array this way, the first number is not 0 but 1, which can be confusing. Moreover, if during development you choose to store in the array only the data that meets certain criteria (or perform any other operation), you will still have to use a loop.

Array

Sometimes you may need to create an array containing a fixed list of values.

One solution is to declare the array and enter the values one by one:

Dim myArray(5)

myArray(0) = "IF"
myArray(1) = "VLOOKUP"
myArray(2) = "SUM"
myArray(3) = "COUNT"
myArray(4) = "ISNUMBER"
myArray(5) = "MID"

A much more convenient solution is to use the Array function, which returns an array of values:

myArray = Array("IF", "VLOOKUP", "SUM", "COUNT", "ISNUMBER", "MID")

Split

The Split function allows you to split a string into an array based on a defined delimiter.

For example, here's a string:

myString = "IF, VLOOKUP, SUM, COUNT, ISNUMBER, MID"

To convert this string into an array, use the Split function and define the separator:

myArray = Split(myString, ", ")

The array will return the following values:

MsgBox myArray(0) 'Returns: IF
MsgBox myArray(1) 'Returns: VLOOKUP
MsgBox myArray(2) 'Returns: SUM
MsgBox myArray(3) 'Returns: COUNT
MsgBox myArray(4) 'Returns: ISNUMBER
MsgBox myArray(5) 'Returns: MID

The following 3 arrays also return the same values:

myArray = Array("IF", "VLOOKUP", "SUM", "COUNT", "ISNUMBER", "MID")
myArray = Split("IF/VLOOKUP/SUM/COUNT/ISNUMBER/MID", "/")
myArray = Split("IF VLOOKUP SUM COUNT ISNUMBER MID", " ")

Join

The opposite function of Split is Join.

This function allows you to combine the values of an array into a string:

myArray = Array("IF", "VLOOKUP", "SUM", "COUNT", "ISNUMBER", "MID")

MsgBox Join(myArray, " - ") 'Returns: IF - VLOOKUP - SUM - COUNT - ISNUMBER - MID