Read our blogs, tips and tutorials
Try our exercises or test your skills
Watch our tutorial videos or shorts
Take a self-paced course
Read our recent newsletters
License our courseware
Book expert consultancy
Buy our publications
Get help in using our site
546 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owl trainers only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
Search our website
We also send out useful tips in a monthly email newsletter ...
Written by Andy Brown
In this tutorial
If you aren't already familiar with variables in VBA, make sure you read my earlier tutorial on creating and using variables before continuing.
Arrays are just multi-cell variables. So whereas the following variable would hold a single value:
'a variable to hold a single name
Dim PersonName As String
the following array would hold the names of up to 6 people (by default arrays are numbered from 0):
'an array to hold the names of 6 people
Dim PersonName(5) As String
This VBA training tutorial shows how to create static arrays, dynamic arrays and even multi-dimensional 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.
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.
A static array is just a collection of variables of the same data type, and so can hold text, numbers or dates:
'array to hold the 7 dwarves
Dim Dwarves(6) As String
'array to hold their ages
Dim DwarfAges(6) As Integer
'array to hold their dates of birth
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!
Having declared an array, you can assign values to it in the same way as for a variable:
'array to hold names of pets
Dim PetNames(2) As String
PetNames(0) = "Shadrach"
PetNames(1) = "Meshach"
PetNames(2) = "Abednego"
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()
'array to hold names of pets
Dim PetNames(2) As String
'read in the names of your 3 cats
PetNames(0) = "Shadrach"
PetNames(1) = "Meshach"
PetNames(2) = "Abednego"
'display upper and lower bounds
MsgBox "Array PETNAMES goes from " & _
LBound(PetNames) & " up to " & _
UBound(PetNames)
End Sub
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()
'array to hold names of pets
Dim PetNames(2) As String
'read in the names of your 3 cats
PetNames(0) = "Shadrach"
PetNames(1) = "Meshach"
PetNames(2) = "Abednego"
'now list out the pet names
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:
'now list out the pet names
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:
'now list out the pet names
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.
If you want to ensure that every cell in an array is empty, you should erase it:
Option Explicit
Sub ListPeople()
'array to hold names of pets
Dim PetNames(2) As String
'read in the names of your 3 cats
PetNames(0) = "Shadrach"
PetNames(1) = "Meshach"
PetNames(2) = "Abednego"
'erase the array to start again
Erase PetNames
'now list out the pet names
Dim i As Integer
For i = 0 To 2
Debug.Print PetNames(i)
Next i
End Sub
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.
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()
'declare array of unknown length
Dim PersonName() As String
'initially there are no people
Dim NumberPeople As Integer
NumberPeople = 0
'loop over all of the people cells
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
'for each person found, extend array
NumberPeople = NumberPeople + 1
ReDim Preserve PersonName(NumberPeople-1)
PersonName(NumberPeople-1) = PersonCell.Value
Next PersonCell
'list out contents to show worked
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.
Here's how the code above works. The first thing to do is to declare an array:
Sub ListPeople()
'declare array of unknown length
Dim PersonName() As String
'initially there are no people
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:
'loop over all of the people cells
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):
'for each person found, extend array
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:
'list out contents to show worked
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:
End Sub
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.
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()
'declare two arrays of unknown length
Dim PersonName() As String
Dim PersonAge() As String
'initially there are no people
Dim NumberPeople As Integer
NumberPeople = 0
'loop over all of the people cells
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
'we've found another person!
NumberPeople = NumberPeople + 1
'for each person found, extend both arrays
ReDim Preserve PersonName(NumberPeople-1)
ReDim Preserve PersonAge(NumberPeople-1)
'now store the name and age of the new person
PersonName(NumberPeople-1) = PersonCell.Value
PersonAge(NumberPeople-1) = PersonCell.Offset(0, 1).Value
Next PersonCell
'list out contents to show worked
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!
To create an array in more than one dimension, separate the arguments with commas. For example:
'the following array has 4 x 5 x 6 elements = 120
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()
'declare array which will become 2-dimensional
Dim PersonDetails() As String
'initially there are no people
Dim NumberPeople As Integer
NumberPeople = 0
'loop over all of the people cells
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
'we've found another person!
NumberPeople = NumberPeople + 1
'for each person found, extend array dimension
ReDim Preserve PersonDetails(1, NumberPeople - 1)
'now store the name and age of the new person
PersonDetails(0, NumberPeople - 1) = PersonCell.Value
PersonDetails(1, NumberPeople - 1) = PersonCell.Offset(0, 1).Value
Next PersonCell
'list out contents to show worked
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!
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()
'create an array to hold all of the words
Dim Words() As String
'split string into words, using space as delimiter
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.
Many applications generate output as CSV files (CSV stands for Comma Separated Values). You can parse these using SPLIT.
You'll need to know how to read from TextStream objects in order to understand this example.
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()
'see other tutorial for more on this
Dim fso As New FileSystemObject
'array of values in each line
Dim LineValues() As String
'each new line read in from the text stream
Dim ReadLine As String
Dim ts As TextStream
'open file
Set ts = fso.OpenTextFile("c:\Wise Owl\people.csv")
'keep going till no more lines
Do Until ts.AtEndOfStream
'read first line
ReadLine = ts.ReadLine
'split using commas
LineValues = Split(ReadLine, ",")
'write values into this row
ActiveCell.Value = LineValues(0)
ActiveCell.Offset(0, 1).Value = LineValues(1)
ActiveCell.Offset(0, 2).Value = LineValues(2)
'go on to next row
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!
You can learn more about this topic on the following Wise Owl courses:
Kingsmoor House
Railway Street
GLOSSOP
SK13 2AA
Landmark Offices
99 Bishopsgate
LONDON
EC2M 3XD
Holiday Inn
25 Aytoun Street
MANCHESTER
M1 3AE
© Wise Owl Business Solutions Ltd 2024. All Rights Reserved.