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:
Search JabSto ::




Custom Search