Page 91 - Microsoft Office Excel 2003 Programming Inside Out

VBA Programming Starter Kit

Creating an Array
You create an array using the same procedure you use to define a variable. Use a Dim, a
Private, a Public, or a Static statement followed by the array name and the date type that is to
be stored in the array. The only difference between creating an array and creating a variable
is that you must define at least an upper boundary in the array declaration. (You can also
specify a lower boundary, as you’ll see shortly.) To create a string array that could hold the
names of the 12 months, for example, you would use the following statement:

Dim strMonths(11) as String
Remember, unless specified otherwise, arrays start with a default lower boundary of 0.
Therefore, to hold the twelve months, you set an upper boundary of 11 (0 to 11 provides you
with twelve elements). If you had included the Option Base 1 statement in the declarations
section then you could declare the array like this:

Dim strMonths(12) as String
Besides using the Option Base statement, you can also set the lower boundary of an array to
a value other than 0 or 1 by explicitly declaring it when you create the array using a To clause.
For example, if you wanted to declare an array that included only the three months of sum­
mer, you could use the following statement:

Dim strSummerMonths(6 To 8) as String

Creating Multidimensional Arrays

All the arrays we have talked about so far have been simple, single-dimensional arrays. You Chapter 4
can also create multidimensional arrays with up to 60 dimensions, although you will proba­
bly never find a reason to use more than 3 or 4 dimensions.
To declare a multidimensional array, you separate the boundaries of each dimension by com­
mas. A two-dimensional array that could hold a value for each month over 10 years could be
declared with the following statement:

Dim intDecadeArray(9, 11) as Integer
Visualizing multidimensional arrays might seem daunting, but some could be straightfor­
ward. For example, one use of a two-dimensional array would be as a table, where the first
dimension represents the rows of the table while the second dimension represents the col­
umns, much like the appearance of a worksheet in Excel.
Expanding to a third dimension is not much harder. Continuing with the table or worksheet
example, you could use a third dimension to keep track of multiple tables or worksheets. For
example, if a garden supply store were open sixteen hours a day, you could create an array
such as the following:

Dim curHourlySales(12, 31, 16) as Currency

65
Part 2: Visual Basic for Applications