Using VBA and Worksheet Functions

In This Chapter

Using functions to make your VBA expressions more powerful Using the VBA built-in functions Using Excel worksheet functions in your VBA code Writing custom functions
In previous chapters, I allude to the fact that you can use functions in your VBA expressions. I provide a full explanation in this chapter. Functions can make your VBA code perform some powerful feats, with little or no programming effort required. If you like that idea, this chapter’s for you.

What Is a Function?

All Excel users beyond rank beginners use worksheet functions in their formulas. The most common worksheet function is the SUM function, and you have hundreds of others at your disposal.
A function essentially performs a calculation and returns a single value. The SUM function, of course, returns the sum of a range of values. The same holds true for functions used in your VBA expressions: Each function does its thing and returns a single value.

The functions you use in VBA can come from three sources:

Built-in functions provided by VBA Worksheet functions provided by Excel
Custom functions that you (or someone else) write, using VBA
The rest of this chapter clarifies the differences and (I hope) convinces you of the value of using functions in your VBA code.

Using VBA Functions

VBA provides numerous built-in functions. Some of these functions take arguments and some do not.


VBA function examples

In this section, I present a few examples of using VBA functions in code. In many of these examples, I use the MsgBox function to display a value in a message box. Yes, MsgBox is a VBA function — a rather unusual one, but a function nonetheless. This useful function displays a message in a pop-up dialog box. For more details about the MsgBox function, see Chapter 15.
A workbook that contains all of the examples is available at this topic’s Web site.

Displaying the system date

The first example uses VBA’s Date function to display the current system date in a message box:
tmpD-76_thumb
Notice that the Date function doesn’t use an argument. Unlike worksheet functions, a VBA function with no argument doesn’t require an empty set of parentheses. In fact, if you provide an empty set of parentheses, the VBE will remove them.
To get the system date and time, use the Now function instead of the Date function. Or to get only the time, use the Time function.

Finding a string length

The following procedure uses the VBA Len function, which returns the length of a string. The Len function takes one argument: the string. When you execute this procedure, the message box displays 11 because the argument has 11 characters.
tmpD-77_thumb
Excel also has a function, which you can use in your worksheet formulas. The Excel version and the VBA function work the same.

Displaying the integer part of a number

The following procedure uses the Fix function, which returns the integer portion of a value — the value without any decimal digits:
tmpD-78_thumb
In this case, the message box displays 123.
VBA has a similar function called Int. The difference between Int and Fix is how each deals with negative numbers.
Int returns the first negative integer that’s less than or equal to the argument.
Fix returns the first negative integer that’s greater than or equal to the argument.

Determining a file size

The following Sub procedure displays the size, in bytes, of the Excel executable file. It finds this value by using the FileLen function.
tmpD-79_thumb
Notice that this routine hard codes the filename (that is, it explicitly states the path). Generally, this isn’t a good idea. The file might not be on the C drive, or the Excel folder may have a different name. The following statement shows a better approach:
tmpD-80_thumb
Path is a property of the Application object. It simply returns the name of the folder in which the application (that is, Excel) is installed (without a trailing backslash).

Identifying the type of a selected object

The following procedure uses the Type Name function, which returns the type of the selected object (as a string):
tmpD-81_thumb
This could be a Range, a ChartObject, a TextBox, or any other type of object that can be selected.
The TypeName function is very versatile. You can also use this function to determine the data type of a variable.
VBA functions that do more than return a value
A few VBA functions go above and beyond the call of duty. Rather than simply return a value, these functions have some useful side effects. Table 9-1 lists them.

Table 9-1 Functions with Useful Side Benefits
Function What It Does
MsgBox Displays a handy dialog box containing a message and buttons.
The function returns a code that identifies which button the user
clicks. See Chapter 15 for details.
InputBox Displays a simple dialog box that asks the user for some input.
The function returns whatever the user enters into the dialog
box. I discuss this in Chapter 15.
Function What It Does
Shell Executes another program. The function returns the task ID
(a unique identifier) of the other program (or an error if the
function can’t start the other program).

Discovering VBA functions

How do you find out which functions VBA provides? Good question. The best source is the Excel Visual Basic Help system. I compiled a partial list of functions, which I share with you in Table 9-2. I omitted some of the more specialized or obscure functions.
For complete details on a particular function, type the function name into a VBA module, move the cursor anywhere in the text, and press F1.

Table 9-2 VBA’s Most Useful Built-in Functions
Function What It Does
Abs Returns a number’s absolute value
Array Returns a variant containing an array
Asc Converts the first character of a string to its ASCII value
Atn Returns the arctangent of a number
Choose Returns a value from a list of items
Chr Converts an ANSI value to a string
Cos Returns a number’s cosine
CurDir Returns the current path
Date Returns the current system date
DateAdd Returns a date to which a specified time interval has been
added — for example, one month from a particular date
DateDiff Returns an integer showing the number of specified time intervals
between two dates, for example the number of months between
now and your birthday
DatePart Returns an integer containing the specified part of a given
date — for example, a date’s day of the year
Table 9-2 (continued)
Function What It Does
DateSerial Converts a date to a serial number
DateValue Converts a string to a date
Day Returns the day of the month from a date value
Dir Returns the name of a file or directory that matches a pattern
Erl Returns the line number that caused an error
Err Returns the error number of an error condition
Error Returns the error message that corresponds to an error number
Exp Returns the base of the natural logarithm (e) raised to a power
FileLen Returns the number of bytes in a file
Fix Returns a number’s integer portion
Format Displays an expression in a particular format
GetSetting Returns a value from the Windows registry
Hex Converts from decimal to hexadecimal
Hour Returns the hours portion of a time
InputBox Displays a box to prompt a user for input
InStr Returns the position of a string within another string
Int Returns the integer portion of a number
IPmt Returns the interest payment for an annuity or loan
IsArray Returns True if a variable is an array
IsDate Returns True if an expression is a date
IsEmpty Returns True if a variable has not been initialized
IsError Returns True if an expression is an error value
IsMissing Returns True if an optional argument was not passed to a
procedure
IsNull Returns True if an expression contains no valid data
IsNumeric Returns True if an expression can be evaluated as a number
Function What It Does
IsObject Returns True if an expression references an OLE Automation object
LBound Returns the smallest subscript for a dimension of an array
LCase Returns a string converted to lowercase
Left Returns a specified number of characters from the left of a string
Len Returns the number of characters in a string
Log Returns the natural logarithm of a number to base e
LTrim Returns a copy of a string, with any leading spaces removed
Mid Returns a specified number of characters from a string
Minute Returns the minutes portion of a time value
Month Returns the month from a date value
MsgBox Displays a message box and (optionally) returns a value
Now Returns the current system date and time
RGB Returns a numeric RGB value representing a color
Right Returns a specified number of characters from the right of a string
Rnd Returns a random number between 0 and 1
RTrim Returns a copy of a string, with any trailing spaces removed
Second Returns the seconds portion of a time value
Sgn Returns an integer that indicates a number’s sign
Shell Runs an executable program
Sin Returns a number’s sine
Space Returns a string with a specified number of spaces
Sqr Returns a number’s square root
Str Returns a string representation of a number
StrComp Returns a value indicating the result of a string comparison
String Returns a repeating character or string
Tan Returns a number’s tangent
Table 9-2 (continued)
Function What It Does
Time Returns the current system time
Timer Returns the number of seconds since midnight
TimeSerial Returns the time for a specified hour, minute, and second
TimeValue Converts a string to a time serial number
Trim Returns a string without leading or trailing spaces
TypeName Returns a string that describes a variable’s data type
UBound Returns the largest available subscript for an array’s dimension
UCase Converts a string to uppercase
Val Returns the numbers contained in a string
VarType Returns a value indicating a variable’s subtype
Weekday Returns a number representing a day of the week
Year Returns the year from a date value

Using Worksheet Functions in VBA

Although VBA offers a decent assortment of built-in functions, you might not always find exactly what you need. Fortunately, you can also use most of Excel’s worksheet functions in your VBA procedures. The only worksheet functions that you cannot use are those that have an equivalent VBA function.
VBA makes Excel’s worksheet functions available through the Worksheet Function object, which is contained in the Application object. (Remember, the Application object is Excel.) Therefore, any statement that uses a worksheet function must use the Application. Worksheet Function qualifier. In other words, you must precede the function name with Application.Worksheet Function (with a dot separating the two). The following is an example:
tmpD-82_thumb
You can omit the Application part of the expression because it’s assumed. You can also omit the Worksheet Function part of the expression; VBA will determine that you want to use an Excel worksheet function. But if you do so, then you must include the Application part. In other words, these three expressions all work exactly the same:
tmpD-83_thumb
My personal preference is to use the Worksheet Function part just to make it perfectly clear that the code is using an Excel function.

Worksheet function examples

In this section, I demonstrate how to use worksheet functions in your VBA expressions.

Finding the maximum value in a range

Here’s an example showing how to use the MAX worksheet function in a VBA procedure. This procedure displays the maximum value in the range named Number List on the active worksheet:
tmpD-84_thumb
You can use the MIN function to get the smallest value in a range. And, as you might expect, you can use other worksheet functions in a similar manner. For example, you can use the LARGE function to determine the kth-largest value in a range. The following expression demonstrates this:
tmpD-85_thumb
Notice that the LARGE function uses two arguments; the second argument represents the kth part — 2 in this case (the second-largest value).

Calculating a mortgage payment

The next example uses the PMT worksheet function to calculate a mortgage payment. I use three variables to store the data that’s passed to the Pmt function as arguments. A message box displays the calculated payment.
tmpD-86_thumb

As the following statement shows, you can also insert the values directly as the function arguments:

tmpD-87_thumb
However, using variables to store the parameters makes the code easier to read and modify, if necessary.

Using a lookup function

The following example uses the simple lookup table shown in Figure 9-1. Range A1:B13 is named Price List.
tmpD-88_thumb
You can download this workbook from the topic’s Web site.
The range, named Price List, contains prices for parts.
Figure 9-1:
The range, named Price List, contains prices for parts.

The procedure starts this way:

1. VBA’s InputBox function asks the user for a part number.
Figure 9-2 shows the Microsoft Excel dialog box that displays when this statement is executed.
2. This statement assigns the part number the user enters for the PartNum variable.
3. The next statement activates the Prices worksheet, just in case it’s not already the active sheet.
Use the InputBox function to get the user's input.
Figure 9-2:
Use the InputBox function to get the user’s input.
4. The code uses the VLOOKUP function to find the part number in the table.
Notice that the arguments you use in this statement are the same as those you would use with the function in a worksheet formula. This statement assigns the result of the function to the Price variable.
5. The code displays the price for the part via the MsgBox function.
This procedure doesn’t have any error handling, and it fails miserably if you enter a nonexistent part number. (Try it.) Add some error-handling statements for a more robust procedure. I discuss error handling in Chapter 12.


Entering worksheet functions

You can’t use the Excel Paste Function dialog box to insert a worksheet function into a VBA module. Instead, enter such functions the old-fashioned way: by hand. However, you can use the Paste Function dialog box to identify the function you want to use and find out about its arguments.
1. Activate a worksheet.
2. Choose Insert Function as you normally would.
3. Figure out how the function works.
4. Type the function and its arguments into your module.
Follow these steps to display the VBE’s Auto List Members option, which displays a drop-down list of all worksheet functions:
1. Type Application.Worksheet Function, followed by a period.
2. If this feature isn’t working, choose the VBE’s Tools Options command.
3. Click the Editor tab.
4. Place a check mark next to Auto List Members.

More about Using Worksheet Functions

Newcomers to VBA often confuse VBA’s built-in functions and Excel’s workbook functions. A good rule to remember is that VBA doesn’t try to reinvent the wheel. For the most part, VBA doesn’t duplicate Excel worksheet functions.
Bottom line? If you need to use a function, first determine whether VBA has something that meets your needs. If not, check out the worksheet functions. If all else fails, you may be able to write a custom function by using VBA.
The Worksheet Function object contains the worksheet functions available to VBA procedures. To see a list of these functions, you can use the Object Browser as shown in Figure 9-3. Follow these steps to display a complete list of worksheet functions available in VBA:
1. In the VBE, press F2.
The Object Browser appears.
2. In the Project/Library drop-down list (the one in the upper-left corner of the Object Browser), select Excel.
3. In the list labeled Classes, select Worksheet Function.
The Members of list shows all the worksheet functions you can use in your code.
For most worksheet functions that are unavailable as methods of the Application object, you can use an equivalent VBA built-in operator or function. For example, the MOD worksheet function is unavailable in
the Worksheet Function object because VBA has an equivalent, built-in Mod operator. This is by design — a VBA operator works faster than an Excel function in a VBA module.
Use the Object Browser to show the worksheet function available in VBA.
Figure 9-3:
Use the Object Browser to show the worksheet function available in VBA.

Using Custom Functions

I’ve covered VBA functions and Excel worksheet functions. The third category of functions you can use in your VBA procedures is custom functions. A custom function is one you develop yourself using (what else?) VBA. To use a custom function, you must define it in the workbook in which you use it.

Here’s an example of defining a simple Function procedure and then using it in a VBA Sub procedure:

tmpD-92_thumb
The custom function Multiply Two has two arguments. The Show Result Sub procedure uses this Function procedure by passing two arguments to it (in parentheses). The Show Result procedure then displays a message box showing the value returned by the Multiply Two function.
The Multiply Two function is fairly useless. It’s much more efficient to perform the multiplication in the Show Result Sub procedure. I include it simply to give you an idea of how a Sub procedure can make use of a custom function.
You can use custom functions also in your worksheet formulas. For example, if Multiply Two is defined in your workbook, you can write a formula such as this one:
tmpD-93_thumb
This formula returns the product of the values in cells A1 and A2.
Custom worksheet functions is an important (and very useful) topic. So important (and useful) that I devote an entire chapter to it. See Chapter 21.

Next post:

Previous post: