Microsoft Office Tutorials and References
In Depth Information
Using VBA's Built-In Functions
Dim MyArray(1 To 10, 1 To 10) As Integer
You can think of the preceding array as occupying a 10 x 10 matrix. To refer to
a specific element in a 2-D array, 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
Arrays crop up later in this chapter in the sections that discuss looping.
Using VBA’s Built-In 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 period ( ). The VB Editor displays a drop-down list of all functions. In .
addition to functions, the displayed list also includes built-in constants. The
VBA functions are all described in the online help. To view help, just move
the cursor over a function name and press F1.
Here’s a statement that calculates the square root of a variable by using VBA’s
Sqr function, and then assigns the result to a variable named x .
x = Sqr(MyValue)
You can use many (but not all) of Excel’s worksheet functions in your VBA code.
To use a worksheet function in a VBA statement, just precede the function name
with WorksheetFunction and a dot. The following code demonstrates how to use
Excel’s MEDIAN worksheet function in a VBA statement.
x = WorksheetFunction.Median(Range(“A1:A10”))
It’s important to understand that you can’t use worksheet functions that have an
equivalent VBA function. For example, VBA can’t access Excel’s SQRT worksheet
function because VBA has its own version of that function: Sqr . Therefore, the
following statement generates an error:
x = Application.Sqrt(123)