Microsoft Office Tutorials and References
In Depth Information
Built-in Functions
To get a list of VBA functions while you’re writing your code, type VBA followed by a
period (.). The VBE displays a list of all its members, including functions (see Figure
8-3). The functions are preceded by a green icon.
If this technique doesn’t work for you, make sure that the Auto List Members option is
selected. Choose Tools➜Options and then click the Editor tab.
Figure 8-3: Displaying a list of VBA functions in the VBE.
You use functions in VBA expressions in much the same way that you use functions in worksheet
formulas. Here’s a simple procedure that calculates the square root of a variable (using the VBA
Sqr function), stores the result in another variable, and then displays the result:
Sub ShowRoot()
Dim MyValue As Double
Dim SquareRoot As Double
MyValue = 25
SquareRoot = Sqr(MyValue)
MsgBox SquareRoot
End Sub
The VBA Sqr function is equivalent to the Excel SQRT worksheet function.
You can use many (but not all) of Excel’s worksheet functions in your VBA code. The
WorksheetFunction object, which is contained in the Application object, holds all the
worksheet functions that you can call from your VBA procedures.
To use a worksheet function in a VBA statement, just precede the function name with
Application.WorksheetFunction
 
Search JabSto ::




Custom Search