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. The function is shown in Figure 40.1.

Figure 40.1. A simple custom worksheet function.

You can, of course, accomplish the same effect with the following worksheet formula, which uses a nested IF function:

=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 also can nest custom functions and combine them with other elements in your formulas.

Figure 40.2. Creating a worksheet formula that uses a 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 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.

You can call custom functions from a 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 like 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:

Functions don’t necessarily use arguments. 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.

The function that follows takes a single argument and uses Excel’s text-to-speech generator to “speak” the argument.

Function SayIt(txt)
  Application.Speech.Speak (txt)
End Function

For example, if you enter this formula, you will hear 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:

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 1000 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 percent.)

=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 1000 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. And, 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 dot. Finally, TopAvg is assigned the value of Sum divided by Num.

You can use all of Excel’s 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.