IN THIS CHAPTER
As mentioned in the preceding chapter, you can create two types of VBA procedures: Sub
procedures and Function
procedures. This chapter focuses on Function
procedures.
Function procedures that you write in VBA are quite versatile. You can use these functions in two situations:
This chapter focuses on creating functions for use in your formulas.
Excel includes more than 450 predefined worksheet functions. With so many from which to choose, you may be curious as to why anyone would need to develop additional functions. The main reason is that creating a custom function can greatly simplify your formulas by making them shorter, and shorter formulas are more readable and easier to work with. For example, you can often replace a complex formula with a single function. Another reason is that you can write functions to perform operations that would otherwise be impossible.
Creating custom functions is relatively easy after you understand VBA. Without further ado, here's an example of a VBA function procedure. This function is stored in a VBA module, which is accessible from the VB Editor.
This example function, named NumSign
, uses one argument. The function returns a text string of Positive
if its argument is greater than zero, Negative
if the argument is less than zero, and Zero
if the argument is equal to zero. If the argument is nonnumeric, the function returns an empty string. The NumSign
function is shown in Figure 40.1.
You can, of course, accomplish the same effect with the following worksheet formula, which uses nested IF
functions:
=IF(A1=0,"Zero",IF(A1>0,"Positive","Negative"))
Many would agree that the custom function solution is easier to understand and to edit than the worksheet formula.
When you enter a formula that uses the NumSign
function, Excel executes the function to get the result. This custom function works just like any built-in worksheet function. You can insert it in a formula by choosing Formulas Function Library Function Wizard, which displays the Insert Function dialog box. (Custom functions are listed in the User Defined category.) When you select the function from the list, you can then use the Function Arguments dialog box to specify the arguments for the function, as shown in Figure 40.2. You can also nest custom functions and combine them with other elements in your formulas.
This section describes the NumSign
function. Here again is the code:
Function NumSign(num)
Select Case num
Case Is < 0
NumSign = "Negative"
Case 0
NumSign = "Zero"
Case Is > 0
NumSign = "Positive"
End Select
End Function
Notice that the procedure starts with the keyword Function
, followed by the name of the function (NumSign
). This custom function uses one argument (num
), and the argument's name is enclosed in parentheses. The num
argument represents the cell or variable that is to be processed. When the function is used in a worksheet, the argument can be a cell reference (such as A1
) or a literal value (such as –123
). When the function is used in another procedure, the argument can be a numeric variable, a literal number, or a value that is obtained from a cell.
The NumSign
function uses the Select Case
construct (described in Chapter 39) to take a different action, depending on the value of num
. If num
is less than zero, NumSign
is assigned the text Negative
. If num
is equal to zero, NumSign
is Zero
. If num
is greater than zero, NumSign
is Positive
. The value returned by a function is always assigned to the function's name.
If you work with this function, you might notice a problem if the argument is nonnumeric. In such a case, the function returns Positive
. In other words, the function has a bug. Following is a revised version that returns an empty string if the argument is nonnumeric. This code uses the VBA IsNumeric
function to check the argument. If it's numeric, the code checks the sign. If the argument is not numeric, the Else
part of the If-Then-Else
structure is executed:
Function NumSign(num)
If IsNumeric(num) Then
Select Case num
Case Is < 0
NumSign = "Negative"
Case 0
NumSign = "Zero"
Case Is > 0
NumSign = "Positive"
End Select
Else
NumSign = ""
End If
End Function
Without using a custom function, you would need the following formula to get the same result:
=IF(ISNUMBER(A1),IF(A1<0,"Negative",IF(A1>0,"Positive","Zero")),"")
A custom Function
procedure has much in common with a Sub
procedure. Function procedures have some important differences, however. Perhaps the key difference is that a function returns a value (which can be a number or a text string). When writing a Function
procedure, the value that's returned is the value that has been assigned to the function's name when a function is finished executing.
To create a custom function, follow these steps:
End Function
statement.Function names that are used in worksheet formulas must adhere to the same rules as variable names.
You can execute a Sub
procedure in many ways, but you can execute a Function
procedure in just two ways:
You can call custom functions from a VBA procedure just as you call built-in VBA functions. For example, after you define a function called CalcTax
, you can enter a statement such as the following:
Tax = CalcTax(Amount, Rate)
This statement executes the CalcTax
custom function with Amount
and Rate
as its arguments. The function's result is assigned to the Tax
variable.
You use a custom function in a worksheet formula just as you use built-in functions. However, you must ensure that Excel can locate the function. If the function procedure is in the same workbook, you don't have to do anything special. If the function is defined in a different workbook, you may have to tell Excel where to find the function. The following are the three ways in which you can do this:
CountNames
that's defined in a workbook named MyFunctions
, you can use a reference such as the following:
=MyFunctions.xlsm!CountNames(A1:A1000)
='My Functions.xlsm'!CountNames(A1:A1000)
Keep in mind the following about function procedure arguments:
The following sections present a series of examples that demonstrate how to use arguments effectively with functions. Coverage of optional arguments is beyond the scope of this book.
Most functions use arguments, but that's not a requirement. Excel, for example, has a few built-in worksheet functions that don't use arguments, such as RAND
, TODAY
, and NOW
.
The following is a simple example of a function that has no arguments. This function returns the UserName
property of the Application
object, which is the name that appears in the Personalize section of the Excel Options dialog box. This function is simple, but it can be useful because no other way is available to get the user's name to appear in a worksheet formula:
Function User()
' Returns the name of the current user
User = Application.UserName
End Function
When you enter the following formula into a worksheet cell, the cell displays the name of the current user:
=User()
As with Excel's built-in functions, when you use a function with no arguments, you must include a set of empty parentheses.
The function that follows takes a single argument and uses the Excel text-to-speech generator to “speak” the argument:
Function SayIt(txt)
Application.Speech.Speak (txt)
End Function
For example, if you enter this formula, Excel will “speak” the contents of cell A1 whenever the worksheet is recalculated:
=SayIt(A1)
You can use this function in a slightly more complex formula, as shown here. In this example, the argument is a text string rather than a cell reference:
=IF(SUM(A:A)>1000,SayIt("Goal reached"),)
This formula calculates the sum of the values in Column A. If that sum exceeds 1,000, you will hear “Goal reached.”
When you use the SayIt
function in a worksheet formula, the function always returns 0
because a value is not assigned to the function's name.
This section contains a more complex function that is designed for a sales manager who needs to calculate the commissions earned by the sales force. The commission rate is based on the amount sold — those who sell more earn a higher commission rate. The function returns the commission amount, based on the sales made (which is the function's only argument — a required argument). The calculations in this example are based on the following table.
Monthly Sales | Commission Rate |
0–$9,999 | 8.0% |
$10,000–$19,999 | 10.5% |
$20,000–$39,999 | 12.0% |
$40,000+ | 14.0% |
You can use any of several different methods to calculate commissions for various sales amounts that are entered into a worksheet. You could write a formula such as the following:
=IF(AND(A1>=0,A1<=9999.99),A1*0.08,IF(AND(A1>=10000,
A1<=19999.99), A1*0.105,IF(AND(A1>=20000,
A1<=39999.99),A1*0.12,IF(A1>=40000,A1*0.14,0))))
This approach isn't the best for a couple of reasons. First, the formula is overly complex and difficult to understand. Second, the values are hard-coded into the formula, making the formula difficult to modify if the commission structure changes.
A better solution is to use a lookup table function to compute the commissions; for example:
=VLOOKUP(A1,Table,2)*A1
Using the VLOOKUP
function requires that you have a table of commission rates set up in your worksheet.
Another option is to create a custom function, such as the following:
Function Commission(Sales)
' Calculates sales commissions
Tier1 = 0.08
Tier2 = 0.105
Tier3 = 0.12
Tier4 = 0.14
Select Case Sales
Case 0 To 9999.99
Commission = Sales * Tier1
Case 10000 To 19999.99
Commission = Sales * Tier2
Case 20000 To 39999.99
Commission = Sales * Tier3
Case Is >= 40000
Commission = Sales * Tier4
End Select
End Function
After you define the Commission
function in a VBA module, you can use it in a worksheet formula. Entering the following formula into a cell produces a result of 3,000. (The amount, 25,000, qualifies for a commission rate of 12%.)
=Commission(25000)
If the sales amount is in cell D23, the function's argument would be a cell reference, like this:
=Commission(D23)
This example builds on the previous one. Imagine that the sales manager implements a new policy: the total commission paid is increased by 1% for every year that the salesperson has been with the company. For this example, the custom Commission
function (defined in the preceding section) has been modified so that it takes two arguments, both of which are required arguments. Call this new function Commission2
:
Function Commission2(Sales, Years)
' Calculates sales commissions based on years in service
Tier1 = 0.08
Tier2 = 0.105
Tier3 = 0.12
Tier4 = 0.14
Select Case Sales
Case 0 To 9999.99
Commission2 = Sales * Tier1
Case 10000 To 19999.99
Commission2 = Sales * Tier2
Case 20000 To 39999.99
Commission2 = Sales * Tier3
Case Is >= 40000
Commission2 = Sales * Tier4
End Select
Commission2 = Commission2 + (Commission2 * Years / 100)
End Function
The modification was quite simple. The second argument (Years
) was added to the Function
statement, and an additional computation was included that adjusts the commission before exiting the function.
The following is an example of how you write a formula by using this function. It assumes that the sales amount is in cell A1 and that the number of years that the salesperson has worked is in cell B1:
=Commission2(A1,B1)
The example in this section demonstrates how to use a worksheet range as an argument. Actually, it's not at all tricky; Excel takes care of the details behind the scenes.
Assume that you want to calculate the average of the five largest values in a range named Data
. Excel doesn't have a function that can do this calculation, so you can write the following formula:
=(LARGE(Data,1)+LARGE(Data,2)+LARGE(Data,3)+
LARGE(Data,4)+LARGE(Data,5))/5
This formula uses Excel's LARGE
function, which returns the nth largest value in a range. The preceding formula adds the five largest values in the range named Data
and then divides the result by 5. The formula works fine, but it's rather unwieldy. Plus, what if you need to compute the average of the top six values? You'd need to rewrite the formula and make sure that all copies of the formula also get updated.
Wouldn't it be easier if Excel had a function named TopAvg
? For example, you could use the following (nonexistent) function to compute the average:
=TopAvg (Data,5)
This situation is an example of when a custom function can make things much easier for you. The following is a custom VBA function, named TopAvg
, which returns the average of the top n values in a range:
Function TopAvg(Data, Num)
' Returns the average of the highest Num values in Data
Sum = 0
For i = 1 To Num
Sum = Sum + WorksheetFunction.Large(Data, i)
Next i
TopAvg = Sum / Num
End Function
This function takes two arguments: Data
(which represents a range in a worksheet) and Num
(the number of values to average). The code starts by initializing the Sum
variable to 0
. It then uses a For-Next
loop to calculate the sum of the nth largest values in the range. (Note that Excel's LARGE
function is used within the loop.) You can use an Excel worksheet function in VBA if you precede the function with WorksheetFunction
and a period. Finally, TopAvg
is assigned the value of Sum
divided by Num
.
You can use all Excel worksheet functions in your VBA procedures except those that have equivalents in VBA. For example, VBA has a Rnd
function that returns a random number. Therefore, you can't use Excel's RAND
function in a VBA procedure.
Useful functions don't have to be complicated. The function in this section is essentially a wrapper for a built-in VBA function called Split
. The Split
function makes it easy to extract an element in a delimited string. The function is named ExtractElement
:
Function ExtractElement(Txt, n, Separator)
' Returns the nth element of a text string, where the
' elements are separated by a specified separator character
ExtractElement = Split(Application.Trim(Txt), Separator)(n - 1)
End Function
The function takes three arguments:
Txt
: A delimited text string, or a reference to a cell that contains a delimited text stringn
: The element number within the stringSeparator
: A single character that represents the separatorHere's a formula that uses the ExtractElement
function:
=EXTRACTELEMENT("123-45-678",2,"-")
The formula returns 45
, the second element in the string that's delimited by hyphens.
The delimiter can also be a space character. Here's a formula that extracts the first name from the name in cell A1:
=EXTRACTELEMENT(A1,1," ")
Debugging a Function
procedure can be a bit more challenging than debugging a Sub
procedure. If you develop a function to use in worksheet formulas, an error in the Function
procedure simply results in an error display in the formula cell (usually #VALUE!
). In other words, you don't receive the normal runtime error message that helps you locate the offending statement.
When you're debugging a worksheet formula, using only one instance of the function in your worksheet is the best technique. The following are three methods that you may want to use in your debugging:
MsgBox
functions at strategic locations to monitor the value of specific variables. Fortunately, message boxes in function procedures pop up when the procedure is executed. But make sure that you have only one formula in the worksheet that uses your function; otherwise, the message boxes appear for each formula that's evaluated.Sub
procedure. Runtime errors display normally, and you can either fix the problem (if you know what it is) or jump right into the debugger.The Excel Insert Function dialog box makes it easy to identify a function and insert it into a formula. This dialog box also displays custom functions written in VBA. After you select a function, the Function Arguments dialog box prompts you for the function's arguments.
You also can display a description of your custom function in the Insert Function dialog box. To do so, follow these steps:
The description that you enter appears in the Insert Function dialog box.
Another way to provide a description for a custom function is to execute a VBA statement that uses the MacroOptions
method. The MacroOptions
method also lets you assign your function to a specific category and even provide a description of the arguments. The argument descriptions display in the Function Arguments dialog box, which appears after you select the function in the Insert Function dialog box. Excel 2010 added the ability to provide descriptions of function arguments.
Figure 40.4 shows the Function Arguments dialog box, which prompts the user to enter arguments for a custom function (TopAvg
). This function appears in function category 3 (Math and Trig). I added the description, category, and argument descriptions by executing this Sub
procedure:
Sub CreateArgDescriptions()
Application.MacroOptions Macro:="TopAvg", _
Description:="Calculates the average of the top n values in a range", _
Category:=3, _
ArgumentDescriptions:=Array("The range that contains the data", "The value of n")
End Sub
The category numbers are listed in the VBA Help system. You execute this procedure only one time. After you execute it, the description, category, and argument descriptions are stored in the file.
The information in this chapter only scratches the surface when it comes to creating custom functions. It should be enough to get you started, however, if you're interested in this topic.