Array variables are essentially variables which can hold multiple, related values. One of the most useful benefits of using arrays is that you can loop through the values within the array, performing actions which use, test or modify those values. Excel itself contains dozens of arrays which are referred to as collections. For example, the "Workbooks" collection is essentially an array containing a reference to all of the workbooks currently open in Excel.

An array is a variable and must therefore be declared before you can use it. However, because it holds multiple values, when you declare it, you must also declare the size of the array. The syntax for doing so is to put the size in brackets after the name of the array. For example, the line:

Dim strBranches(12) As String

declares an array capable of holding 12 string values.

When you need to refer back to the data within the array, both for the purposes of retrieval and modification of data, use the name of the array followed by an index number in brackets. However, by default the first item in an array has an index number of zero rather than one. Thus, to place the word "London" in the first item in our "strBranches" array in the example above, we would say:

strBranches(0) = "London"

Array indexing differs from the way that collection indexes are handled within Excel VBA. To access the first worksheet in the active workbook, you would say:

ActiveWorkbook.Worksheets(1)

Given that the convention of using zero as the first index of an array is used in most programming languages, it is probably as well to go with the flow. However, if you prefer to have one as the first index, VBA offers you two methods of doing so.

The first is to use an "Option" declaration at the top of the module above any sub routines and variable declarations. The syntax is simple.

Option Base 1

The alternative is to specify the start and end index numbers in brackets after the array name when the array is declared. This approach offers more flexibility since it allows you to map the array numbers to any other numbers referred to elsewhere in your code. Thus, we could map the array index numbers to years, using a statement like the following.

Dim intYear(2000 to 2010) As Integer