Function Examples | Excel 2007 Power Programming with VBA (Mr. Spreadsheets Bookshelf)

In this section, I present a series of examples that demonstrate how to use arguments effectively with functions. By the way, this discussion also applies to Sub procedures.

Functions with no argument

Like Sub procedures, Function procedures need not have arguments. Excel, for example, has a few built-in functions that don't use arguments, including RAND, TODAY, and NOW. You can create similar functions.

This section contains examples of functions that don't use an argument.

CD-ROM

A workbook that contains these functions is available on the companion CD-ROM. The file is named no argument.xlsm .

Here's a simple example of a function that doesn't use an argument. The following function returns the UserName property of the Application object. This name appears in the Options dialog box (General tab) and is stored in the Windows Registry.

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

When you enter the following formula, the cell returns the name of the current user ( assuming that it's listed properly in the Registry):

 =User()

Note	When you use a function with no arguments in a worksheet formula, you must include a set of empty parentheses. This requirement is not necessary if you call the function in a VBA procedure, although including the empty parentheses does make it clear that you're calling a function.

To use this function in another procedure, you can assign it to a variable, use it in an expression, or use it as an argument for another function.

The following example calls the User function and uses the return value as an argument for the MsgBox statement. The concatenation operator ( & ) joins the literal string with the result of the User function.

 Sub ShowUser()     MsgBox "Your name is " & User() End Sub

This example demonstrates how you can create a wrapper function that simply returns a property or the result of a VBA function. Following are three additional wrapper functions that take no argument.

 Function ExcelDir() As String '   Returns the directory in which Excel is installed     ExcelDir = Application.Path     End Function Function SheetCount() '   Returns the number of sheets in the workbook     SheetCount = Application.Caller.Parent.Parent.Sheets.Count     End Function     Function SheetName() '   Returns the name of the worksheet     SheetName = Application.Caller.Parent.Name End Function

Controlling Function Recalculation

When you use a custom function in a worksheet formula, when is it recalculated?

Custom functions behave like Excel's built-in worksheet functions. Normally, a custom function is recalculated only when it needs to be - which is only when any of the function's arguments are modified. You can, however, force functions to recalculate more frequently. Adding the following statement to a Function procedure makes the function recalculate whenever the sheet is recalculated. If you're using automatic calculation mode, a calculation occurs whenever any cell is changed.

 Application.Volatile True

The Volatile method of the Application object has one argument (either True or False ). Marking a Function procedure as volatile forces the function to be calculated whenever recalculation occurs for any cell in the worksheet.

For example, the custom StaticRand function can be changed to emulate Excel's RAND function using the Volatile method, as follows :

 Function NonStaticRand() '   Returns a random number that '   changes with each calculation     Application.Volatile True     NonStaticRand = Rnd() End Function

Using the False argument of the Volatile method causes the function to be recalculated only when one or more of its arguments change as a result of a recalculation. (If a function has no arguments, this method has no effect.)

To force an entire recalculation, including nonvolatile custom functions, press Ctrl+Alt+F9. This key combination will, for example, generate new random numbers for the StaticRand function presented in this chapter.

Here's another example of a function that doesn't take an argument. I used to use Excel's RAND function to quickly fill a range of cells with values. But I didn't like the fact that the random numbers change whenever the worksheet is recalculated. So I remedied this by converting the formulas to values.

Then I realized that I could create a custom function that returned random numbers that didn't change. I used the VBA built-in Rnd function, which returns a random number between 0 and 1. The custom function is as follows:

 Function StaticRand() '   Returns a random number that doesn't '   change when recalculated     StaticRand = Rnd() End Function

If you want to generate a series of random integers between 0 and 1,000, you can use a formula such as this:

 =INT(StaticRand()*1000)

The values produced by this formula never change when the worksheet is calculated normally. However, you can force the formula to recalculate by pressing Ctrl+Alt+F9

A function with one argument

This section describes a function for sales managers who need to calculate the commissions earned by their sales forces. The calculations in this example are based on the following table:

Open table as spreadsheet

Monthly Sales	Commission Rate
0 “$9,999	8.0%
$10,000 “$19,999	10.5%
$20,000 “$39,999	12.0%
$40,000+	14.0%

Note that the commission rate is nonlinear and also depends on the month's total sales. Employees who sell more earn a higher commission rate.

There are several ways to calculate commissions for various sales amounts entered into a worksheet. If you're not thinking too clearly, you might waste lots of time and come up with a lengthy 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))))

This is a bad approach for a couple of reasons. First, the formula is overly complex, making it difficult to understand. Second, the values are hard-coded into the formula, making the formula difficult to modify.

A better (non-VBA) approach is to use a lookup table function to compute the commissions. For example, the following formula uses VLOOKUP to retrieve the commission value from a range named Table and multiplies that value by the value in cell A1.

 =VLOOKUP(A1,Table,2)*A1

Yet another approach (which eliminates the need to use a lookup table) is to create a custom function such as the following:

 Function Commission(Sales)     Const Tier1 = 0.08     Const Tier2 = 0.105     Const Tier3 = 0.12     Const Tier4 = 0.14 '   Calculates sales commissions     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 enter this function in a VBA module, you can use it in a worksheet formula or call the function from other VBA procedures.

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)

Even if you don't need custom functions in a worksheet, creating Function procedures can make your VBA coding much simpler. For example, if your VBA procedure calculates sales commissions, you can use the exact same function and call it from a VBA procedure.

Here's a tiny procedure that asks the user for a sales amount and then uses the Commission function to calculate the commission due:

 Sub CalcComm()     Dim Sales as Long     Sales = InputBox("Enter Sales:")     MsgBox "The commission is " & Commission(Sales) End Sub

The CalcComm procedure starts by displaying an input box that asks for the sales amount. Then it displays a message box with the calculated sales commission for that amount.

This Sub procedure works, but it is rather crude. Following is an enhanced version that displays formatted values and keeps looping until the user clicks No (see Figure 10-4).

Figure 10-4: Using a function to display the result of a calculation.

 Sub CalcComm()     Dim Sales As Long     Dim Msg As String, Ans As String '   Prompt for sales amount     Sales = Val(InputBox("Enter Sales:", _     "Sales Commission Calculator")) '   Build the Message     Msg = "Sales Amount:" & vbTab & Format(Sales, "$#,##0.00")     Msg = Msg & vbCrLf & "Commission:" & vbTab     Msg = Msg & Format(Commission(Sales), "$#,##0.00")     Msg = Msg & vbCrLf & vbCrLf & "Another?" '   Display the result and prompt for another     Ans = MsgBox(Msg, vbYesNo, "Sales Commission Calculator")     If Ans = vbYes Then CalcComm End Sub

This function uses two VBA built-in constants: vbTab represents a tab (to space the output), and vbCrLf specifies a carriage return and line feed (to skip to the next line). VBA's Format function displays a value in a specified format (in this case, with a dollar sign, comma, and two decimal places).

Use Arguments, Not Cell References

All ranges that are used in a custom function should be passed as arguments. Consider the following function, which returns the value in A1, multiplied by 2:

 Function DoubleCell()     DoubleCell = Range("A1") * 2 End Function

Although this function works, there are times when it may return an incorrect result. Excel's calculation engine cannot account for ranges in your code that are not passed as arguments. Therefore, in some cases, all precedents may not be calculated before the function's value is returned. The DoubleCell function should be written as follows, with A1 passed as the argument:

 Function DoubleCell(cell)     DoubleCell = cell * 2 End Function

In both of these examples, the Commission function must be available in the active workbook; otherwise , Excel displays an error message saying that the function is not defined.

A function with two arguments

Imagine that the aforementioned hypothetical sales managers implement a new policy to help reduce turnover : The total commission paid is increased by 1 percent for every year that the salesperson has been with the company.

I modified the custom Commission function (defined in the preceding section) so that it takes two arguments. The new argument represents the number of years . Call this new function Commission2 :

 Function Commission2(Sales, Years) '   Calculates sales commissions based on '   years in service     Const Tier1 = 0.08     Const Tier2 = 0.105     Const Tier3 = 0.12     Const 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

Pretty simple, eh? I just added the second argument ( Years ) to the Function statement and included an additional computation that adjusts the commission.

Here's an example of how you can write a formula using this function (it assumes that the sales amount is in cell A1 and the number of years the salesperson has worked is in cell B1):

 =Commission2(A1,B1)

CD-ROM

All of these commission- related procedures are available on the companion CD-ROM in a file named commission functions.xlsm .

A function with an array argument

A Function procedure also can accept one or more arrays as arguments, process the array(s), and return a single value. The array can also consist of a range of cells.

The following function accepts an array as its argument and returns the sum of its elements:

 Function SumArray(List) As Double     Dim Item As Variant     SumArray = 0     For Each Item In List         If WorksheetFunction.IsNumber(Item) Then _            SumArray = SumArray + Item     Next Item End Function

Excel's ISNUMBER function checks to see whether each element is a number before adding it to the total. Adding this simple error-checking statement eliminates the type-mismatch error that occurs when you try to perform arithmetic with something other than a number.

The following procedure demonstrates how to call this function from a Sub procedure. The MakeList procedure creates a 100-element array and assigns a random number to each element. Then the MsgBox function displays the sum of the values in the array by calling the SumArray function.

 Sub MakeList()     Dim Nums(1 To 100) As Double     Dim i as Integer     For i = 1 To 100         Nums(i) = Rnd * 1000     Next i     MsgBox SumArray(Nums) End Sub

Notice that the SumArray function doesn't declare the data type of its argument (it's a variant). Because it's not declared as a specific numeric type, the function also works in your worksheet formulas in which the argument is a Range object. For example, the following formula returns the sum of the values in A1:C10:

 =SumArray(A1:C10)

You might notice that, when used in a worksheet formula, the SumArray function works very much like Excel's SUM function. One difference, however, is that SumArray does not accept multiple arguments. Understand that this example is for educational purposes only. Using the SumArray function in a formula offers absolutely no advantages over the Excel SUM function.

CD	This example, named array argument.xlsm , is available on the companion CD-ROM.

A function with optional arguments

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

 LEFT(text,num_chars)

The first argument is required, but the second is optional. If the optional argument is omitted, Excel assumes a value of 1. Therefore, the following two formulas return the same result:

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

The custom functions that you develop in VBA also can have optional arguments. You specify an optional argument by preceding the argument's name with the keyword Optional . In the argument list, optional arguments must appear after any required arguments.

Following is a simple function example that returns the user's name. The function's argument is optional.

 Function User(Optional UpperCase As Variant)     If IsMissing(UpperCase) Then UpperCase = False     User = Application.UserName     If UpperCase Then User = UCase(User) End Function

If the argument is False or omitted, the user's name is returned without any changes. If the argument is True , the user's name is converted to uppercase (using the VBA UCase function) before it is returned. Notice that the first statement in the procedure uses the VBA IsMissing function to determine whether the argument was supplied. If the argument is missing, the statement sets the UpperCase variable to False (the default value).

All the following formulas are valid, and the first two produce the same result:

 =User() =User(False) =User(True)

Note	If you need to determine whether an optional argument was passed to a function, you must declare the optional argument as a `Variant` data type. Then you can use the `IsMissing` function within the procedure, as demonstrated in this example.

The following is another example of a custom function that uses an optional argument. This function randomly chooses one cell from an input range and returns that cell's contents. If the second argument is True , the selected value changes whenever the worksheet is recalculated (that is, the function is made volatile). If the second argument is False (or omitted), the function is not recalculated unless one of the cells in the input range is modified.

 Function DrawOne(Rng As Variant, Optional Recalc As Variant = False) '   Chooses one cell at random from a range '   Make function volatile if Recalc is True     Application.Volatile Recalc '   Determine a random cell     DrawOne = Rng(Int((Rng.Count) * Rnd + 1)) End Function

Notice that the second argument for DrawOne includes the Optional keyword, along with a default value.

All the following formulas are valid, and the first two have the same effect:

 =DrawOne(A1:A100) =DrawOne(A1:A100,False) =DrawOne(A1:A100,True)

This function might be useful for choosing lottery numbers, picking a winner from a list of names , and so on.

CD-ROM

This function is available on the companion CD-ROM. The filename is draw.xlsm .

A function that returns a VBA array

VBA includes a useful function called Array . The Array function returns a variant that contains an array (that is, multiple values). If you're familiar with array formulas in Excel, you have a head start on understanding VBA's Array function. You enter an array formula into a cell by pressing Ctrl+Shift+Enter. Excel inserts curly braces around the formula to indicate that it's an array formula.

CROSS-REFERENCE

See Chapter 3 for more details on array formulas.

Note	It's important to understand that the array returned by the `Array` function is not the same as a normal array that's made up of elements of the `Variant` data type. In other words, a variant array is not the same as an array of variants.

The MonthNames function, which follows, is a simple example that uses VBA's Array function in a custom function:

 Function MonthNames()     MonthNames = Array("Jan", "Feb", "Mar", "Apr","May", "Jun", _         "Jul", "Aug", "Sep", "Oct", "Nov", "Dec") End Function

The MonthNames function returns a horizontal array of month names. You can create a multicell array formula that uses the MonthNames function. Here's how to use it: Make sure that the function code is present in a VBA module. Then in a worksheet, select multiple cells in a row (start by selecting 12 cells). Then enter the formula that follows (without the braces) and press Ctrl+Shift+Enter:

 {=MonthNames()}

What if you'd like to generate a vertical list of month names? No problem; just select a vertical range, enter the following formula (without the braces), and then press Ctrl+Shift+Enter:

 {=TRANSPOSE(MonthNames())}

This formula uses the Excel TRANSPOSE function to convert the horizontal array to a vertical array.

The following example is a variation on the MonthNames function:

 Function MonthNames(Optional MIndex)     Dim AllNames As Variant     Dim MonthVal As Long     AllNames = Array("Jan", "Feb", "Mar", "Apr", _        "May", "Jun", "Jul", "Aug", "Sep", "Oct", _        "Nov", "Dec")     If IsMissing(MIndex) Then         MonthNames = AllNames     Else         Select Case MIndex             Case Is >= 1 '             Determine month value (for example, 13=1)               MonthVal = ((MIndex - 1) Mod 12)               MonthNames = AllNames(MonthVal)             Case Is <= 0 ' Vertical array               MonthNames = Application.Transpose(AllNames)         End Select     End If End Function

Notice that I use the VBA IsMissing function to test for a missing argument. In this situation, it is not possible to specify the default value for the missing argument in the argument list of the function because the default value is defined within the function. You can use the IsMissing function only if the optional argument is a variant.

This enhanced function uses an optional argument that works as follows:

If the argument is missing, the function returns a horizontal array of month names.
If the argument is less than or equal to 0, the function returns a vertical array of month names. It uses Excel's TRANSPOSE function to convert the array.
If the argument is greater than or equal to 1, it returns the month name that corresponds to the argument value.

Note

This procedure uses the Mod operator to determine the month value. The Mod operator returns the remainder after dividing the first operand by the second. Keep in mind that the AllNames array is zero-based and that indices range from 0 to 11. In the statement that uses the Mod operator, 1 is subtracted from the function's argument. Therefore, an argument of 13 returns (corresponding to Jan), and an argument of 24 returns 11 (corresponding to Dec).

You can use this function in a number of ways, as illustrated in Figure 10-5.

Figure 10-5: Different ways of passing an array or a single value to a worksheet.

Range A1:L1 contains the following formula entered as an array. Start by selecting A1:L1, enter the formula (without the braces), and then press Ctrl+Shift+Enter.

 {=MonthNames()}

Range A3:A14 contains integers from 1 to 12. Cell B3 contains the following (nonarray) formula, which was copied to the 11 cells below it:

 =MonthNames(A3)

Range D3:D14 contains the following formula entered as an array:

 {=MonthNames(-1)}

Range F3 contains this (nonarray) formula:

 =MonthNames(3)

Remember: To enter an array formula, you must press Ctrl+Shift+Enter.

Note	The lower bound of an array, created using the `Array` function, is determined by the lower bound specified with the `Option Base` statement at the top of the module. If there is no `Option Base` statement, the default lower bound is .

CD-ROM

A workbook that demonstrates the MonthNames function is available on the companion CD-ROM. The file is named month names.xslm .

A function that returns an error value

In some cases, you might want your custom function to return a particular error value. Consider the RemoveVowels function, which I presented earlier in this chapter:

 Function RemoveVowels(Txt) As String '   Removes all vowels from the Txt argument     Dim i As Long     RemoveVowels = ""     For i = 1 To Len(Txt)         If Not UCase(Mid(Txt, i, 1)) Like "[AEIOU]" Then             RemoveVowels = RemoveVowels & Mid(Txt, i, 1)         End If     Next i End Function

When used in a worksheet formula, this function removes the vowels from its single-cell argument. If the argument is a numeric value, this function returns the value as a string. You may prefer that the function returns an error value (#N/A), rather than the numeric value converted to a string.

You might be tempted simply to assign a string that looks like an Excel formula error value. For example:

 RemoveVowels = "#N/A"

Although the string looks like an error value, it is not treated as such by other formulas that may reference it. To return a real error value from a function, use the VBA CVErr function, which converts an error number to a real error.

Fortunately, VBA has built-in constants for the errors that you want to return from a custom function. These errors are Excel formula error values and not VBA runtime error values. These constants are as follows:

xlErrDiv0 (for #DIV/0!)
xlErrNA (for #N/A)
xlErrName (for #NAME?)
xlErrNull (for #NULL!)
xlErrNum (for #NUM!)
xlErrRef (for #REF!)
xlErrValue (for #VALUE!)

To return a #N/A error from a custom function, you can use a statement like this:

 RemoveVowels = CVErr(xlErrNA)

The revised RemoveVowels function follows. This function uses an If-Then construct to take a different action if the argument is not text. It uses Excel's ISTEXT function to determine whether the argument is text. If the argument is text, the function proceeds normally. If the cell doesn't contain text (or is empty), the function returns the #N/A error.

 Function RemoveVowels(Txt) As Variant ' Removes all vowels from the Txt argument ' Returns #VALUE if Txt is not a string     Dim i As Long     RemoveVowels = ""     If Application.WorksheetFunction.IsText(Txt) Then        For i = 1 To Len(Txt)            If Not UCase(Mid(Txt, i, 1)) Like "[AEIOU]" Then                RemoveVowels = RemoveVowels & Mid(Txt, i, 1)            End If        Next i     Else         RemoveVowels = CVErr(xlErrNA)     End If End Function

Note	Notice that I also changed the data type for the function's return value. Because the function can now return something other than a string, I changed the data type to `Variant` .

A function with an indefinite number of arguments

Some Excel worksheet functions take an indefinite number of arguments. A familiar example is the SUM function, which has the following syntax:

 SUM(number1,number2...)

The first argument is required, but you can have as many as 254 additional arguments in Excel 2007. Here's an example of a SUM function with four range arguments:

 =SUM(A1:A5,C1:C5,E1:E5,G1:G5)

You can even mix and match the argument types. For example, the following example uses three arguments: the first is a range, the second is a value, and the third is an expression.

 =SUM(A1:A5,12,24*3)

You can create Function procedures that have an indefinite number of arguments. The trick is to use an array as the last (or only) argument, preceded by the keyword ParamArray .

Note	`ParamArray` can apply only to the last argument in the procedure's argument list. It is always a `Variant` data type, and it is always an optional argument (although you don't use the `Optional` keyword).

Following is a function that can have any number of single-value arguments. (It doesn't work with multicell range arguments.) It simply returns the sum of the arguments.

 Function SimpleSum(ParamArray arglist() As Variant) As Double     For Each arg In arglist         SimpleSum = SimpleSum + arg Next arg     Next arg End Function

To modify this function so it works with multicell range arguments, you need to add another loop, which processes each cell in each of the arguments:

 Function SimpleSum(ParamArray arglist() As Variant) As Double     Dim cell As Range     For Each arg In arglist         For Each cell In arg         SimpleSum = SimpleSum + cell Next cell     Next arg End Function

The SimpleSum function is similar to Excel's SUM function, but it's not nearly as flexible. Try it out by using various types of arguments, and you'll see that it fails if any of the cells contains a non-value.