Part VI: Programming Excel with VBA

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

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.

826

Chapter 40: Creating Custom Worksheet Functions

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

Function names that are used in worksheet formulas must adhere to the same rules as variable names.

What a Function Can’t Do

Almost everyone who starts creating custom worksheet functions using VBA makes a fatal mistake: They try to get the function to do more than is possible.

A worksheet function returns a value, and the function must be completely “passive.” In other words, the function can’t change anything on the worksheet. For example, you can’t develop a worksheet function that changes the formatting of a cell. (Every VBA programmer has tried, and not one of them has been successful!) If your function attempts to perform an action that isn’t allowed, the function simply returns an error.

VBA functions that aren’t used in worksheet formulas can do anything that a regular Sub procedure can do — including changing cell formatting.

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.

827

Part VI: Programming Excel with VBA

Using custom functions in a worksheet formula

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:

•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 are 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 45 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, up-front 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. n

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.

828

Chapter 40: Creating Custom Worksheet Functions

•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 CD

The examples in this chapter are available on the companion CD-ROM. The file is named VBA functions.xlsm.

A function with no argument

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

Like 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. n

829

Part VI: Programming Excel with VBA

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:

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.

830

Chapter 40: Creating Custom Worksheet Functions

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)

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:

831

Part VI: Programming Excel with VBA

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)

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

832