Page 68 - Excel for Scientists and Engineers: Numerical Methods

CHAPTER 2 FUNDAMENTALS OF PROGRAMMING WITH VBA 45

worksheet. However, the values in the range can be accessed in the same way
that elements in a true array are accessed; for example, XRange(3) returns the
third element in the "array."

Some Worksheet Functions Used Within VBA
Create an Array Automatically

If you use a worksheet function within VBA that returns an array, the lower
array index will be 1. Such worksheet functions include: LINEST, TRANSPOSE,
MINVERSE and MMULT. That's why it's important to use Option Base 1;
otherwise, you will have some arrays with lower array index of zero and others
with lower array index of one.

An Array of Object Variables
There is an important difference between equating a range of cells in a
worksheet to a simple variable in VBA, e.g.,
ar = Range("A2:BS")
or equating a range of cells in a worksheet an object variable by using the Set
command, e.g.,
Set ar = Range("A2: B9")
Equating a variable in VBA to a worksheet range creates an array in VBA in
which each array element contains the value stored in the cell. Using the Set
command to equate an object variable in VBA to a worksheet range creates a
Range object.
For an array of object variables, you must use a different approach to obtain
the upper or lower bounds of the array indices, e.g.,
ar.Rows.Count
or

ar.Columns.Count.

Working with Arrays in Sub Procedures:
Passing Values from a VBA Module to a Worksheet
There are at least two ways to send values from a VBA array to a worksheet.
You can set up a loop and write the value of each array element to a worksheet
cell, or you can assign the value of the VBA array to the value of a worksheet
range. The latter method can cause a problem when you use this method with a
l-D range, as described next.