Microsoft Excel 2016 BIBLE (2016)

IN THIS CHAPTER

1. Getting an overview of VBA functions

2. Looking at function procedures

3. Focusing on function procedure arguments

4. Debugging custom functions

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

Note

This chapter assumes that you're familiar with entering and editing VBA code in the Visual Basic (VB) Editor.

See Chapter 39, “Introducing Visual Basic for Applications,” 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 nonnumeric, the function returns an empty string. The NumSign 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 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 40.2. You can also nest custom functions and combine them with other elements in your formulas.

Figure 40.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 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, NumSignis 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 nonnumeric. 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 nonnumeric. 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 Functionprocedure, 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 anEnd Function statement.

Note

Step 3 is 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. Putting a function procedure in the wrong type of code module is a common mistake.

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.

The preceding paragraph isn't quite true. Over the years I've discovered a few cases in which a VBA function used in a formula can have an effect. For example, it's possible to create a custom worksheet function that adds or deletes cell comments. But, for the most part, functions used in formulas must be passive.

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.

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:

1. =MyFunctions.xlsm!CountNames(A1:A1000)

2. If the workbook name contains a space, you need to add single quotes around the workbook name. For example

3. ='My Functions.xlsm'!CountNames(A1:A1000)

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

Chapter 45, “Creating Custom Excel Add-Ins,” 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 Subprocedure 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.

The examples in this chapter are available on this book's website at www.wiley.com/go/excel2016bible. 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 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.

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 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," ")

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 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 that you may want to use in your debugging:

· PlaceMsgBoxfunctions 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. Runtime 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 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 40.3, appears.

Figure 40.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 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). 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

Figure 40.4 Using the Function Arguments dialog box to insert a custom function.

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.

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 44, “VBA Examples,” for more examples of useful VBA functions. You may be able to use the examples directly or adapt them for your needs .