Microsoft Office Tutorials and References
In Depth Information
Arrays
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 you’ll rarely 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-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
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. You can use a variable to assign the number of elements in
 
Search JabSto ::




Custom Search