557 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 three 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.
|
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.
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:
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.
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.