21/03/2021 Excel VBA Programming Arrays and Loops
Home and Excel VBA
Learn
Excel VBA
Programming
Arrays and Loops
Arrays are usually used with loops. This is because it's (fairly) easy
to access each array position in a loop you just use the loop
variable between the round brackets of the array.
What we'll do now is to store some numbers in an array. We'll then
use a loop to print them out to cells in a spreadsheet.
To test this out, set up another Sub in the coding window you
created for the last lesson. Call it ArrayExercise_2. Now type the
following code for your new Sub
[Link] 1/5
21/03/2021 Excel VBA Programming Arrays and Loops
We've set up an Integer array called MyArray. This goes from 1 to
5:
Dim MyArray(1 To 5) As Integer
The reason why we need 1 as the lowest position in our array is
because we want to access rows in a spreadsheet. If we'd gone
with the default array then the first position would be 0. There is no
row 0 in a spreadsheet, so this would have caused errors. (You'll
see a way round this in a moment.)
The next 5 lines store the values 10 to 50 into each position in the
array.
We then have the For loop. The first line is curious:
For i = 1 To UBound(MyArray)
This sets up a variable called i and stores a value of 1 in it. The
lowercase letter i is a popular variable name in loop code, as it's
nice and short. Think of it as standing for "Initial Value".
After the word "To" we have this:
[Link] 2/5
21/03/2021 Excel VBA Programming Arrays and Loops
UBound(MyArray)
The UBound part is short for Upper Boundary. This gets you the
highest number in your array (there's also an LBound to get the
lowest value). In between the round brackets of UBound you type
the name of your array. Notice that you don't need round brackets
for your array name here.
The loop, then, goes from 1 to the highest value in the array, which
is 5 in this case. So we're looping round 5 times.
The code for the loop is this:
Cells(i, 1).Value = MyArray(i)
The thing to bear in mind here is that the value of i will change each
time round the loop. By typing the loop variable between the round
brackets of not only Cells but MyArray you can access each row or
column on the spreadsheet and each position in the array. This is a
very powerful technique and one we highly recommend you learn.
The first time round the loop, the values will really be these:
Cells(1, 1).Value = MyArray(1)
The next time round, the values will be these:
Cells(2, 1).Value = MyArray(2)
The third time round, these:
Cells(3, 1).Value = MyArray(3)
[Link] 3/5
21/03/2021 Excel VBA Programming Arrays and Loops
And so on.
Test your code out. Run your Sub and then have a look at your
spreadsheet. You should see this:
The code has placed each value from the array into a cell on the
spreadsheet.
In the next lesson, we'll take a look at multi dimensional arrays.
Multi Dimensional Arrays >
<< Back to the Excel VBA Home Page
Lots more free online courses here on our main Home and Learn
site
© All course material copyright Ken Carney
[Link] 4/5
21/03/2021 Excel VBA Programming Arrays and Loops
[Link] 5/5
