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 can define an array of 12 string variables so that each variable corresponds to the name of a 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 arrays

You declare an array with a Dim or Public statement, just as you declare a regular variable. You can also 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 

Tip  

When you declare an array, you need specify only the upper index, in which case VBA assumes that is the lower index. Therefore, the two statements that follow 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.

By default, VBA assumes zero-based arrays. If you would like 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 

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 three 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 — 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 

Following is a declaration for a 3-D array that contains 1,000 elements (visualize this array as a cube).

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

Reference an item within the array by supplying three index numbers:

 MyArray(4, 8, 2) = 0 

Declaring dynamic arrays

A dynamic array doesn't 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. This is often done by using a variable, the value of which isn't known until the procedure is executing. For example, if the variable — contains a number, you can define the array's size by using this statement:

 ReDim MyArray (1 to x) 

You can use the ReDim statement any number of times, changing the array's size as often as you need to. When you change an array's dimensions the existing values are destroyed . If you would like to preserve the existing values, use ReDim Preserve . For example:

 ReDim Preserve MyArray (1 to y) 

Arrays crop up later in this chapter when I discuss looping ("Looping blocks of instructions").