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.

Tip 

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 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:

 x = Sqr(MyValue) 

Having knowledge of VBA's functions can save you lots of work. For example, consider the REMOVESPACES Function procedure presented at the beginning of this chapter. That function uses a For-Next loop to examine each character in a string and builds a new string. A much simpler (and more efficient) version of that Function procedure uses the VBA Replace function. The following is a rewritten version of the Function procedure:

 Function REMOVESPACES2(cell) As String '   Removes all spaces from cell     REMOVESPACES2 = Replace(cell, " ", "") End Function 

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 period.

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

 DecValue = 2007 RomanValue = WorksheetFunction.Roman(DecValue) 

The variable RomanValue contains the string MMVII. Fans of old movies are often dismayed when they learn that Excel does not have a function to convert a Roman numeral to its decimal equivalent. You can, of course, create such a function using VBA. Are you up for a challenge?

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 = WorksheetFunction.SQRT(123) 'error