Microsoft Office Tutorials and References

In Depth Information

**Using Arrays**

Table 24-2:
VBA Logical Operators

Operator

What It Does

Not

Performs a logical negation on an expression

And

Performs a logical conjunction on two expressions

Or

Performs a logical disjunction on two expressions

Xor

Performs a logical exclusion on two expressions

Eqv

Performs a logical equivalence on two expressions

Imp

Performs a logical implication on two expressions

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.

The negation operator (a minus sign) is handled differently in VBA. In Excel, the

following formula returns 25:

=–5^2

In VBA, x equals –25 after this statement is executed:

x = –5 ^ 2

VBA performs the exponentiation operation first, and then applies the negation

operator. The following statement returns 25:

x = (–5) ^ 2

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 comprising exactly 100 numbers (of data type Long):