Creating Dynamic Arrays

 < Day Day Up > 

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 contained within its elements. To increase the number of elements an array can hold without losing the data it contains, you need to add the Preserve keyword to the command, as in the following example:

ReDim Preserve intTestArray(730)

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 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)

 < Day Day Up > 

Microsoft Excel 2003 Programming Inside Out
Microsoft Office Excel 2003 Programming Inside Out (Inside Out (Microsoft))
ISBN: 0735619859
EAN: 2147483647
Year: 2006
Pages: 161

Similar book on Amazon © 2008-2017.
If you may any questions please contact us: