Creating ArraysThis content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.
You can create two types of arrays in Microsoft® Visual Basic® for Applications (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 do not 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 might want to include the parentheses everywhere to make it clear that the variable is an array.
When you have 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 is a good idea to include it, so that it is obvious to you what the lower bound of the array is:
' This array contains 100 elements. ReDim alngNum(0 To 99)
If you do not include the lower bound, it is 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 might 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 require to store and size the array accordingly. If an error occurs because you have not made the array large enough, you can resize it within the error handler as many times as necessary. When you're through working with the array, if it is larger than you require, you can resize it to make it just large enough to contain the data it currently has.
Understanding Arrays | Arrays and Variants | Assigning One Array to Another | Returning an Array from a Function | Passing an Array to a Procedure | Sorting Arrays | Using the Filter Function to Search String Arrays | Using a Binary Search Function to Search Numeric Arrays | Searching a Dictionary