Page 64 - Excel for Scientists and Engineers: Numerical Methods

CHAPTER 2 FUNDAMENTALS OF PROGRAMMING WITH VBA 41

The following example causes the InputBox method to return a Range
object (so that you can use its Address property in addition to its Value
property, for example):
Set known-Ys = ApplicationhputBox -
("Select the range of Y values", "STEP 1 OF 2", , , , , , 8)

Visual Basic Arrays

If you're familiar with other programming languages you are probably
familiar with the concept of an array. An array is a collection of related
variables denoted by a single name, such as Sample. You can then specify any
element in the array by using an index number: Sample(l), Sample(7), etc.
Many scientists make extensive use of arrays in their calculations. Because
some aspects of arrays in VBA can be confusing, this chapter provides detailed
coverage of this important topic.

Dimensioning an Array
The Dim (short for Dimension) statement is used to declare the size of an
array. Unless specified otherwise, VBA arrays begin with an index of 0. Thus
the statement
Dim Sample( 10)
establishes array storage for 1 1 elements, Sample(0) through Sarnple(l0).
However, you can specify that the arrays in your procedure begin with an array
index of 1. Since worksheet ranges, worksheet functions and worksheet arrays
use (or assume) a lower array index of 1, always specifying VBA arrays with
lower array index of 1 can eliminate a lot of confusion.
There are two ways to specify the lower array index. You can specify the
lower bound of an array in the Dim statement. For example,
Dim Sample (1 To 10)
sets the lower array index = 1 for the array Sample. It's considered good
programming practice to put the Dim statements at the beginning of the
procedure.
Alternatively, you can use the Option Base 1 statement, which specifies that
all arrays in the procedure begin with a lower index of 1. The Option Base 1
statement is used at the module level: that is, it must appear in a module sheet
before any procedures.