Microsoft Office Tutorials and References
In Depth Information
The array examples in the preceding section are one-dimensional arrays. VBA arrays can have up to 60 dimen-
sions although it's rare to need more than three dimensions (a 3-D array). The following statement declares a
100-integer array with two dimensions (2-D):
Dim MyArray(1 To 10, 1 To 10) As Long
You can think of the preceding array as occupying a 10 x 10 matrix. To refer to a specific element in a 2-D ar-
ray, you need to specify two index numbers. For example, here's how you can assign a value to an element in
the preceding array:
MyArray(3, 4) = 125
A dynamic array does not have a preset number of elements. You declare a dynamic array with a blank set of
Dim MyArray() As Long
Before you can use a dynamic array in your code, however, you must use the ReDim statement to tell VBA how
many elements are in the array (or ReDim Preserve if you want to keep the existing values in the array). You
can use the ReDim statement any number of times, changing the array's size as often as you like.
Arrays crop up later in this chapter in the sections that discuss looping.
Using Built-In VBA Functions
VBA has a variety of built-in functions that simplify calculations and operations. Many of VBA's functions are
similar (or identical) to Excel's worksheet functions. For example, the VBA function UCase, which converts a
string argument to uppercase, is equivalent to the Excel worksheet function UPPER.
To display a list of VBA functions while writing your code, type VBA followed by a peri-
od (.). The VB Editor displays a list of all functions and constants (see Figure 25-1). If
this does not work for you, make sure that you select the Auto List Members option.
Choose Tools ⇒ Options and click the Editor tab. In addition to functions, the displayed
list also includes built-in constants. The VBA functions are all described in the online
help. To view Excel Help, just move the cursor over a function name and press F1.