Page 92 - Microsoft Office Excel 2003 Programming Inside Out

Microsoft Office Excel 2003 Programming Inside Out

Creating Dynamic Arrays

The preceding examples of declaring arrays are all fixed arrays, meaning that each array has a
set size that can’t be changed. Dynamic arrays have the ability to increase in size as needed.
This expansion doesn’t happen automatically; rather, you make it happen with commands
you add to your procedures.
To declare a dynamic array, you omit the boundary from the declaration statement. If you
wanted to create a test dynamic array, you could use the following statement:

Dim intTestArray() as Integer.
Before using a dynamic array, you do need to set the number of elements it will contain by
using the ReDim statement. ReDim lets you reset, or redimension, an array to the number of
elements that are needed. The ReDim statement requires only the name of the array and the
number of elements that it can now contain, as in the following command:
ReDim intTestArray(365)

The ReDim statement reinitializes the array, which causes the array to lose all data that is con­
tained within its elements. To increase the number of elements an array can hold without los­
ing the data it contains, you need to add the Preserve keyword to the command, as in the
following example:

ReDim Preserve intTestArray(730)

Caution There is no way to decrease the size of an array without losing the data
it contains.

You can store data in an array by specifying the index number of the element to which you
Chapter 4
want to assign the value. The following code fragments create an array to hold the names of
the four major time zones within the continental United States and assign those names to the
array:

Option Base 1
Dim strUSTimeZones(4) as String
strUSTimeZones(1) = "Eastern"
strUSTimeZones(2) = "Central"
strUSTimeZones(3) = "Mountain"
strUSTimeZones(4) = "Pacific"
Retrieving a value from an array works the same way: you specify the index number of the
element you want to use. The following statement retrieves the fourth element from the third
dimension of a two-dimensional array:

intValue = intMyArray(3,4)

66
Part 2: Visual Basic for Applications