Page 81 - Excel Progamming Weekend Crash Course

d540629 ch04.qxd 9/2/03 9:28 AM Page 56

56 Friday Evening

If you do not want to use 0 as the lower array index, you have two choices. One choice is
to include the Option Base 1 statement at the start of a module, outside of any proce-
dures. This results in all arrays in the module having a lower bound of 1 instead of 0. The
other choice provides more flexibility, using the To keyword in the array declaration:
Dim ArrayName(lowerbound To upperbound) As Type

Lowerbound must be smaller than upperbound, and both must be integers. Here’s an
example:

Dim MyArray(20 To 40) As Long
Arrays with a single index are called one-dimensional arrays. You can also create arrays
with two or more indexes by specifying the additional indexes in the Dim statement.

Dim TwoDimensions(10, 10) As Single
This array has 121 elements, references as TwoDimensions(0, 0) through
TwoDimensions(10, 10). The first dimension has 11 elements numbered 0 through 10.
The second dimension also has 11 elements. The total number of elements is the product
of 11 times 11. You can use the To keyword in multidimensional arrays:

Dim ChessBoard(1 To 8, 1 To 8) As String
VBA does not impose any specific limits on the number of elements in an array, or the
number of dimensions. Available system memory and disk space, however, impose practical
limits, but these limits are not likely to pose a problem. In the situation where a system is
running low on both free memory and disk space, you might encounter problems, but this is
very unlikely to happen.
If your program tries to use an array element that does not exist, a runtime
error occurs. For example, if you create an array with the statement Dim
Note MyArray(20) As Integer, then you’ll get an error if you try to use
MyArray(21), MyArray(30), and so on.

Dynamic Arrays
A dynamic array does not have a fixed size. It can be enlarged or shrunk as needed by code
during program execution. A dynamic array is declared by using empty parentheses in the
Dim statement:

Dim MyDynamicArray() As type
Before using the array, you must set its size using the ReDim statement:

ReDim MyDynamicArray(size)