Chapter 9

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. There are three flavors of functions: those built into VBA (vanilla), those built into Excel (strawberry), and other functions written in VBA (chocolate). I provide a full and tasty 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?

Except for a few people who think Excel is a word processor, all Excel users incorporate 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 Built-In 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 and also returns a value. For more details about the MsgBox function, see Chapter 15.

A workbook that contains all the examples is available at this book’s website.

Displaying the system date or time

The first example uses VBA’s Date function to display the current system date in a message box:

Sub ShowDate()

    MsgBox "Today is: " & Date

End Sub

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 type an empty set of parentheses, the VBE will promptly remove them.

To get the system time, use the Time function. And if you want it all, use the Now function to return both the date and the time.

Finding a string length

The following procedure uses the VBA Len function, which returns the length of a text string. The Len function takes one argument: the string. When you execute this procedure, the message box displays your name, and the number of characters in your name (see Figure 9-1).

Sub GetLength()

    Dim MyName As String

    Dim StringLength As Integer

    MyName = Application.UserName

    StringLength = Len(MyName)

    MsgBox MyName & " has " & StringLength & " characters."

End Sub

Figure 9-1: Calculating the length of your name.

Excel also has a LEN function, which you can use in your worksheet formulas. The Excel version and the VBA function work the same.

Displaying the name of a month

The following procedure uses the MonthName function, which returns the name of a month. MonthName uses one argument, an integer between 1 and 12.

Sub ShowMonthName()

    Dim ThisMonth As Long

    ThisMonth = Month(Date)

    MsgBox MonthName(ThisMonth)

End Sub

This procedure uses the Month function to get the current month (as a value), and this value is assigned to the ThisMonth variable. The MonthName function then converts the value to text. So if you run this procedure in April, the message box displays the text April.

Actually, the ThisMonth variable isn’t required. You can get the same effect with this expression that uses three VBA functions:

MonthName(Month(Date))

Here, the current date is passed as an argument to the Month function, which returns a value that is passed as an argument to the MonthName function.

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.

Sub GetFileSize()

    Dim TheFile As String

    TheFile = "C:Program Files (x86)Microsoft OfficeOffice15EXCEL.EXE"

    MsgBox FileLen(TheFile)

End Sub

Notice that this routine hard codes the filename (that is, it explicitly states the path). 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:

TheFile = Application.Path & "EXCEL.EXE"

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 TypeName function, which returns the type of the selection on the worksheet (as a string):

Sub ShowSelectionType()

    Dim SelType As String

    SelType = TypeName(Selection)

    MsgBox SelType

End Sub

The selection could be a Range, a Picture, a Rectangle, a ChartArea, 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 VBA 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.
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. Another way is to type VBA, followed by a period. You get a list of items, as shown in Figure 9-2. Those with a green icon are functions. If this feature isn’t working, choose the VBE’s Tools⇒Options command, click the Editor tab, and place a checkmark next to Auto List Members.

Figure 9-2: A way to display a list of VBA functions.

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.
Choose	Returns a value from a list of items.
Chr	Converts an ANSI value to a string.
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.
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.
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.
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.
InStrRev	Returns the position of a string within another, from the end of a string.
Int	Returns the integer portion of a number.
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.
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.
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.
Replace	Replaces a substring in a string with another substring.
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.
Second	Returns the seconds portion of a time value.
Shell	Runs an executable program.
Space	Returns a string with a specified number of spaces.
Split	Splits a string into parts, using a delimiting character.
Sqr	Returns a number’s square root.
String	Returns a repeating character or string.
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.
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 WorksheetFunction object, which is contained in the Application object. Here’s an example of how you can use Excel’s SUM function in a VBA statement:

Total = Application.WorksheetFunction.Sum(Range("A1:A12"))

You can omit the Application part or the WorksheetFunction part of the expression. In either case, VBA will figure out what you’re doing. In other words, these three expressions all work exactly the same:

Total = Application.WorksheetFunction.Sum(Range("A1:A12"))

Total = WorksheetFunction.Sum(Range("A1:A12"))

Total = Application.Sum(Range("A1:A12"))

My personal preference is to use the WorksheetFunction 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 that shows how to use Excel’s MAX worksheet function in a VBA procedure. This procedure displays the maximum value in column A on the active worksheet (see Figure 9-3):

Sub ShowMax()

    Dim TheMax As Double

    TheMax = WorksheetFunction.MAX(Range("A:A"))

    MsgBox TheMax

End Sub

Figure 9-3: Using a worksheet function in your VBA code.

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:

SecondHighest = WorksheetFunction.LARGE(Range("A:A"),2)

Notice that the LARGE function uses two arguments; the second argument represents the k-th 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.

Sub PmtCalc()

    Dim IntRate As Double

    Dim LoanAmt As Double

    Dim Periods As Integer

    IntRate = 0.0825 / 12

    Periods = 30 * 12

    LoanAmt = 150000

    MsgBox WorksheetFunction.PMT(IntRate, Periods, -LoanAmt)

End Sub

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

MsgBox WorksheetFunction.PMT(0.0825 /12, 360, -150000)

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 VBA’s InputBox and MsgBox functions, plus Excel’s VLOOKUP function. It prompts for a part number and then gets the price from a lookup table. In Figure 9-4, range A1:B13 is named PriceList.

Sub GetPrice()

    Dim PartNum As Variant

    Dim Price As Double

    PartNum = InputBox("Enter the Part Number")

    Sheets("Prices").Activate

    Price = WorksheetFunction.VLOOKUP(PartNum, Range("PriceList"), 2, False)

    MsgBox PartNum & " costs " & Price

End Sub

You can download this workbook from the book’s website.

Figure 9-4: The range, named PriceList, contains prices for parts.

The procedure starts this way:

1. VBA’s InputBox function asks the user for a part number.

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.

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.) If this were an actual application that’s used in an actual business, you would want to add some statements that deal with errors more gracefully. 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.

You can also take advantage of the VBE’s Auto List Members option, which displays a drop-down list of all worksheet functions. Just type Application.WorksheetFunction, followed by a period. Then you’ll see an list of the function you can use, as shown in Figure 9-5. If this feature isn’t working, choose the VBE’s Tools⇒Options command, click the Editor tab, and place a checkmark next to Auto List Members.

Figure 9-5: Getting a list of worksheet functions that you can use in your VBA code.

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.

For most worksheet functions that are unavailable as methods of the WorksheetFunction object, you can use an equivalent VBA built-in operator or function. For example, the MOD worksheet function is not available in the WorksheetFunction object because VBA has an equivalent, its built-in Mod operator.

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.

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 (also known as a User Defined Function, or UDF) is one you develop yourself by using (what else?) VBA. To use a custom function, you must define it in the workbook in which you use it — or else define the functions in an add-in (see Chapter 21).

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

Function MultiplyTwo(num1, num2) As Double

    MultiplyTwo = num1 * num2

End Function

Sub ShowResult()

    Dim n1 As Double, n2 As Double

    Dim Result As Double

    n1 = 123

    n2 = 544

    Result = MultiplyTwo(n1, n2)

    MsgBox Result

End Sub

The custom function MultiplyTwo has two arguments. The ShowResult Sub procedure uses this Function procedure by passing two arguments to it (in parentheses). The ShowResult procedure then displays a message box showing the value returned by the MultiplyTwo function.

I probably don’t have to tell you that the MultiplyTwo function is fairly useless. It’s much more efficient to perform the multiplication in the ShowResult 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 also use custom functions in your worksheet formulas. For example, if MultiplyTwo is defined in your workbook, you can write a formula such as this one:

=MultiplyTwo(A1,A2)

This formula returns the product of the values in cells A1 and A2.

Creating custom worksheet functions is an important (and very useful) topic. So important (and useful) that I devote an entire chapter to it. See Chapter 20 for some examples that are actually useful.