Excel VBA Programming For Dummies, 4th Edition (2015)

Part V. Putting It All Together

Discover how to use VBA code to display numbers as words at www.dummies.com/extras/excelvbaprogramming.

In this part …

Find out why you might want to create custom worksheet functions.

Make your custom functions work just like Excel's built-in functions.

Discover Excel add-ins.

Create simple add-ins.

Chapter 20. Creating Worksheet Functions - and Living to Tell about It

In This Chapter

Knowing why custom worksheet functions are so useful

Understanding the basics of custom worksheet functions

Writing your own functions

Exploring functions that use various types of arguments

Examining function examples

Understanding the Insert Function dialog box

For many macro mavens, VBA’s main attraction is the capability to create custom worksheet functions — functions that look, work, and feel just like those that Microsoft built into Excel. A custom function offers the added advantage of working exactly how you want it to (because youwrote it). I introduce custom functions in Chapter 5. In this chapter, I get down to the nitty-gritty and describe some tricks of the trade.

Why Create Custom Functions?

You are undoubtedly familiar with Excel’s worksheet functions; even Excel novices know how to use common worksheet functions such as SUM, AVERAGE, and IF. By my count, Excel 2016 contains more than 450 predefined worksheet functions. And if that’s not enough, you can create functions by using VBA.

With all the functions available in Excel and VBA, you may wonder why you would ever need to create functions. The answer: to simplify your work. With a bit of planning, custom functions are very useful in worksheet formulas and VBA procedures. Often, for example, you can significantly shorten a formula by creating a custom function. After all, shorter formulas are more readable and easier to work with.

What custom worksheet functions can't do

As you develop custom functions for use in your worksheet formulas, it's important that you understand a key point. VBA worksheet Function procedures are essentially passive. For example, code within a Function procedure cannot manipulate ranges, change formatting, or do many of the other things that are possible with a Sub procedure. An example may help.

It might be useful to create a function that changes the color of text in a cell based on the cell's value. Try as you might, however, you can't write such a function. It always returns an error value.

Just remember this: A function used in a worksheet formula returns a value. It does not perform actions with objects.

That said, there are a few exceptions to this rule. For example, here's a Function procedure that changes the text in a cell comment:

Function ChangeComment(cell, NewText)
cell.Comment.Text NewText
End Function

And here's a formula that uses the function. It works only if cell A1 already has a comment. When the formula is calculated, the comment is changed.

=ChangeComment(A1,"I changed the comment!")

I'm not sure if this is an oversight or a feature. But it's a rare example of a VBA function that changes something in a worksheet.

Understanding VBA Function Basics

A VBA function is a procedure that’s stored in a VBA module. You can use these functions in other VBA procedures or in your worksheet formulas. Custom functions cannot be created with the macro recorder, although the macro recorder can help you identify relevant properties and methods.

A module can contain any number of functions. You can use a custom function in a formula just as though it were a built-in function. If the function is defined in a different workbook, however, you must precede the function name with the workbook name. Suppose that you develop a function called DiscountPrice (which takes one argument) and the function is stored in a workbook named pricing.xlsm.

To use this function in the pricing.xlsm workbook, enter a formula such as this:

=DiscountPrice(A1)

If you want to use this function in a different workbook, enter a formula such as this (and make sure the pricing.xlsm file is open):

=pricing.xlsm!discountprice(A1)

If the custom function is stored in an add-in, you don’t need to precede the function name with the workbook name. I discuss add-ins in Chapter 21.

Custom functions appear in the Insert Function dialog box, in the User Defined category. Pressing Shift+F3 is one way to display the Insert Function dialog box.

Writing Functions

Remember that a function’s name acts like a variable. The final value of this variable is the value returned by the function. To demonstrate, examine the following function, which returns the user’s first name:

Function FirstName()
Dim FullName As String
Dim FirstSpace As Long
FullName = Application.UserName
FirstSpace = InStr(FullName, " ")
If FirstSpace = 0 Then
FirstName = FullName
Else
FirstName = Left(FullName, FirstSpace - 1)
End If
End Function

This function starts by assigning the UserName property of the Application object to a variable named FullName. Next, it uses the VBA InStr function to locate the position of the first space in the name. If there is no space, FirstSpace is equal to 0, and FirstName is equal to the entire name. If FullName does have a space, the Left function extracts the text to the left of the space and assigns it to FirstName.

Notice that FirstName is the name of the function and is also used as a variable name in the function. The final value of FirstName is the value that’s returned by the function. Several intermediate calculations may be going on in the function, but the function always returns the last value assigned to the variable that is the same as the function’s name.

All the examples in this chapter are available at this book’s website.

Working with Function Arguments

To work with functions, you need to understand how to work with arguments. An argument is not a disagreement between variables. Rather, it's information that is passed to the function and then used by the function to do its thing.

The following points apply to the arguments for Excel worksheet functions and custom VBA functions:

· Arguments can be cell references, variables (including arrays), constants, literal values, or expressions.

· Some functions have no arguments.

· Some functions have a fixed number of required arguments.

· Some functions have a combination of required and optional arguments.

The examples in this section demonstrate how to work with various types of arguments.

A function with no argument

Some functions don't use any arguments. For example, Excel has a few built-in worksheet functions that don’t use arguments. These include RAND, TODAY, and NOW.

Here’s an example of a custom function with no arguments. The following function returns the UserName property of the Application object. This name appears in the General tab of the Excel Options dialog box. This simple but useful example shows the only way you can get the user’s name to appear in a worksheet cell:

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 current user’s name:

=User()

As with the Excel built-in functions, you must include a set of empty parentheses when using a function with no arguments. Otherwise, Excel tries to interpret the function as a named range.

A function with one argument

The single-argument function in this section is designed for sales managers who need to calculate the commissions earned by their salespeople. The commission rate depends on the monthly sales volume; those who sell more earn a higher commission rate. The function returns the commission amount based on the monthly sales (which is the function’s only argument — a required argument). The calculations in this example are based on Table 20-1.

Table 20-1 Commission Rates by Sales

You can use several approaches to calculate commissions for sales amounts entered into a worksheet. You could write a lengthy worksheet formula such as this:

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

A couple of reasons make this a bad approach. First, the formula is overly complex. Second, the values are hard-coded into the formula, making the formula difficult to modify if the commission structure changes.

A better approach is to create a table of commission values and use a LOOKUP table function to compute the commissions:

=VLOOKUP(A1,Table,2)*A1

Another approach, which doesn’t require a table of commissions, is to create a custom function:

Function Commission(Sales)
' Calculates sales commissions
Const Tier1 As Double = 0.08
Const Tier2 As Double = 0.105
Const Tier3 As Double = 0.12
Const Tier4 As Double = 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
Commission = Round(Commission, 2)
End Function

Notice that the four commission rates are declared as constants rather than hard-coded in the statements. This makes it very easy to modify the function if the commission rates change.

After you define this 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 percent:

=Commission(25000)

Figure 20-1 shows a worksheet that uses the Commission function in formulas in column C.

Figure 20-1: Using the Commission function in a worksheet.

A function with two arguments

The next example builds on the preceding one. Imagine that the sales manager implements a new policy to reward long-term employees: The total commission paid increases by 1 percent for every year the salesperson has been with the company.

I modified the custom Commission function (defined in the preceding section) 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
Const Tier1 As Double = 0.08
Const Tier2 As Double = 0.105
Const Tier3 As Double = 0.12
Const Tier4 As Double = 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)
Commission2 = Round(Commission2, 2)
End Function

I simply added the second argument (Years) to the Function statement and included an additional computation that adjusts the commission before exiting the function. This additional computation multiplies the original commission by the number of years in services, divides by 100, and then adds the result to the original computation.

Here’s an example of how you can write a formula by using this function. (It assumes that the sales amount is in cell A1; cell B1 specifies the number of years the salesperson has worked.)

=Commission2(A1,B1)

Figure 20-2 shows a worksheet that uses the Commission2 function.

Figure 20-2: Using the Commission2 function, which takes two arguments.

A function with a range argument

Using a worksheet range as an argument is not at all tricky; Excel takes care of the behind-the-scenes details.

Here's a simple but useful function that concatenates the contents of a range. It takes two arguments: InRange (the worksheet range to be concatenated), and Delim (one or more delimiter characters, to be inserted between cells).

Function JoinText(InRange, Delim)
Dim Cell As Range
For Each Cell In InRange
JoinText = JoinText & Cell.Value & Delim
Next Cell
JoinText = Left(JoinText, Len(JoinText) - Len(Delim))
End Function

It uses a For Each-Next construct to loop through each cell in the range. It concatenates the cell contents, followed by the Delim character(s). The last statement removes the final delimiter, which is not needed because there are no more items.

Figure 20-3 shows an example. The second argument is a two-character string (a comma followed by a space).

Figure 20-3: Using the JoinText function to concatenate cells.

Here's another example of a function that uses a range argument. 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, so you would probably write a 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 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 decide that you need to compute the average of the top six values? You would need to rewrite the formula — and make sure that you update all copies of the formula.

Wouldn’t this be easier if Excel had a function named TopAvg? Then you could compute the average by using the following (nonexistent) function:

=TopAvg(Data,5)

This example shows a case in which a custom function can make things much easier for you. The following custom VBA function, named TopAvg, returns the average of the N largest values in a range:

Function TopAvg(InRange, N)
' Returns the average of the highest N values in InRange
Dim Sum As Double
Dim i As Long
Sum = 0
For i = 1 To N
Sum = Sum + WorksheetFunction.Large(InRange, i)
Next i
TopAvg = Sum / N
End Function

This function takes two arguments: InRange (which is a worksheet range) and N (the number of values to average). It starts by initializing the Sum variable to 0. It then uses a For-Next loop to calculate the sum of the N largest values in the range. Note that I use the Excel LARGE function within the loop. Finally, TopAvg is assigned the value of the Sum divided by N.

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 the Excel RAND function in a VBA procedure.

A function with an optional argument

Many Excel built-in worksheet functions use optional arguments. An example is the LEFT function, which returns characters from the left side of a string. Its official syntax follows:

LEFT(text[,num_chars])

The first argument is required, but the second (in square brackets) is optional. If you omit the optional argument, Excel assumes a value of 1. Therefore, the following formulas return the same result:

=LEFT(A1,1)
=LEFT(A1)

The custom functions you develop in VBA also can have optional arguments. You specify an optional argument by preceding the argument’s name with the keyword Optional. The argument's name is then followed by an equal sign and the default value. If the optional argument is missing, the code uses the default value.

One caveat: If you use optional arguments, they must always be last in the argument list.

The following example shows a custom function that uses an optional argument:

Function DrawOne(InRange, Optional Recalc = 0)
' Chooses one cell at random from a range

Randomize
' Make function volatile if Recalc is 1
If Recalc = 1 Then Application.Volatile True

' Determine a random cell
DrawOne = InRange(Int((InRange.Count) * Rnd + 1))
End Function

This function randomly chooses one cell from an input range. The range passed as an argument is actually an array (I explain arrays in Chapter 7), and the function selects one item from the array at random. If the second argument is 1, the selected value changes whenever the worksheet is recalculated. (The function is made volatile.) If the second argument is 0 (or is omitted), the function is not recalculated unless one of the cells in the input range is modified.

I use the Randomize statement to ensure that a different random number "seed" is chosen each time the workbook is opened. Without this statement, the same random numbers are generated each time the workbook is opened.

You can use this function for choosing lottery numbers, selecting a winner from a list of names, and so on.

Debugging custom functions

Debugging a Function procedure can be a bit more challenging than debugging a Sub procedure. If you develop a function for use in worksheet formulas, you find that 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.

You can choose among three methods for debugging custom functions:

· Place MsgBox functions at strategic locations to monitor the value of specific variables. Fortunately, message boxes in Function procedures pop up when you execute the procedure. Make sure that only one formula in the worksheet uses your function. Otherwise, the message boxes appear for each formula that's evaluated — which could get very annoying.

· Test the procedure by calling it from a Sub procedure. Runtime errors normally appear in a pop-up window, and you can either correct the problem (if you know it) or jump right into the debugger.

· Set a breakpoint in the function and then use the Excel debugger to step through the function. You can then access all the usual debugging tools. Refer to Chapter 13 to find out about the debugger.

Introducing Wrapper Functions

This section contains some relatively simple custom worksheet functions that are also very useful. I call these function wrapper functions because they consist of code that's wrapped around intrinsic VBA elements. In other words, they allow you to use VBA functions in worksheet formulas.

Earlier in this chapter, I present a wrapper function:

Function User()
' Returns the name of the current user
User = Application.UserName
End Function

This function, in essence, lets your formulas access the UserName property of the Application object.

The remainder of this section contains some additional wrapper functions.

The NumberFormat function

This function simply displays the number format for a cell. It can be useful if you need to ensure that a group of cells all have the same number format.

Function NumberFormat(Cell)
' Returns the cell's number format
NumberFormat = Cell(1).NumberFormat
End Function

Notice the use of Cell (1)? If a multicell range is used as an argument, only the first cell is used.

You can easily write similar functions that return a cell's text color, background color, font, and so on.

The ExtractElement function

This is easily my favorite wrapper function. It returns a substring from a text string that contains multiple elements, separated by a separator character. For example, this formula returns cow, which is the third element in a string that uses a space as a separator. The arguments, of course, could be cell references.

=ExtractElement("dog horse cow cat", 3, " ")

Here's the code, which is a wrapper for VBA's Split function:

Function ExtractElement(Txt, n, Sep)
' Returns the nth element of a text string, where the
' elements are separated by a specified separator character
ExtractElement = Split(Application.Trim(Txt), Sep)(n - 1)
End Function

Figure 20-4 shows the ExtractElement function used in worksheet formulas. Column A contains the text string, Column B contains the element number to be extracted, and Column C contains the delimiter (cells that appear to be blank contain a space character).

Figure 20-4: Using the ExtractElement function to return an element from a string.

The SayIt function

This simple function is a wrapper for the Speak method of the Application.Speech object. It uses a synthesized voice to "speak" the argument.

Function SayIt(txt)
' Speaks the argument
Application.Speech.Speak txt, True
End Function

Here's an example:

=IF(C10>10000,SayIt("Over budget"),"OK")

The formula checks cell C10. If the value is greater than 10,000, the function speaks the text: "Over budget." If the value is less than 10,000, the function displays the text OK (and doesn't say anything).

Use sparingly. If you use this function more than one time, it can be very confusing. Also, remember that this function is evaluated each time the worksheet is calculated, so the voice may get very annoying if you're making many changes. This function is probably more suited for amusement purposes.

The IsLike function

VBA's Like operator is a very flexible way to compare text strings. Check it out in the VBA Help system. This function brings that power to your worksheet formulas:

Function IsLike(text, pattern)
' Returns true if the first argument is like the second
IsLike = text Like pattern
End Function

Working with Functions That Return an Array

Array formulas are one of Excel’s most powerful features. If you’re familiar with array formulas, you'll be happy to know that you can create VBA functions that return an array.

Returning an array of month names

I'll start out with a simple example. The MonthNames function returns a 12-element array of — you guessed it — month names.

Function MonthNames()
MonthNames = Array("January", "February", "March", _
"April", "May", "June", "July", "August", _
"September", "October", "November", "December")
End Function

To use the MonthNames function in a worksheet, you must enter it as a 12-cell array formula. For example, select range A2:L2 and enter =MonthNames(). Then press Ctrl+Shift+Enter to enter the array formula in all 12 selected cells. Figure 20-5 shows the result.

Figure 20-5: Using the MonthNames function to return a 12-element array.

If you want the month names to display in a column, select 12 cells in a column and use this array formula. (Don't forget to enter it by pressing Ctrl+Shift+Enter.)

=TRANSPOSE(MonthNames())

You can also pick out a single month from the array. Here's a formula (not an array formula) that displays the fourth element of the array: April.

=INDEX(MonthNames(),4)

Returning a sorted list

Suppose that you have a list of names you want to show in sorted order in another range of cells. Wouldn’t it be nice to have a worksheet function do that for you?

The custom function in this section does just that: It takes a single-column range of cells as its argument and then returns an array of those cells sorted. Figure 20-6 shows how it works. Range A2:A13 contains some names. Range C2:C13 contains this multicell array formula. (Remember that you must enter the formula by pressing Ctrl+Shift+Enter.)

=Sorted(A2:A13)

Figure 20-6: Using a custom function to return a sorted range.

Here's the code for the Sorted function:

Function Sorted(Rng As Range)
Dim SortedData() As Variant
Dim Cell As Range
Dim Temp As Variant, i As Long, j As Long
Dim NonEmpty As Long

' Transfer data to SortedData
For Each Cell In Rng
If Not IsEmpty(Cell) Then
NonEmpty = NonEmpty + 1
ReDim Preserve SortedData(1 To NonEmpty)
SortedData(NonEmpty) = Cell.Value
End If
Next Cell

' Sort the array
For i = 1 To NonEmpty
For j = i + 1 To NonEmpty
If SortedData(i) > SortedData(j) Then
Temp = SortedData(j)
SortedData(j) = SortedData(i)
SortedData(i) = Temp
End If
Next j
Next i

' Transpose the array and return it
Sorted = Application.Transpose(SortedData)
End Function

The Sorted function starts by creating an array named SortedData. This array contains all the nonblank values in the argument range. Next, the SortedData array is sorted, using a bubble-sort algorithm. Because the array is a horizontal array, it must be transposed before it is returned by the function.

The Sorted Function works with a range of any size, as long as it’s in a single column or row. If the unsorted data is in a row, your formula needs to use Excel’s TRANSPOSE function to display the sorted data horizontally. For example:

=TRANSPOSE(Sorted(A16:L16))

Using the Insert Function Dialog Box

The Excel Insert Function dialog box is a handy tool that lets you choose a worksheet function from a list and prompts you for the function’s arguments. As I note earlier in this chapter, your custom worksheet functions also appear in the Insert Function dialog box. Custom functions appear in the User Defined category.

Function procedures defined with the Private keyword do not appear in the Insert Function dialog box. Therefore, if you write a Function procedure that’s designed to be used only by other VBA procedures (but not in formulas), you should declare the function as Private.

Displaying the function’s description

The Insert Function dialog box displays a description of each built-in function. But as you can see in Figure 20-7, a custom function displays the following text as its description: No help available.

Figure 20-7: By default, the Insert Function dialog box does not provide a description for custom functions.

To display a meaningful description of your custom function in the Insert Function dialog box, perform a few additional (nonintuitive) steps:

1. Activate a worksheet in the workbook that contains the custom function.

2. Choose Developer  ⇒  Code  ⇒  Macros (or press Alt+F8).

The Macro dialog box appears.

3. In the Macro Name field, type the function’s name.

Note that the function does not appear in the list of macros; you must type the name.

4. Click the Options button.

The Macro Options dialog box appears.

5. In the Description field, type a description of the function.

6. Click OK.

7. Click Cancel.

Now the Insert Function dialog box displays the description of your function (see Figure 20-8).

Figure 20-8: The custom function now displays a description.

Custom functions, by default, are listed in the User Defined category. To add a function to a different category, you need to use VBA. This statement, when executed, adds the TopAvg function to the Math & Trig category (which is category 3):

Application.MacroOptions Macro:="TopAvg", Category:=3

Check the Help system for other category numbers.

You need to execute this statement only one time. After you execute it (and save the workbook), the category number is permanently assigned to the function.

Adding argument descriptions

When you access a built-in function from the Insert Function dialog box, the Function Arguments dialog box displays descriptions of the arguments (see Figure 20-9).

Figure 20-9: By default, the Function Arguments dialog box displays Function argument descriptions for built-in functions only.

In the past, it was not possible to add argument descriptions. But beginning with Excel 2010, Microsoft finally implemented this feature. You provide argument descriptions by using the MacroOptions method. Here's an example that adds descriptions for the arguments used by the TopAvg function:

Sub AddArgumentDescriptions()
Application.MacroOptions Macro:="TopAvg", _
ArgumentDescriptions:= _
Array("Range that contains the values", _
"Number of values to average")
End Sub

You need to execute this procedure only one time. After you execute it, the argument descriptions are stored in the workbook and are associated with the function.

Notice that the argument descriptions appear as arguments for the Array function. You must use the Array function even if you're assigning a description for a function that has only one argument.

This chapter provides lots of information about creating custom worksheet functions. Use these examples as models when you create functions for your own work. As usual, the online help provides additional details. Turn to Chapter 21 if you want to find out how to make your custom functions more accessible by storing them in an add-in.