Creating 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 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.

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 ➪ 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.

Analyzing the custom function

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

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.xlsm, 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. Here's an 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 containing 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.

Creating a function with no arguments

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.

Creating a function with one argument

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)

Creating 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:

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)

Creating 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 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)

Creating a function with a range argument

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.

Creating 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 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 that extracts the first name from the name in cell A1:

=EXTRACTELEMENT(A1,1," ")