Microsoft Office Tutorials and References

In Depth Information

**Using Arrays**

Operators play a major role in VBA. Familiar operators describe mathematical

operations, including addition (+), multiplication (*), division (/), subtraction (–),

exponentiation (^), and string concatenation (&). Less familiar operators are the backslash (\)

that’s used in integer division, and the Mod operator that’s used in modulo arithmetic.

VBA also supports the same comparative operators used in Excel formulas: equal

to (=), greater than (>), less than (<), greater than or equal to (>=), less than or equal

to (<=), and not equal to (<>). Additionally, VBA provides a full set of logical

operators, such as
And
,
Not
,
Or
, and so on. The order of precedence for operators in VBA

exactly matches that in Excel. Of course, you can add parentheses to change the

natural order of precedence.

Using Arrays

An
array
is a group of elements of the same type that have a common name. You

refer to a specific element in the array by using the array name and an index

number. For example, you may define an array of 12 string variables so that each

variable corresponds to the name of a different month. If you name the array

MonthNames
, you can refer to the first element of the array as
MonthNames(0)
, the

second element as
MonthNames(1)
, and so on, up to
MonthNames(11)
.

Declaring an array

You declare an array with a
Dim
or
Public
statement just as you declare a regular

variable. You also can specify the number of elements in the array. You do so by

specifying the first index number, the keyword
To
, and the last index number — all

inside parentheses. For example, here’s how to declare an array comprised of

exactly 100 integers:

Dim MyArray(1 To 100) As Integer

When you declare an array, you need to specify only the upper index, in which

case VBA (by default) assumes that 0 is the lower index. Therefore, the following

two statements have the same effect:

Dim MyArray(0 to 100) As Integer

Dim MyArray(100) As Integer

In both cases, the array consists of 101 elements.

Declaring multidimensional arrays

The array examples in the preceding section are one-dimensional arrays. VBA arrays

can have up to 60 dimensions. The following statement declares a 100-integer array

with two dimensions: