Microsoft Office Tutorials and References
In Depth Information
Using Built-In VBA Functions
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
period (.). The VB Editor displays a list of all functions and constants (see Figure 24-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.
Figure 24-1: Displaying a list of VBA functions in the VB Editor.
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:
 
Search JabSto ::




Custom Search