Built-in Functions


Like most programming languages, VBA has a variety of built-in functions that simplify calculations and operations. Many VBA functions are similar (or identical) to Excel worksheet functions. For example, the VBA fun ction UCase, which converts a string argument to uppercase, is equivalent to the Excel worksheet function UPPER.

CROSS-REFERENCE  

Appendix B contains a complete list of VBA functions, with a brief description of each. All are thoroughly described in the VBA Help system.

Tip  

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.

image from book
Figure 8-3: Displaying a list of VBA functions in the VBE.

If this technique doesn't work for you, make sure that the Auto List Members option is selected. Choose Tools image from book Options and then click the Editor tab.

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 
image from book
The MsgBox Function

The MsgBox function is one of the most useful VBA functions. Many of the examples in this chapter use this function to display the value of a variable.

This function often is a good substitute for a simple custom dialog box. It's also an excellent debugging tool because you can insert MsgBox functions at any time to pause your code and display the result of a calculation or assignment.

Most functions return a single value, which you assign to a variable. The MsgBox function not only returns a value, but also displays a dialog box that the user can respond to. The value returned by the MsgBox function represents the user's response to the dialog. You can use the MsgBox function even when you have no interest in the user's response but want to take advantage of the message display.

The official syntax of the MsgBox function has five arguments (those in square brackets are optional):

 MsgBox(prompt[, buttons][, title][, helpfile, context]) 
  • prompt : (Required) The message displayed in the pop-up display.

  • buttons : (Optional) A value that specifies which buttons and which icons, if any, appear in the message box. Use built-in constants - for example, vbYesNo .

  • title : (Optional) The text that appears in the message box's title bar. The default is Microsoft Excel .

  • helpfile : (Optional) The name of the help file associated with the message box.

  • context : (Optional) The context ID of the help topic. This represents a specific help topic to display. If you use the context argument, you must also use the helpfile argument.

You can assign the value returned to a variable, or you can use the function by itself without an assignment statement. This example assigns the result to the variable Ans .

 Ans = MsgBox("Continue?", vbYesNo + vbQuestion, "Tell me") If Ans = vbNo Then Exit Sub 

Notice that I used the sum of two built-in constants ( vbYesNo + vbQuestion ) for the buttons argument. Using vbYesNo displays two buttons in the message box: one labeled Yes and one labeled No. (See the results in the accompanying figure.) Adding vbQuestion to the argument also displays a question mark icon (see the accompanying figure). When the first statement is executed, Ans contains one of two values, represented by the constant vbYes or vbNo . In this example, if the user clicks the No button, the procedure ends.

image from book

See Chapter 12 for more information about the MsgBox function.

image from book
 

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 = 2007     RomanValue = Application.WorksheetFunction.Roman(DecValue)     MsgBox RomanValue End Sub 

When you execute this procedure, the MsgBox function displays the string MMVII . Fans of old movies are often dismayed when they learn that Excel doesn't have a function to convert a Roman numeral to its decimal equivalent.

Keep in mind that you cannot use worksheet functions that have an equivalent VBA function. For example, VBA cannot 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 
CROSS-REFERENCE  

As I describe in Chapter 10, you can use VBA to create custom worksheet functions that work just like Excel's built-in worksheet functions.




Excel 2007 Power Programming with VBA
Excel 2007 Power Programming with VBA (Mr. Spreadsheets Bookshelf)
ISBN: 0470044012
EAN: 2147483647
Year: 2007
Pages: 319

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net