Page 80 - Excel Progamming Weekend Crash Course

d540629 ch04.qxd 9/2/03 9:28 AM Page 55

Session 4 — Syntax and Data in VBA 55

types to use. Rather, restrict use of this data type to situations where its flexibility is really
needed. This includes:
For data that must be treated as a number or as a string depending on the
circumstances.
For procedure arguments that can take different kinds of data.
For retrieving data from worksheet cells when you do not know what kind of data
the cell contains.

Working with procedure arguments is covered is Session 7, and retrieving
data from worksheet cells is covered in Session 10.
Cross-Ref

Using Arrays
An array lets you store multiple data items in one place. The array has a name, and the indi-
vidual items are identified by a numeric index. When you create an array, you specify one
of VBA’s data types, and all the elements in the array are of that type. Arrays are useful for
storing related data together in one location. For example, suppose your program needs to
work with sales figures for the 12 months of the year. You could create an array with 12 ele-
ments and store January’s figure in element 1, February’s in element 2, and so on. Using an
array for data often makes your code simpler and easier to write. VBA has two types of
arrays — static and dynamic.

Static Arrays
A static array has a fixed size or number of elements. You specify the array size when you
declare it, and the size cannot change during program execution. The declaration syntax is:

Dim ArrayName(n) As Type
ArrayName must follow the usual VBA naming rules, and type can be any of VBA’s data
types. The size of the array is specified by n. Actually, the size will be one more than n
because VBA array indexes start by default at 0; therefore, the declaration

Dim MyArray(50) As Integer
actually results in an array with 51 members, at indexes 0 through 50. Array elements
are referenced using the array name and an index in parentheses:

MyArray(0) = 1
MyArray(25) = 72

You can use an integer variable or constant as the index:
Dim idx As Integer
idx = 12
MyArray(idx) = 44 ‘ Same as MyArray(12)