VBA Arrays

VBA Code Snippets

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.

Download the example file
I recommend you download the files which support this post, as you’ll be able to work along with examples.  This is the best way to learn.  You’ll be able to see the solutions in action, plus the file will be useful for future reference.  The support files are available for FREE to newsletter subscribers.

Click below to subscribe and gain access to the subscriber area.  You will also receive:

  • My favorite tips and tricks direct to your inbox
  • Exclusive content (which is only available to subscribers)
  • FREE tools and downloads

Download Icon

If you’re already a subscriber, click here to log-in to the subscriber downloads area.

The filename for this post is 0017 VBA Arrays.zip

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:

Top Suppliers List

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 10,000 suppliers; that’s going to be a very dull day of coding.  Unless, of course, we use an 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 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.

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 the 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 for arrays, parentheses are 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 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.  Therefore, it can become an array if we assign an array to it.

Look at the code below.  First, a 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 in 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 (before declaring any subs or variables).

Option Base 1

Create a static two-dimension array

Arrays can contain multiple dimensions (or sub-arrays).  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!

Create a dynamic array

The problem with static arrays is that often we don’t know the number of elements required when declaring the variables.  Instead, we must 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

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

Assigning values to an array

After creating an array, whether dynamic or static, we need a way to assign values to the 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 assigns 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

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

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

Download the 100 Excel Macros ebook

100 Excel Macros Book

  • Contains 100 Excel VBA macros
  • Learn VBA by following along with the example codes
  • Apply to your macros, automate Excel, save time.

Download the ebook today!

Click the button below to subscribe, you’ll gain access to the subscriber area in which you can download the ebook.

Download Icon (on mid-green background)

Check if a value is in an array

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

The following is a reusable function which finds an element 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 maco to resize a dynamic array.

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

Array already dimensionedd

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

Resize and blank values

The macro below creates, then resizes an array.  After that, the code loops through the array to demonstrate that 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, 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 creates 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.

Don’t forget:

If you’ve found this post useful, or if you have a better approach, then please leave a comment below.

Do you need help adapting this to your needs?

I’m guessing the examples in this post didn’t exactly meet your situation.  We all use Excel differently, so it’s impossible to write a post that will meet everybody’s needs.  By taking the time to understand the techniques and principles in this post (and elsewhere on this site) you should be able to adapt it to your needs.

But, if you’re still struggling you should:

  1. Read other blogs, or watch YouTube videos on the same topic.  You will benefit much more by discovering your own solutions.
  2. Ask the ‘Excel Ninja’ in your office.  It’s amazing what things other people know.
  3. Ask a question in a forum like Mr Excel, or the Microsoft Answers Community.  Remember, the people on these forums are generally giving their time for free.  So take care to craft your question, make sure it’s clear and concise.  List all the things you’ve tried, and provide screenshots, code segments and example workbooks.
  4. Use Excel Rescue, who are my consultancy partner.   They help by providing solutions to smaller Excel problems.

What next?
Don’t go yet, there is plenty more to learn on Excel Off The Grid.  Check out the latest posts:

Leave a Reply

Your email address will not be published. Required fields are marked *