Arrays follow the same rules as ordinary variables. They can be local, module, or global
and can be any data type, including Variant. The size of the array in terms of elements is
limited to an integer (in the range –32,768 to 32,767). The default lower boundary is always
0, but this can be altered by placing an Option Base statement in the declarations section of
the module:
Option Base 1
All arrays in that module start at 1.
You can also specify the lower limit for an array by using theTokeyword:
Dim temp (1 To 15 ) as String
Multidimensional Arrays
I have only discussed one-dimensional arrays, but you can have several dimensions to an
array. Think of it as being like a spreadsheet. You have rows and columns that give a
reference; you can also have several different sheets so that a cell reference is made up of the
sheet name plus the cell column and row:
Dim temp(10,4) as String
If this was a spreadsheet, it would have 11 columns and five rows, giving you a total of
55 pigeonholes or cells to place your data into and refer to it.
A three-dimensional array would be as follows:
Dim temp(10,4,3) as String
Imagining this again as a spreadsheet, it would have 11 columns and five rows, but they
would span across four worksheets, giving you a total of 220 pigeonholes. If you remember
that each one of these elements can take a string up to 65,400 characters, you begin to see
how much memory can be used by a simple array and how much data can be stored.
Dimensioning an array immediately allocates memory to it—this is an important consideration
when planning your program. Taking up large chunks of memory can cause your program and
Windows to run inefficiently. Because Windows itself is a graphical application, it uses large
amounts of RAM (random access memory) to hold information. You may find that using a large
array slows Windows down, and other applications run more slowly and take longer to process
information. This may be all right on your home computer, but a professional application needs
to take this into account.
Further dimensions are possible, but these become difficult to keep track of and manipulate.
Five dimensions is considered the safe maximum to use. If you go back to thinking of an array as
being like a series of spreadsheets, think how complicated a five-dimensional spreadsheet would be!
ReDimcan still be used to resize the array, but you cannot use it to change the number
of dimensions in the array, nor can you use it to change the type of the array—for example,
from string to integer.
Chapter 2: Variables, Arrays, Constants, and Data Types 23