Chapter 39: Creating Custom
Worksheet Functions
In This Chapter
Getting an overview of VBA functions
Looking at function procedures
Focusing on function procedure arguments
Debugging custom functions
Pasting custom functions
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.
Overview of VBA Functions
Function procedures that you write in VBA are quite versatile. You can use these functions in two situations:
• You can call the function from a different VBA procedure.
• You can use the function in formulas that you create in a worksheet.
This chapter focuses on creating functions for use in your formulas.
Excel includes more than 400 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.
Note
This chapter assumes that you're familiar with entering and editing VBA code in the Visual Basic (VB) Editor.
See Chapter 38 for an overview of the VB Editor.
An Introductory Example
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.
A custom function
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 non-numeric, the function returns an empty string. The NumSign
function is shown in Figure 39.1.
Figure 39.1
A simple custom worksheet function.
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.
Using the function in a worksheet
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 39.2. You can also nest custom functions and combine them with other elements in your formulas.
Figure 39.2
Creating a worksheet formula that uses a custom function.
Analyzing the custom function
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 38) 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 non-numeric. 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 non-numeric. 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”)),””)
About Function Procedures
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:
1. Activate the VB Editor (press Alt+F11).
2. Select the workbook in the Project window.
3. Choose Insert ⇒ Module to insert a VBA module. Or you can use an existing code module. However, it must be a standard VBA module.
4. Enter the keyword Function followed by the function's name and a list of the arguments (if any) in parentheses. If the function doesn't use an argument, the VB Editor adds a set of empty parentheses.
5. Insert the VBA code that performs the work — and make sure that the variable corresponding to the function's name has the appropriate value when the function ends. This is the value that the function returns.
6. End the function with an End Function statement.
Note
Step 3 is very important. If you put a function procedure in a code module for ThisWorkbook or a worksheet (for example, Sheet1), the function will not be recognized in a worksheet formula. Excel will display a #NAME? error.
Function names that are used in worksheet formulas must adhere to the same rules as variable names.
Executing Function Procedures
You can execute a Sub
procedure in many ways, but you can execute a Function
procedure in just two ways:
• Call it from another VBA procedure.
• Use it in a worksheet formula.
Calling custom functions from a procedure
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.
Using custom functions in a worksheet formula
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:
• Precede the function's name with a file reference. For example, if you want to use a function called CountNames
that's defined in a workbook named MyFunctions
, you can use a reference such as the following:
=MyFunctions.xlsm!CountNames(A1:A1000)
If the workbook name contains a space, you need to add single quotes around the workbook name. For example
='My Functions.xlsm'!CountNames(A1:A1000)
If you insert the function with the Insert Function dialog box, the workbook reference is inserted automatically.
• Set up a reference to the workbook. If the custom function is defined in a referenced workbook, you don't need to precede the function name with the workbook name. You establish a reference to another workbook by choosing Tools ⇒ References (in the VB Editor). You're presented with a list of references that includes all open workbooks. Place a check mark in the item that refers to the workbook that contains the custom function. (Click the Browse button if the workbook isn't open.)
• Create an add-in. When you create an add-in from a workbook that has function procedures, you don't need to use the file reference when you use one of the functions in a formula; the add-in must be installed, however.
Chapter 44 discusses add-ins.
Note
Function procedures don't appear in the Macro dialog box because you can't execute a function directly. As a result, you need to do extra, upfront work to test your functions while you're developing them. One approach is to set up a simple Sub procedure that calls the function. If the function is designed to be used in worksheet formulas, you can enter a simple formula that uses the function to test it while you're developing the function.
Function Procedure Arguments
Keep in mind the following about function procedure arguments:
• Arguments can be variables (including arrays), constants, literals, or expressions.
• Some functions do not have arguments.
• Some functions have a fixed number of required arguments (from 1 to 60).
• Some functions have a combination of required and optional 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.
On the Web
The examples in this chapter are available on this book's website. The file is named VBA functions.xlsm.
A function with no argument
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 very 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.
A function with one argument
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
Note
To hear the synthesized voice, your system must be set up to play sound.
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.
Another function with one argument
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)
A function with two arguments
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)
A function with a range argument
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.
A simple but useful function
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 very 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 string
• n
: The element number within the string
• Separator
: A single character that represents the separator
Here'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 the extracts the first name from the name in cell A1:
=EXTRACTELEMENT(A1,1,” “)
Debugging Custom Functions
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 run-time error message that helps you to 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:
• Place 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.
• Test the procedure by calling it from a Sub
procedure. Run-time errors display normally, and you can either fix the problem (if you know what it is) or jump right into the debugger.
• Set a breakpoint in the function and then use the Excel debugger to step through the function. Press F9, and the statement at the cursor becomes a breakpoint. The code will stop executing, and you can step through the code line by line (by pressing F8). Consult the Help system for more information about using VBA debugging tools.
Inserting Custom Functions
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.
Note
Function procedures that are defined with the Private keyword do not appear in the Insert Function dialog box. So, if you create a function that will be used only by other VBA procedures, you should declare the function by using the Private keyword.
You also can display a description of your custom function in the Insert Function dialog box. To do so, follow these steps:
1. Create the function in a module by using the VB Editor.
2. Activate Excel.
3. Choose Developer ⇒ Code ⇒ Macros. The Macro dialog box appears.
4. Type the name of the function in the Macro Name field. Notice that functions don't typically appear in this dialog box, so you must enter the function name yourself.
5. Click the Options button. The Macro Options dialog box, shown in Figure 39.3, appears.
Figure 39.3
Entering a description for a custom function. This description appears in the Insert Function dialog box.
6. Enter a description of the function and then click OK. The Shortcut key field is irrelevant for functions.
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 39.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 executing it, the description, category, and argument descriptions are stored in the file.
Figure 39.4
Using the Function Arguments dialog box to insert a custom function.
Learning More
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.
See Chapter 43 for more examples of useful VBA functions. You may be able to use the examples directly or adapt them for your needs.