Excel VBA Programming Arrays

So far, you have been using variables that store one piece of information. You have stored one Integer value, one Single value, or one String. An array is a way to store more than one value under the same name. An array looks like this when it is declared:

Dim MyArray(4) As Integer

The only difference between declaring a variable to be an array and declaring an ordinary variable is the round brackets after the variable name. In the code above, the variable name is MyArray. A pair of round brackets follows the variable name. VBA takes this to mean that you want to set up an array. The number of values you want to store using your variable name is typed between the round brackets. However, one important point to bear in mind is that the first position in the array is 0. So the above array can actually hold 5 values, 0 to 4.

If you don't want the first position to be 0 then you can declare it like this:

Dim MyArray(1 To 5) As Integer

This time, the first position in the array is 1. The number of values it can hold would then go from 1 to 5.

To place a value in an array position you do it like this:

MyArray(0) = 10
MyArray(1) = 20
MyArray(2) = 30
MyArray(3) = 40
MyArray(4) = 50

So you type the name of the variable again, followed by a pair of round brackets. Inside of the round brackets you type the number of the array position you want to fill. You then assign a value to that position in the normal way, after an equal sign. In the code above, array position 0 has a value of 10, array position 1 has a value of 20, and so on.

To get a value back out of any array, it's just like any other variable: put it on the right of an equal sign:

ArrayValue = MyArray(1)

Notice that you still need the round brackets with a position number between them. The line above will store whatever value is at position 1 in the array into the variable to the left of the equal sign.

Let's try some arrays out. You can try these exercises in a new blank workbook, or on a new sheet if you already have a file opened up in Excel. If you have a version Excel prior to Excel 2013 then just click Sheet2 at the bottom. For Excel 2013 users then you'll need to add a new Sheet. Now return to your coding window. If you can't see the code for Sheet2, double click it in the Project Explorer on the left: (If you can't see the Project Explorer, click View > Project Explorer from the menu at the top.)

Create a new Sub in your code window. Call it ArrayExercise_1. Add the following code:

Excel VBA code showing an Array set up

Click anywhere inside of Sub and End Sub. Now press F5 to run the code. You should see the message box display the value at position 1 in the array, which is a value of 2. Try changing MsgBox MyArray(1) to MsgBox MyArray(4). Run the code again and a value of 5 will appear in the message box.

Now try this. Change the message box line to MsgBox MyArray(5). Run the code again and you'll get an error message. This one:

Subscript out of range error

The Subscript is that number in round brackets. The message is telling you that your array doesn't have that many positions.


In the next lesson, we'll take a look at how arrays and loops work together in Excel VBA.