If you aren't already familiar with variables in VBA, make sure you read my earlier tutorial on creating and using variables before continuing.
What are Arrays?
Arrays are just multi-cell variables. So whereas the following variable would hold a single value:
the following array would hold the names of up to 6 people (by default arrays are numbered from 0):
Dim PersonName(5) As String
This VBA training tutorial shows how to create static arrays, dynamic arrays and even multi-dimensional arrays!
When to Use Arrays
Be careful not to overuse arrays, particularly if you have a progamming background in other languages. Excel already comes supplied with a built-in structure for holding multiple related values:
A built-in structure for holding related information (otherwise known as a range of cells).
The main reason to use arrays is to speed up processing. For the above example, if you're going to refer to the list of 6 people frequently it may be quicker to read their names into an array and hold them in memory.
Arrays Starting from One, not Zero
By default, arrays in VBA start from 0, but you can change this behaviour with the Option Base 1 statement:
You can not put something in the 0th element of an array if it starts at 1!
It is a difficult decision to make whether to start arrays at 0 or 1, but on balance I'd go with the default of 0 (and omit the Option Base 1 statement shown above). Here are some reasons why:
Reason | Notes |
|---|
Simplicity | If you start arrays at 0, you won't have to type Option Base 1 in every new module. |
Consistency | VBA isn't consistent about arrays: some built-in arrays (such as the SelectedItem array returned from a file dialog box, for example) start at 1. However, on the whole most arrays start with 0. |
Compatibility | VBA may allow you to start arrays at 1, but other languages don't. In particular, if you ever want to graduate from VBA to VB or C#, you should get used to arrays starting at 0. |
And with that out of the way, let's start looking at how to declare and use static arrays.
Declaring a Static Array
A static array is just a collection of variables of the same data type, and so can hold text, numbers or dates:
Dim Dwarves(6) As String
Dim DwarfAges(6) As Integer
Dim DwarfDobs(6) As Date
It's probably just about worth mentioning that you can declare an array upper and lower limit. For example:
Dim Dwarves(3 to 9) As String
I have never used this in my programming career!
Setting Values in an Array
Having declared an array, you can assign values to it in the same way as for a variable:
Dim PetNames(2) As String
PetNames(0) = "Shadrach"
PetNames(1) = "Meshach"
PetNames(2) = "Abednego"
Referring to the Lower and Upper Bound of an Array
You can use UBound and LBound to refer to the upper and lower limits of an array. The following code would display this message:
The message displays the upper and lower bounds of the array.
Here is the code to demonstrate this:
Option Explicit
Sub ListPeople()
Dim PetNames(2) As String
PetNames(0) = "Shadrach"
PetNames(1) = "Meshach"
PetNames(2) = "Abednego"
MsgBox "Array PETNAMES goes from " & _
LBound(PetNames) & " up to " & _
UBound(PetNames)
End Sub
Looping Over the Elements in an Array
One of the most common things you will do with an array is to loop over all of the elements in it. To do this, you should create an integer variable to refer to each subscript in the array:
Sub ListPeople()
Dim PetNames(2) As String
PetNames(0) = "Shadrach"
PetNames(1) = "Meshach"
PetNames(2) = "Abednego"
Dim i As Integer
For i = 0 To 2
Debug.Print PetNames(i)
Next i
End Sub
Note that you can loop over the elements using the upper and lower bounds instead:
Dim i As Integer
For i = LBound(PetNames) To UBound(PetNames)
Debug.Print PetNames(i)
Next i
Or, if you prefer, you could start your loop at 1:
Const NumberPets As Integer = 3
Dim i As Integer
For i = 1 To NumberPets
Debug.Print PetNames(i - 1)
Next i
Whichever of the methods above you choose, you should see the contents of the array in your immediate window:
Each of the 3 loops above will show the same thing: a list of the array elements.
Erasing an Array
If you want to ensure that every cell in an array is empty, you should erase it:
Option Explicit
Sub ListPeople()
Dim PetNames(2) As String
PetNames(0) = "Shadrach"
PetNames(1) = "Meshach"
PetNames(2) = "Abednego"
Erase PetNames
Dim i As Integer
For i = 0 To 2
Debug.Print PetNames(i)
Next i
End Sub
Sorting Arrays in VBA
You can't! If you want to sort the pet names shown above into alphabetical order, your best choice is:
Put the contents of the array into worksheet cells.
Sort the column created in VBA.
Read the contents back into a new version of the array.
Dynamic Arrays
Nearly every array I've ever used in VBA has been dynamic: that is, I haven't known how big the array would end up being when I first declared it.
Suppose that you want to read the values of a column of cells into an array:
Suppose that you want to read all of the names in column A into an array, but you're not sure how many there will be.
To do this, you need to dimension the array initially with no specific number of items, then redimension it for each new person found. The program shown below would produce the following output:
The VBA code below would show a message containing the people found.
The code which produced this message box was as follows (it's broken down below into its different parts):
Sub ListPeople()
Dim PersonName() As String
Dim NumberPeople As Integer
NumberPeople = 0
Dim PersonCell As Range
Dim TopCell As Range
Dim PersonRange As Range
Set TopCell = Range("A1")
Set PersonRange = Range(TopCell, _
TopCell.End(xlDown))
For Each PersonCell In PersonRange
NumberPeople = NumberPeople + 1
ReDim Preserve PersonName(NumberPeople-1)
PersonName(NumberPeople-1) = PersonCell.Value
Next PersonCell
Dim msg As String
Dim i As Integer
msg = "People in array: " & vbCrLf
For i = 1 To NumberPeople
msg = msg & vbCrLf & PersonName(i-1)
Next i
MsgBox msg
End Sub
Note the use of the keyword Preserve. Without this, Excel would create a new array containing one more value than the predecessor - but lose any previous contents in the process. Also note that redimensioning an array like this takes a copy of the old array and puts it into a new one. For 6 names this will run like lightning, but for 6,000 you'll start noticing speed issues.
Dynamic Arrays - Breaking Down the Code
Here's how the code above works. The first thing to do is to declare an array:
Sub ListPeople()
Dim PersonName() As String
Dim NumberPeople As Integer
NumberPeople = 0
Initially, then, we don't know how big the array is. The code then creates a variable called PersonRange to refer to the column of names:
Dim PersonCell As Range
Dim TopCell As Range
Dim PersonRange As Range
Set TopCell = Range("A1")
Set PersonRange = Range(TopCell, _
TopCell.End(xlDown))
Now that we've got a reference to the range of cells from A1 down to the bottom of the list, we can loop over them:
For Each PersonCell In PersonRange
For each cell, we now:
Increment the number of people found to one more than it used to be.
Increase the size of the array, being careful to preserve any existing values.
Store the name just found in the last array entry (the one we've just created by extending the array).
Here is the code to do this (including the end of the loop):
NumberPeople = NumberPeople + 1
ReDim Preserve PersonName(NumberPeople-1)
PersonName(NumberPeople-1) = PersonCell.Value
Next PersonCell
We now want to check if this has worked, so we first create a string variable to hold the start of a message:
Dim msg As String
Dim i As Integer
msg = "People in array: " & vbCrLf
Finally, we add each name stored in our array to the end of this message (with a carriage return code before each), then display the results:
For i = 1 To NumberPeople
msg = msg & vbCrLf & PersonName(i-1)
Next i
MsgBox msg
And just for completeness:
This is a good example of when we gained nothing by using an array - we could have just read in each cell's value and then printed it out directly to the Immediate window.
Avoiding Multi-dimensional Arrays
Let's start with how to avoid them. Suppose that you want to read the names and ages of the people in the spreadsheet shown below into arrays:
You might want to read the names and ages of these people into an array.
The easy way to do this is to create two arrays: one for the names, and another for the ages. Here (without any further explanation) is code which would do just that:
Option Explicit
Sub ListPeople()
Dim PersonName() As String
Dim PersonAge() As String
Dim NumberPeople As Integer
NumberPeople = 0
Dim PersonCell As Range
Dim TopCell As Range
Dim PersonRange As Range
Set TopCell = Range("A1")
Set PersonRange = Range(TopCell, _
TopCell.End(xlDown))
For Each PersonCell In PersonRange
NumberPeople = NumberPeople + 1
ReDim Preserve PersonName(NumberPeople-1)
ReDim Preserve PersonAge(NumberPeople-1)
PersonName(NumberPeople-1) = PersonCell.Value
PersonAge(NumberPeople-1) = PersonCell.Offset(0, 1).Value
Next PersonCell
Dim i As Integer
For i = 1 To NumberPeople
Debug.Print PersonName(i-1), PersonAge(i-1)
Next i
End Sub
If you're wondering what you'd do if you got a 3rd column, the answer is: create a 3rd array. If you created a 4th column, maybe it would be time to think of creating a database application!
Declaring Multi-dimensional Arrays
To create an array in more than one dimension, separate the arguments with commas. For example:
Dim PointlessArray(3, 4, 5)
Depressingly, you can have up to 32 dimensions. IMHO, two is one too many!
For our example above, you could list out the cell contents as follows:
Option Explicit
Sub ListPeople()
Dim PersonDetails() As String
Dim NumberPeople As Integer
NumberPeople = 0
Dim PersonCell As Range
Dim TopCell As Range
Dim PersonRange As Range
Set TopCell = Range("A1")
Set PersonRange = Range(TopCell, _
TopCell.End(xlDown))
For Each PersonCell In PersonRange
NumberPeople = NumberPeople + 1
ReDim Preserve PersonDetails(1, NumberPeople - 1)
PersonDetails(0, NumberPeople - 1) = PersonCell.Value
PersonDetails(1, NumberPeople - 1) = PersonCell.Offset(0, 1).Value
Next PersonCell
Dim i As Integer
For i = 1 To NumberPeople
Debug.Print PersonDetails(0, i - 1), PersonDetails(1, i - 1)
Next i
End Sub
There are a couple of points worth noting about this:
When you first declare an array which you will later resize, don't put any numbers as subscripts.
When you change the size of a multi-dimensional array using REDIM PRESERVE, you can only change the size of the last subscript.
There - that's over with!
Example: Splitting Text into Words
One of the most useful things that you can do with arrays is to split text strings into distinct words or phrases, using SPLIT:
ArrayName = Split(Text string, character to use as delimiter)
As an example, suppose that you want to count the number of words in a phrase like the following:
I wandered lonely as a cloud
By far the easiest way to do this is to split the string into distinct words, and then count how many there are:
Option Explicit
Const txt As String = "I wandered lonely as a cloud"
Sub Example()
Dim Words() As String
Words = Split(txt, " ")
MsgBox "String contains " & (UBound(Words) + 1) & " words"
End Sub
This would produce the following output:
The array generated goes from 0 to 5, and has 6 elements.
Example - Parsing CSV Files
Many applications generate output as CSV files (CSV stands for Comma Separated Values). You can parse these using SPLIT.
Suppose that you have exported our list of people (now expanded to include a hair colour column) to a CSV file. This is what it looks like in Excel:
In Excel we now have 3 columns.
The CSV file generated looks like this:
The CSV file contains a list of the people, with column values separated by commas.
If you wanted to read these values in to extract the name, age and hair colour for each person, by far the easiest way to do it would be to split each line up, using the commas as separation characters. Here is code to do this:
Sub ReadLines()
Dim fso As New FileSystemObject
Dim LineValues() As String
Dim ReadLine As String
Dim ts As TextStream
Set ts = fso.OpenTextFile("c:\Wise Owl\people.csv")
Do Until ts.AtEndOfStream
ReadLine = ts.ReadLine
LineValues = Split(ReadLine, ",")
ActiveCell.Value = LineValues(0)
ActiveCell.Offset(0, 1).Value = LineValues(1)
ActiveCell.Offset(0, 2).Value = LineValues(2)
ActiveCell.Offset(1, 0).Select
Loop
End Sub
This routine would read in the CSV file line by line, splitting each line into an array by using the comma as a separator. The result?
Assuming that you have A1 as the active cell when you run this macro, this is what you will get.
And that completes my thoughts on using arrays in VBA!
