Multidimensional Arrays in Excel VBA

 

The arrays in our last lesson were all one dimensional arrays. It's one dimensional because there's only one column of items. But you can have arrays with more than one dimension. In theory you could have an array with up to 60 dimensions, in Excel VBA. However, you'll be glad to know that it's a rare programme that uses more than 3 dimensions, or even 2 dimensions.

 

To set up an array with more than one dimension, you simply add a comma after the first number between the round brackets of your array name, then add another number. Like this:

Dim MyArray(5, 4) As Integer

Or like this:

Dim MyArray(1 To 5, 1 To 6) As Integer

In the second declaration above, we've specified that the array positions should start at 1 rather than the default 0.

The arrays above are both 2-D arrays. If you want to add another dimension, just add another comma and another number:

Dim MyArray(5, 4, 6) As Integer
Dim MyArray(1 To 5, 1 To 4, 1 To 6) As Integer

In this next exercise, we'll set up a 2-D array. We'll then print out arrays values in cells on the spreadsheet.

Create a new Sub and call it ArrayExercise_3. (You can use your spreadsheet from the previous lesson, if you like.) As the first line of code, add this line:

Dim MyArray(2, 3) As Integer

This sets up a 2-D array. Think of this like the rows and columns on your spreadsheet. The 2 means 3 rows (0 to 2, remember). The 3 means 4 columns.

To store data in the first row, add these lines:

MyArray(0, 0) = 10
MyArray(0, 1) = 10
MyArray(0, 2) = 10
MyArray(0, 3) = 10

This means row 0 column 0 has a value of 10, row 0 column 1 has a value of 10, row 0 column 2 has a value of 10, and row 0 column 3 has a value of 10.

Of course, there is no row or column 0 on a spreadsheet, and you'll see how we solve that in the loop. For now, add values for the other positions in the 2-D arrays:

MyArray(1, 0) = 20
MyArray(1, 1) = 20
MyArray(1, 2) = 20
MyArray(1, 3) = 20
MyArray(2, 0) = 30
MyArray(2, 1) = 30
MyArray(2, 2) = 30
MyArray(2, 3) = 30

The new lines add values to the rest of the positions in the array.

To go through all positions in a 2-D you need a double loop. A double loop means one loop inside another. The outer loop takes care of the rows while the inner loop takes care of the columns. (The rows are the first positions between the round brackets of MyArray, while the column are the second positions between the round brackets of MyArray)

For the loop, the outer loop, add this:

For i = 0 To 2

Next i

You now need the inner loop, in bold below:

For i = 0 To 2

For j = 0 To 3

Next j

Next i

The variable for the inner loop is j rather than i. But they are just variable names, so we could have called them almost anything we liked. Notice, too, that the outer loop goes from 0 to 2 while the inner loop goes from 0 to 3. These equate to the numbers between round the brackets of MyArray when we set it up.

The code for the loop is this, but it needs to go between the For and Next of the inner loop:

Cells(i + 1, j + 1).Value = MyArray(i, j)

This is quite complex, so we'll go through it. Take a look at the Cells part:

Cells(i + 1, j + 1)

Because our arrays is set up to start at 0 we need to add 1 to i and j. If we didn't, then the first time round the loop the values would be these:

Cells(0, 0)

This would produce an error as there is no row 0, column 0 in an Excel spreadsheet.

In case you're wondering why the first time round the loop would produce values of 0, 0 for Cells, here's an explanation.

The first line in the outer loop is another loop. This means that the entire inner loop will execute from 0 to 3. VBA will then drop to the Next i line. The next i after 0 is 1. The end condition for the outer loop, however, is 2, so we're not done with the outer loop yet. So again it drops down to execute its code. Its code just happens to be the inner loop, so it executes the whole of this inner loop again. In other words, the outer loop is going round and round from 0 to 2 times. As it's going round and round, it just so happens that it will run the inner loop 0 to 3 times.

The first time round, the values in the inner loop will be:

0, 0
0, 1
0, 2
0, 3

The second time round the inner loop, values for i and j will be:

1, 0
1, 1
1, 2
1, 3

The third time it will be:

2, 0
2, 1
2, 2
2, 3

So the first number, which is i, goes up by 1 each time. These are the rows. The second number, j, will always be 0, 1, 2 and then 3 (the columns).

Notice that after the equal sign of the Cells line, we have this:

= MyArray(i, j)

The i and j between the round brackets of MyArray will be the same as the numbers above.

But the whole of your code should look like this:

Excel VBA code showing a multi dimensional array in a  for loop

Run the code and see what happens. Switch back to your spreadsheet and you should see this:

Spreadsheet showing the result of a multi dimensional array

Multidimensional arrays and double loops can be very tricky to understand, so don't worry if hasn't all sunk in yet. Go over it a few times and you'll get there.

 

In the next lesson, we'll take a look at the Split function and how it relates to arrays.