Expanding to Dynamic Arrays

So far, all the array examples have specified the array's lower and upper bounds when declared. Doing so creates what's known as a fixed-size array. If you don't specify the size of the array when you declare it, you create a dynamic array. To size the array, you must use the ReDim statement as follows:

 Dim arrExample() As datatype ... ReDim arrExample([lower] To upper) As datatype 

Because VBA insists on an explicit value when declaring an array using Dim, this is the only way to control the value of the lower and upper bounds using a variable.

About ReDim

The ReDim statement changes the dimensions and reallocates storage space for a dynamic array. Use this statement to define an array's bounds after you've declared the array and before you use the array.

The ReDim statement takes the form

 ReDim [Preserve] arrayvariable([lowerTo] upper) As datatype 

where Preserve is a VBA keyword that preserves an array's data when changing the only or last dimension value (bound). Without the Preserve keyword, ReDim erases an array's existing values.

The following procedure uses a dynamic array:

 Public Sub ArrayExample3(l As Integer, u As Integer)   Dim intCounter As Integer   Dim arrExample() As Variant   ReDim arrExample(l To u)   For intCounter = l To u     arrExample(intCounter) = intCounter * 0.1   Next   For intCounter = l To u     Debug.Print arrExample(intCounter)   Next End Sub 

In this procedure, the array's dimensions aren't set by the original Dim statement, but by the ReDim statement. Consequently, you can pass the lower and upper values to the procedure, which makes the procedure much more flexible.

You can use the procedure provided in TimeTrack.mdb's Chapter7 module. Or, you can open a blank module and create it yourself. Either way, execute the procedure by entering the following statement in the Immediate window:

ArrayExample3 1, 4

Figure 7.2 shows the results 0.1, 0.2, 0.3, and 0.4. Try again, but this time pass the values 2 and 6, as shown in Figure 7.3.

Figure 7.2. Pass the lower and upper dimensions via procedure arguments.

Figure 7.3. Change the array's dimensions by passing different values.