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)