Using Arrays

An array is a group of elements of the same type that have a common name; you refer to a specific element in the array by using the array name and an index number. For example, you may define an array of 12 string variables so that each variable corresponds to the name of a different month. If you name the array MonthNames, you can refer to the first element of the array as MonthNames(0), the second element as MonthNames(1), and so on, up to MonthNames(11).

Declaring an Array

You declare an array with a Dim or Public statement just as you declare a regular variable. You also can specify the number of elements in the array. You do so by specifying the first index number, the keyword To, and the last index number-all inside parentheses. For example, here's how to declare an array comprising exactly 100 integers:

 Dim MyArray(1 To 100) As Integer 

When you declare an array, you need to specify only the upper index, in which case VBA (by default) assumes that 0 is the lower index. Therefore, the following two statements have the same effect:

 Dim MyArray(0 to 100) As Integer Dim MyArray(100) As Integer 

In both cases, the array consists of 101 elements.

If you want VBA to assume that 1 is the lower index for all arrays that declare only the upper index, include the following statement before any procedures in your module:

 Option Base 1 

If this statement is present, the following two statements have the same effect (both declare an array with 100 elements):

 Dim MyArray(1 to 100) As Integer Dim MyArray(100) As Integer 

Declaring Multidimensional Arrays

The array examples in the preceding section are one-dimensional arrays. VBA arrays can have up to 60 dimensions although it's rare to need more than 3 dimensions (a 3-D array). The following statement declares a 100-integer array with two dimensions (2-D):

 Dim MyArray(1 To 10, 1 To 10) As Integer 

You can think of the preceding array as occupying a 10 x 10 matrix. To refer to a specific element in a 2-D array, you need to specify two index numbers. For example, here's how you can assign a value to an element in the preceding array:

 MyArray(3, 4) = 125 

A dynamic array does not have a preset number of elements. You declare a dynamic array with a blank set of parentheses:

 Dim MyArray() As Integer 

Before you can use a dynamic array in your code, however, you must use the ReDim statement to tell VBA how many elements are in the array (or ReDim Preserve if you want to keep the existing values in the array). You can use the ReDim statement any number of times, changing the array's size as often as you like.

Arrays crop up later in this chapter in the sections that discuss looping.