VBA Arrays

Before I start, let me share a little secret with you… I really dislike VBA arrays.  There just seem to be too many oddities in how they work.  Compared with other programming languages, VBA seems to make arrays overly complicated.  If you feel the same, then you’re in the right place.  This post contains a lot of code examples, which will cover most of your use cases.

Thank you to Jon Peltier for suggesting how I can improve this post.

Table of Contents

• What is an array & when to use it?
• Static vs. dynamic Arrays
• Declaring an array as a variable
• Create a static array
• Create a static two-dimension array
• Create a dynamic array
• Index locations
• Index of the first element
• Index of the last element
• Assigning values to an array
• Assign values to elements individually
• Assign values to elements with an array list
• Assign values to elements with a string
• Assign values to elements from a range
• Convert arrays to string and ranges
• Convert array to string
• Convert array to range
• Looping through each element in an array
• For loop: single-dimension array
• For loop: multi-dimension array
• For Each loop: single-dimension array
• For Each loop: multi-dimension array
• Check if a value is in an array
• Find the index of an element in an array
• Resizing an array
• Resize and blank values
• Resize array and keep existing values
• Sorting array order
• Reverse array order
• Filter an array
• Conclusion

What is an array & when to use it?

An array is a list of variables of the same type.  For example, a list of supplier names would be an array.

Let’s assume we have a list of 5 suppliers that can change each month.  Look at the screenshot below as an example:

To hold the supplier list, we could create 5 variables, then assign values from a worksheet to each variable.  This is what the code might look like:

Sub ListSuppliers()

'Create the variables
Dim Supplier1 As String
Dim Supplier2 As String
Dim Supplier3 As String
Dim Supplier4 As String
Dim Supplier5 As String

'Assign values to the suppliers
Supplier1 = ActiveSheet.Range("A2").Offset(0, 0).Value2
Supplier2 = ActiveSheet.Range("A2").Offset(1, 0).Value2
Supplier3 = ActiveSheet.Range("A2").Offset(2, 0).Value2
Supplier4 = ActiveSheet.Range("A2").Offset(3, 0).Value2
Supplier5 = ActiveSheet.Range("A2").Offset(4, 0).Value2

End Sub

That doesn’t seem too bad, does it?  Now imagine we have to list 1,000 suppliers, or 10,000 suppliers; that’s going to be a very dull day of coding.  Unless, of course, we use an array.

Also, what if we have an unknown number of suppliers.  What are we going to do then?  We would need create more variables than we need just to ensure there is enough space.  Again we can turn to a VBA array.

Look at the code below; it creates an array to hold 10,000 suppliers, populated from 10,000 cells in column A. You don’t need to understand it at this stage; instead, just be impressed with how neat and tidy it is. It’s difficult to believe that a VBA array containing a list of 10,000 items takes less code than a list of five variables.

Sub ListSuppliersArray()

Dim Suppliers(1 To 10000) As String
Dim i As Long

For i = LBound(Suppliers) To UBound(Suppliers)

    Suppliers(i) = ActiveSheet.Range("A2").Offset(i - 1, 0).Value2

Next i

End Sub

Using the VBA above, it doesn’t matter if there are 1, 20, 50, 1,000, or 10,000 items, the code will be the same length.  This is the advantage of arrays; we don’t have to write the same code over and over.  Instead we can write one piece of code which add all of the items into an array.

But, it doesn’t end there.  If the values to assign are in a contiguous range, we can reduce the code to just a few lines.  Look at the macro below; a range of 10,000 cells is assigned to a Variant variable type, which automatically creates an array of 10,000 items (no looping required).  Amazing stuff, right?

Sub ListSuppliersArray()

Dim Suppliers As Variant

Suppliers = ActiveSheet.Range("A2:A10001").Value2

End Sub

OK, now we understand the benefits of VBA arrays, let’s learn how to use them.

Static vs. dynamic Arrays

Arrays come in two forms:

• Static – an array with a fixed number of elements
• Dynamic – an array where the number of elements is determined as the macro runs.

The difference between the two is how they are created. After that, accessing values, looping through elements and other actions are exactly the same.

Declaring an array as a variable

Arrays are declared in the same way as single value variables.  The critical difference is that when declaring an array parentheses are often used after the variable name.

Declare a single variable

'Declare a string as a single variable
Dim myVariable As String

Declare an array variable

'Declare a string as an array
Dim myArray(1 to 5) As String

Arrays, like other variables can be any variable type.  Integers, strings, objects and ranges, etc., can all be included in an array.

Using variant as an array

A variable declared as a Variant can hold any data type.  Interestingly, a Variant type can also become an array if we assign an array to it.

Look at the code below.  First, a standard variable with a Variant data type is created, then an array is assigned to the variable.  As a result, the variable has become an array, and can be treated the same as other arrays.

Dim arrayAsVariant As Variant 
arrayAsVariant = Array("Alpha", "Bravo", "Charlie")

Create a static array

The following macro creates a static array with 5 elements (1, 2, 3, 4 & 5).

Sub CreateStaticArray()

'Create a static array with 5 elements (1, 2, 3, 4, 5)
Dim arr(1 To 5) As Long

End Sub

By default, arrays have base 0, which means they start counting at 0, rather than 1.  The following macro creates a static array with 6 elements (0, 1, 2, 3, 4, 5).  Notice that the array is created with 5 inside the parentheses, but because of base 0, there are actually 6 elements created.

Sub CreateStaticArrayStartingAtZero()

'Create a static array with 6 elements (0, 1, 2, 3, 4, 5)
Dim arr(5) As Long

End Sub

We can turn arrays into base 1 (i.e., counting starts at 1) by inserting the following code at the top of the code module.

Option Base 1

Create a static two-dimension array

Arrays can contain multiple dimensions (or sub-arrays).  This is much like having data in rows and column.  In the code below, we have created a static array of 3 elements, each of which is its own array containing another 3 elements.

Sub Create2DimensionStaticArray()

Dim arr(1 To 3, 1 To 3) As String

arr(1, 1) = "Alpha"
arr(1, 2) = "Apple"
arr(1, 3) = "Ant"
arr(2, 1) = "Bravo"
arr(2, 2) = "Ball"
arr(2, 3) = "Bat"
arr(1, 1) = "Charlie"
arr(2, 2) = "Can"
arr(3, 3) = "Cat"

End Sub

We’re not limited to just two dimensions, VBA allows us up to 60!  I don’t think I’ve very used more than 3, but it’s good to know that there are so many spare.

Create a dynamic array

The problem with static arrays is that we need to know how many elements are required when we create the array.  But often we don’t know the number of elements, or maybe we want to add and remove elements from the array as we go.  Instead, we can turn to dynamic arrays.
NOTE – The term “dynamic array” in Excel and VBA is not the same; they are entirely different methodologies.
The following macro initially creates a dynamic array with no size.  Then, later in the macro, the array is resized, using ReDim, to create 5 elements, starting at 1.

Sub CreateDynamicArray()

'Create the array
Dim arr() As Long

'Resize the array later in the macro
ReDim arr(1 To 5)

End Sub

A dynamic array can be resized many times during macro execution (we will see this later in this post).

Index locations

Each element of an array has an index number (i.e., the position in the array).

Index of the first element

The following macro displays the index number of the first element in an array.

Sub GetIndexOfFirstElement()

'Create the array
Dim arr As Variant
arr = Array("Alpha", "Bravo", "Charlie")

'Get the index number of the first element
MsgBox LBound(arr)

End Sub

LBound() is a function which returns the lowest item in the array.

Index of the last element

The following macro displays the index number of the last element in an array.

Sub GetIndexOfLastElement()

'Create the array
Dim arr As Variant
arr = Array("Alpha", "Bravo", "Charlie")

'Get the index number of the last item element
MsgBox UBound(arr)

End Sub

UBound() is a function which returns the highest item in the array.

Assigning values to an array

After creating an array, whether dynamic or static, we need a way to assign values to the individual elements.

Assign values to elements individually

The following macro creates a static array, then assigns values to each element individually.

Sub AssignFixedValuesToArray()

Dim arr(1 To 5) As String

arr(1) = "Alpha"
arr(2) = "Bravo"
arr(3) = "Charlie"
arr(4) = "Delta"
arr(5) = "Echo"

End Sub

Assign values to elements with an array list

The following macro demonstrates how to assign values to a dynamic array based on a list of values.

Sub AssignValuesFromListToArray()

'Type must be Variant for method to work
Dim arr As Variant
arr = Array("Alpha", "Bravo", "Charlie")

End Sub

The Array() command is a short way to add values to an array.

Assign values to elements with a string

The following macro splits a string into an array.

Sub SplitStringIntoArray()

Dim arr As Variant
Dim myString As String

'Create list with a by common seperator between each element
myString = "Alpha, Bravo, Charlie, Delta, Echo"

'Turn the list into an array
arr = Split(myString, ", ")

End Sub

Assign values to elements from a range

The following macro creates a 2-dimensional array directly from a range.

Sub ReadRangeToArray()

Dim arr As Variant
arr = ActiveSheet.Range("A1:C3").Value2

End Sub

When using this method, the created array will always contain two-dimensions (just like the rows and column from the range).  So, even if the source range is a single row or column, the array will still contain two-dimensions.

Convert arrays to string and ranges

Having got an array, we can then convert it into either a string or display the values in a range.

Convert array to string

The following code creates an array, then uses the Join function to convert it into a string.

Sub JoinArrayIntoString()

Dim arr As Variant
Dim joinedString As String

'Create an array
arr = Array("Alpha", "Bravo", "Charlie")

'Turn array into a string, each item separated by a comma
joinedString = Join(arr, " ,")

End Sub

Convert array to range

A 2-dimensional array can be written to the cells in a worksheet in either a horizontal or vertical direction.

Sub WriteArrayToRange()

Dim arr As Variant
arr = Array("Alpha", "Bravo", "Charlie", "Delta", "Echo")

'Write array across columns
ActiveSheet.Range("D1:H1") = arr

'Alternative, write an array down rows
'ActiveSheet.Range("D1:D5") = Application.Transpose(arr)

End Sub

Looping through each element in an array

There are two ways to loop through the elements of an array:

• For loop – Using the LBound and UBound functions to determine the number of times to loop
• For Each loop – Loops through every item in the array

NOTE – The For Each loop can only read the elements in an array; it cannot be used to change the values assigned to elements.

For loop: single-dimension array

The following example creates a single dimension array, then loops through each element in the array.

Sub ForLoopThroughArray()

Dim arr As Variant
Dim i As Long

arr = Array("Alpha", "Bravo", "Charlie", "Delta", "Echo")

'Loop from the LowerBound to UpperBound items in array
For i = LBound(arr) To UBound(arr)

    MsgBox arr(i)

Next i

End Sub

For loop: multi-dimension array

A For loop can also be used for multi-dimension arrays, as shown in the code below.

Sub ForLoopThrough2DimensionArray()

Dim arr(1 To 3, 1 To 3) As String
Dim i As Long
Dim j As Long

arr(1, 1) = "Alpha"
arr(1, 2) = "Apple"
arr(1, 3) = "Ant"
arr(2, 1) = "Bravo"
arr(2, 2) = "Ball"
arr(2, 3) = "Bat"
arr(3, 1) = "Charlie"
arr(3, 2) = "Can"
arr(3, 3) = "Cat"

For i = LBound(arr) To UBound(arr)

    For j = LBound(arr, 2) To UBound(arr, 2)

        MsgBox arr(i, j)

    Next j

Next i

End Sub

For Each loop: single-dimension array

The For Each loop works on a single or multi-dimension array.  However, it can only read data from an array, it cannot assign values to an array.

Sub ForEachLoopThroughArray()

Dim arr As Variant
Dim arrElement As Variant

arr = Array("Alpha", "Bravo", "Charlie", "Delta", "Echo")

'Loop through array using For Each method
For Each arrElement In arr

    MsgBox arrElement

Next arrElement

End Sub

For Each loop: multi-dimension array

The example below is to illustrate that the For Each loop is identical for both single and multi-dimension arrays.

Sub ForEachLoopThrough2DimensionArray()

Dim arr(1 To 3, 1 To 3) As String
Dim arrElement As Variant

arr(1, 1) = "Alpha"
arr(1, 2) = "Apple"
arr(1, 3) = "Ant"
arr(2, 1) = "Bravo"
arr(2, 2) = "Ball"
arr(2, 3) = "Bat"
arr(3, 1) = "Charlie"
arr(3, 2) = "Can"
arr(3, 3) = "Cat"

'Loop through array
For Each arrElement In arr

    MsgBox arrElement

Next arrElement

End Sub

Check if a value is in an array

We often need to search an array to discover if an item exists.  The following is a reusable function for searching through an array for a specific value.

The result of the function can be:

• True = The value searched is in the array
• False = The value searched is not in the array

The function takes two arguments (1) the array and (2) the value to find.

Function IsValueInArray(arr As Variant, find As Variant) As Boolean

Dim arrElement As Variant

'Loop through array
For Each arrElement In arr

    If arrElement = find Then
        IsValueInArray = True
        Exit Function
    End If

Next arrElement

IsValueInArray = False

End Function

The following is an example of how to call the function above; it tells the function to search for the string “Bravo” within the array.  The result returned is True if found, or False if not.

Sub UseFunctionValueInArray()

Dim arr As Variant
Dim arrElement As Variant

arr = Array("Alpha", "Bravo", "Charlie", "Delta", "Echo")

MsgBox IsValueInArray(arr, "Bravo")

End Sub

Find the index of an element in an array

In the previous sections we returned True of False depending on if an item exists.  But often that is not enough, we want to know where it is in the array.  The following is a reusable function which finds a value in an array, then returns the index position:

The result of the function can be:

• Number returned = The index position of the searched value
• False = The value searched was not found

The function takes two arguments the value to find and the array to search.

Function PositionInArray(arr As Variant, find As Variant) As Variant

Dim i As Long

For i = LBound(arr) To UBound(arr)

    If arr(i) = find Then

        PositionInArray = i
        Exit Function

    End If

Next i

PositionInArray = False

End Function

The following shows how to use the function above; if the string “Bravo” is found within the array, it will return the index position, or False if not found.

Sub UseFunctionPositionInArray()

Dim arr As Variant
Dim arrElement As Variant

arr = Array("Alpha", "Bravo", "Charlie", "Delta", "Echo")

MsgBox PositionInArray(arr, "Bravo")

End Sub

Resizing an array

As we’ve seen above, dynamic arrays are declared without a size.  Then later in the code, ReDim is used to size the array.  ReDim can be used many times during the macro to resize a dynamic array.

Static arrays cannot be resized, trying to do so, leads to an error.

When resizing an array with ReDim, the assigned values will be cleared out. To keep the existing values we must use the ReDim Preserve command.

Resize and blank values

The macro below creates, then resizes an array.  After that, the code loops through the array to demonstrate that folowing a ReDim the the values are cleared.

Sub ResizeArraySize()

Dim arr As Variant
Dim arrElement As Variant

arr = Array("Alpha", "Bravo", "Charlie", "Delta", "Echo")

'Array will resize by lose all previous values
ReDim arr(0 To 5)

'Loop through array using For Each method - all elements blank
For Each arrElement In arr

    MsgBox arrElement

Next arrElement

End Sub

Resize array and keep existing values

The following macro creates, then resizes an array using ReDim Preserve.  As the For Each loop demonstrates, by using ReDim Preserve, the values are maintained.

Sub ResizeArraySizeKeepValues()

Dim arr As Variant
Dim arrElement As Variant

arr = Array("Alpha", "Bravo", "Charlie", "Delta", "Echo")

'Array will resize by lose all previous values
ReDim Preserve arr(0 To 5)

'Add additional value into the array
arr(5) = "Foxtrot"

'Loop through array using For Each method - all elements blank
For Each arrElement In arr

    MsgBox arrElement

Next arrElement

End Sub

Sorting array order

The following function sorts an array alphabetically.  The function takes a single argument, the array to be sorted.

Function SortingArrayBubbleSort(arr As Variant)

Dim i As Long
Dim j As Long
Dim temp As Variant

For i = LBound(arr) To UBound(arr) - 1

    For j = i + 1 To UBound(arr)

        If arr(i) > arr(j) Then

            temp = arr(j)
            arr(j) = arr(i)
            arr(i) = temp

        End If

    Next j

Next i

SortingArrayBubbleSort = arr

End Function

The following is an example of how to use the function above.

Sub CallBubbleSort()

Dim arr As Variant
arr = Array("Charlie", "Delta", "Bravo", "Echo", "Alpha")

arr = SortingArrayBubbleSort(arr)

End Sub

Reverse array order

The function below reverses the order of an array.  The function takes the name of an array as the only argument.

Function ReverseArray(arr As Variant)

Dim temp As Variant
Dim i As Long
Dim arrSize As Long
Dim arrMid As Long

arrSize = UBound(arr)
arrMid = (UBound(arr) - LBound(arr)) \ 2 + LBound(arr)

For i = LBound(arr) To arrMid

    temp = arr(arrSize)
    arr(arrSize) = arr(i)
    arr(i) = temp
    arrSize = arrSize - 1

Next i

ReverseArray = arr

End Function

The code below is an example of how to use the function above.

Sub CallReverseArray()

Dim arr As Variant
arr = Array("Alpha", "Bravo", "Charlie", "Delta", "Echo")

arr = ReverseArray(arr)

End Sub

Filter an array

Along with LBound, UBound, Split and Join, another useful built-in function is Filter.

The Filter function returns an array that includes only the elements which contain a sub-string.  In the example below the filteredArr array only includes the elements which contain the letter “o”,

Sub FilterArray()

Dim arr As Variant
Dim filteredArr As Variant
Dim arrElement As Variant

arr = Array("Alpha", "Bravo", "Charlie", "Delta", "Echo")

'Filter array for any elements with the letter "o"
filteredArr = Filter(arr, "o")

'Loop through the filtered array
For Each arrElement In filteredArr

    MsgBox arrElement

Next arrElement

End Sub

The Filter function has 4 arguments:

Filter(SourceArray, Match, [Include], [CompareType])

• SourceArray – the original array
• Match – the substring to match
• Include (default is True if the argument is excluded)
  • True = include the matched items
  • False = exclude the matched items
• CompareType Include (default is 0 if the argument is excluded):
  • 0 = vbBinaryCompare – The match is case sensitive
  • 1 = vbTextCompare – The match is not case sensitive

Conclusion

Hopefully, this post covers most of your needs.  However, VBA arrays are a vast topic, so make use of online forums to ask specific questions which this post doesn’t answer.

---

What next?

Discover how you can automate your work with our Excel courses and tools.