Page 66 - Excel for Scientists and Engineers: Numerical Methods

CHAPTER 2 FUNDAMENTALS OF PROGRAMMING WITH VBA 43

Dynamic Arrays
If you don't know what array size you will need to handle a particular
problem, you can create a dynamic array. This will allow you to declare a
variable as an array but set its size later. Dimension the array using the Dim
command, using empty parentheses, and use the ReDim command later to
specify the array size, as, for example, in Figure 2-16.

Dim MeanX(), Meany()

'Get number of cells to use in calculation
Ncells = XValues.Count
ReDim MeanX(Ncells), MeanY(Ncel1s)

Figure 2-16. Re-dimensioning an array.

You can also use the ReDim command to change the number of dimensions
of an array.
The ReDim command can appear more than once in a procedure. If you use
the ReDim command to change the size of an array after it has been "populated"
with values, the values will be erased.

Preserving Values in Dynamic Arrays
You can preserve the values in an existing array by using the Preserve
keyword, e.g.,

Dim MeanX(), Meany()
ReDim Preserve MeanX(Ncel1s / 2), MeanY(Ncel1s / 2)
But, there's a limitation. Only the upper bound of the last dimension of a
multidimensional array can be changed. Thus, the following code is valid:

Dim MeanXandY(2, 1000)

ReDim Preserve MeanXandY (2,Ncells / 2)

but the following code will generate a run-time error:

Dim MeanXandY( 1000, 2)

ReDim Preserve MeanXandY (Ncells / 2,2)