Page 65 - Excel for Scientists and Engineers: Numerical Methods

42 EXCEL: NUMERICAL METHODS

Use the Name of the Array Variable
to Specify the Whole Array
You can refer to the complete array by using the array variable name in your
code. The array name can be used with or without parentheses.

Multidimensional Arrays
Arrays can be multidimensional. Two-dimensional arrays are common; to
create a 2-D array called Spectrum, with dimensions 500 rows x 2 columns, use
the statement

Dim Spectrum (500,2)

Declaring the Variable Type of an Array
Since multidimensional arrays such as the one above can use up significant
amounts of memory, it's a good idea to define the data type of the variable. The
complete syntax of the Dim statement is
Dim VariableName(Lower To Upper) As Type

The optional Lower To can be omitted. Type can be Integer, Single,
Double, Variant, etc. See the complete list of data types in "VBA Data Types"
earlier in this chapter.) A Variant array can hold values of different data types,
such as integer and string, in the same array.
Several variables can be dimensioned in a single Dim statement, but there
must be a separate As Type for each variable. Thus
Dim J As Integer, K As Integer

is OK but Dim J, K As Integer declares only the variable J as integer.

Returning the Size of an Array
Use the LBound and UBound functions to obtain the size of an array during
execution of your procedure. The LBound function returns the lower index of
an array. For example, for the array Sample described previously,
LBound(Samp1e) returns 1 and UBound(Samp1e) returns 10.
The complete syntax of LBound and UBound is LBound(arrayname,
dimension). For the array Spectrum dimensioned thus:
Dim Spectrum (500,2)
the statement UBound(Spectrum, 1) returns 500 and UBound(Spectrum,2)
returns 2.