562 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owls 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 ...
Arrays in Visual Basic for Applications macros Part four of a five-part series of blogs |
---|
You can use arrays (multiple variables) in VBA to speed up code - and they are also invaluable for processing CSV files and the like.
This blog is part of our Excel VBA tutorial. We also run training courses for businesses or individuals in Microsoft Excel and in VBA programming.
|
I find it very hard to write enthusiastically about multi-dimensional arrays. When I first started my programming career (1986, if you must know), the first system I worked on was written in an old language called Fortran, and made extensive use of multi-dimensional arrays. The resulting code was hard to understand and write.
You can - and should - avoid ever using multi-dimensional arrays, but there will always be people who like to make life hard, and this blog page is written for them!
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:
There - that's over with!
Parts of this blog |
---|
|
Some other pages relevant to the above blogs include:
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 2023. All Rights Reserved.