Microsoft Office Tutorials and References
In Depth Information
start and stop are the starting and ending indexes for the array. Here’s an example:
Dim Months(1 To 12)
The arrays you have seen so far have a single index — they are one-dimensional. VBA also supports
multidimensional arrays that have two or more indexes by including the information about the
additional indexes in the Dim statement. Here’s an example that creates a two-dimensional array:
Dim ChessBoard(1 To 8, 1 To 8) As Integer
A dynamic array does not have a fixed size — it can be enlarged or shrunk while the program is
running. The syntax for declaring a dynamic array is the same as for static arrays except that no
indexes are specified — the parentheses are left blank:
Dim MyDynamicArray() As type
Before you can use the array, you must set its size using the ReDim statement:
ReDim MyDynamicArray( indexes )
The indexes argument specifies both the number of dimensions and the number of elements,
using the same syntax as you learned about earlier for declaring static arrays. Here are some
examples of declaring and sizing dynamic arrays:
‘ 1 dimension, 21 elements 0-20.
‘ 1 dimension, 26 elements 5-30.
Redim DynamicArray2(5 to 20)
‘ 2 dimensions, 40 total elements.
ReDim DynamicArray3(1 To 4, 1 to 10)
You can change the array size while the program is running as many times as needed using this
ReDim [Preserve] arrayname indexes )
indexes specifies the new array size, as shown here. Use the optional Preserve keyword if you
want the existing data in the array to be kept. If you omit Preserve , the array will be reinitialized
and existing data lost when you execute ReDim . There are some limitations on the use of Preserve :
n When you make an array smaller, the data in the part of the array that is “trimmed off”
will be lost.
n You cannot change the number of dimensions of the array.
n For multidimensional arrays, you can change only the upper bound of the last dimension.