Microsoft Office Tutorials and References
In Depth Information
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:
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
ReDim Preserve intTestArray(730)
Caution There is no way to decrease the size of an array without losing the data
You can store data in an array by specifying the index number of the element to which you
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
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)