Home and Learn - Free Excel VBA Course

6.2 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

Excel VBA code showing an array and a For loop

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:

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)

And so on.

Test your code out. Run your Sub and then have a look at your spreadsheet. You should see this:

Spreadsheet showing values from an array in Excel VBA

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.

< Arrays Intro

Next Lesson: 6.3 Multi Arrays >