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 VBE.
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. Figure 40.1 shows the NumSign function.
You can, of course, accomplish the same effect with the following worksheet formula, which uses nested IF
functions:
=IF(ISNUMBER(A1),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 ➪ Insert Function, 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)
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
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 value 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 first statement inside the function is an If statement. It starts what is known as an If block. An If block consists of an If statement, an End If statement, one or more optional Else If statements, and one optional Else statement. The previous code is indented in a way that makes it obvious that the Else and End If statements near the bottom of the function belong to the If statement near the top of the procedure. Indenting is optional, but you'll find your code is much easier to read if you do it.
The If statement contains the built-in function IsNumeric that returns a True if the argument is a number and False if it's not. Whenever a built-in function begins with Is or Has, it returns True or False (a Boolean value).
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.
There is often more than one way to accomplish the same goal in VBA. Instead of using a Select Case construct, you could use an If block. The following code returns the same result as the original function but uses another If block with an Else If
statement. Note how the indented code makes it easy to see which statements belong to which If blocks:
Function NumSignIfBlock(num)
If IsNumeric(num) Then
If num = 0 Then
NumSign = "Zero"
ElseIf num> 0 Then
NumSign = "Positive"
Else
NumSign = "Negative"
End If
Else
NumSign = ""
End If
End Function
A custom function 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 (such as a number, a date, or a text string). The value that's returned by the function is the value that has been assigned to the function's name when the function is finished executing.
To create a custom function, follow these steps:
End Function
statement. The VBE adds this statement automatically when you type the 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:
MyFunctions.xlsm
, 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 on the General tab of the Excel Options dialog box. This function is simple, but it can be useful because there's no built-in function that returns the user's name:
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 strips out any characters that aren't numbers:
Function NumbersOnly(txt)
Dim i As Long
For i = 1 To Len(txt)
If IsNumeric(Mid(txt, i, 1)) Then
NumbersOnly = NumbersOnly & Mid(txt, i, 1)
End If
Next i
End Function
One application of this function is to strip out alphabetic characters from invoice numbers. The formula that follows returns 759426 because those are the only numeric characters in the text that was passed to it.
=NumbersOnly("INV759426")
Another use is to isolate numbers from a sentence that you get externally. For example, if you copied the sentence “Net income for the quarter was $15,267” from a web page and pasted it into cell A1, you could use the following formula to extract the numbers and use them in other calculations:
=NumbersOnly(A1)
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; here's an 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 of 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 percent 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 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. 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:
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 that extracts the first name from the name in cell A1:
=EXTRACTELEMENT(A1,1," ")
Debugging a function 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 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 you may want to use in your debugging:
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.
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). We've 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 indeed interested in this topic.