| Microsoft Office 2000/Visual Basic Programmer's Guide | |
You can create two types of arrays in VBA—fixed-size arrays and dynamic arrays. A fixed-size array has a fixed number of elements, and is useful only when you know exactly how many elements your array will have while you're writing the code. Most of the time you'll create dynamic arrays.
Arrays can be of any data type. The data type for an array specifies the data type for each element of the array; for example, each element of an array of type Long can contain a Long value. The following code fragment declares an array variable of type Long:
Dim alngNum() As Long
Note You don't have to include the parentheses when you refer to an array variable, except when you declare it, resize it, or refer to an individual element. However, you may want to include the parentheses everywhere to make it clear that the variable is an array.
Once you've declared a dynamic array variable, you can resize the array by using the ReDim statement. To resize the array, you provide a value for the upper bound, and optionally, for the lower bound. The upper and lower bound of an array refer to the beginning and ending indexes for the array.
You must specify the upper bound for the array when you resize it. The lower bound is optional, but it's a good idea to include it, so that it's obvious to you what the lower bound of the array is:
' This array contains 100 elements.
ReDim alngNum(0 To 99)
If you don't include the lower bound, it's determined by the Option Base setting for the module. By default, the Option Base setting for a module is 0. You can set it to 1 by entering Option Base 1 in the Declarations section of the module.
If you are using the ReDim statement on an array that contains values, those values may be lost when the array is resized. To ensure that any values in the array are maintained, you can use the Preserve keyword with the ReDim statement, as follows:
ReDim Preserve alngNum(0 To 364)
Resizing an array with the Preserve keyword can be slow, so you want to do it as infrequently as possible. A good way to minimize use of the Preserve keyword in your code is to estimate the amount of data you need to store and size the array accordingly. If an error occurs because you haven't made the array large enough, you can resize it within the error handler as many times as necessary. Once you're through working with the array, if it's larger than you need, you can resize it to make it only large enough to contain the data it currently has. The example in "Returning an Array from a Function" later in this chapter demonstrates this technique.