Читать книгу Excel 2019 Power Programming with VBA - Michael Alexander, Dick Kusleika - Страница 180

To get a list of VBA functions while you're writing your code, type VBA followed by a period (.).V BE displays a list of all its members, including functions (see Figure 3.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 3.3 Displaying a list of VBA functions in 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 this:

Application.WorksheetFunction

The following example demonstrates how to use an Excel worksheet function in a VBA procedure. Excel's infrequently used ROMAN function converts a decimal number into a Roman numeral.

Sub ShowRoman() Dim DecValue As Long Dim RomanValue As String DecValue = 1939 RomanValue = Application.WorksheetFunction.Roman(DecValue) MsgBox RomanValue End Sub

When you execute this procedure, the MsgBox function displays the string MCMXXXIX.

Keep in mind that you can't use worksheet functions that have an equivalent VBA function. For example, VBA can't access the Excel SQRT worksheet function because VBA has its own version of that function, Sqr. Therefore, the following statement generates an error:

MsgBox Application.WorksheetFunction.Sqrt(123) 'error

In Chapter 5, you will discover that you can use VBA to create custom worksheet functions that work just like Excel's built-in worksheet functions.