Page 67 - Excel for Scientists and Engineers: Numerical Methods

44 EXCEL: NUMERICAL METHODS

If you use Preserve, you can't use the ReDim command to change the
number of dimensions of an array.

Working with Arrays in Sub Procedures:
Passing Values from Worksheet to VBA Module
There are two ways to get values from a worksheet into a VBA array. You
can either set up a loop to read the value of each worksheet cell and store the
value in the appropriate element of an array, or you can assign the VBA array to
a worksheet range. The former method is straightforward; the latter method is
described in the following section.
Depending on which of these two methods you use, there can be a definite
difference with respect to execution speed that could become important if you
are working with extremely large arrays. An appreciable time is required to read
values from a range of worksheet cells and store them in an internal array, while
calculation using values in an internal array is much faster. Thus, if you need to
access array elements a number of times, it will probably be more time-efficient
to store the values in an internal array.

A Range Specified in a Sub Procedure
Can Be Used as an Array
If a variable in a VBA Sub procedure is set equal to a range of cells in a
worksheet, that variable can be used as an array. No Dim statement is necessary.
Thus the following expression creates a variable called TestArray that can be
treated as an array:
TestArray = Range("A2:AlO")
The worksheet array can be a range reference or a name that refers to a
reference. Thus, if the name XRange had been assigned to the range "A2:A10,"
then the following expression would also create a worksheet array called
TestArray :
TestArray = Range("XRange")
A one-row or one-column reference becomes a one-dimensional array; a
rectangular range becomes a two-dimensional array of dimensions array(rows,
columns).
The lower index of these arrays is always 1. Although arrays created within
VBA have a lower array index of zero unless specified otherwise (by means of
the Option Base 1 statement, for example), when you assign a variable name to
a range of worksheet cells, an array is created with lower array index of 1.
Note that the values in the range of cells have not been transferred to an
internal VBA array; the VBA variable simply "points" to the range on the