Dynamic arrays

You can also create dynamic arrays. A dynamic array doesn't have a preset number of elements. Declare a dynamic array with a blank set of parentheses:

Dim MyArray() As Integer

Before you can use this array, you must use the ReDim statement to tell VBA how many elements the array has. Usually, the number of elements in the array is determined while your code is running. You can use the ReDim statement any number of times, changing the array's size as often as you need. The following example demonstrates how to change the number of elements in a dynamic array. It assumes that the NumElements variable contains a value, which your code calculated.

ReDim MyArray(NumElements)

When you redimension an array by using ReDim, you wipe out any values currently stored in the array elements. You can avoid destroying the old values by using the Preserve keyword. The following example shows how you can preserve an array's values when you redimension the array:

ReDim Preserve MyArray(NumElements)

If MyArray currently has ten elements and you execute the preceding statement with NumElements equaling 12, the first ten elements remain intact and the array has room for two additional elements (up to the number contained in the variable NumElements). If NumElements equals 7 however, the first seven elements are retained but the remaining three elements are purged from memory.

The topic of arrays comes up again in Chapter 10, when I discuss looping.

0 0

Post a comment