Excel 2007 Formulas (Mr. Spreadsheets Bookshelf)

In this section, I explore some even more advanced functions. The examples in this section demonstrate some special techniques that you can use with your custom functions.

Returning an Error Value

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

Function REVERSETEXT(text As String) As String ' Returns its argument, reversed REVERSETEXT = StrReverse(text) End Function

This function reverses the contents of its single-cell argument (which can be text or a value). If the argument is a multicell range, the function returns #VALUE!

Assume that you want this function to work only with strings. If the argument does not contain a string, you want the function to return an error value (#N/A). You may be tempted to simply assign a string that looks like an Excel formula error value. For example:

REVERSETEXT = "#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 constants are listed here:

The following is the revised REVERSETEXT function:

Function REVERSETEXT(text As Variant) As Variant ' Returns its argument, reversed If WorksheetFunction.ISNONTEXT(text) Then REVERSETEXT = CVErr(xlErrNA) Else REVERSETEXT = StrReverse(text) End If End Function

First, change the argument from a String data type to a Variant. If the argument's data type is String, Excel will try to convert whatever it gets (for example, number, Boolean value) to a String and will usually succeed. Next, the Excel ISNONTEXT function is used to determine whether the argument is not a text string. If the argument is not a text string, the function returns the #N/A error. Otherwise, it returns the characters in reverse order.

Note 

The data type for the return value of the original REVERSETEXT function was String because the function always returned a text string. In this revised version, the function is declared as a variant because it can now return something other than a string.

Returning an Array from a Function

Most functions that you develop with VBA return a single value. It's possible, however, to write a function that returns multiple values in an array.

Cross Ref 

Part III deals with arrays and array formulas. Specifically, these chapters provide examples of a single formula that returns multiple values in separate cells. As you'll see, you can also create custom functions that return arrays.

VBA includes a useful function called Array. The Array function returns a variant that contains an array. It's important to understand that the array returned is not the same as a normal array composed of elements of the variant type. In other words, a variant array is not the same as an array of variants.

If you're familiar with using array formulas in Excel, you have a head start understanding the VBA Array function. You enter an array formula into a cell by pressing Ctrl+Shift+Enter. Excel inserts brackets around the formula to indicate that it's an array formula. See Chapter 15 for more details on array formulas.

Note 

The lower bound of an array created by using the Array function is, by default, 0. However, the lower bound can be changed if you use an Option Base statement.

The following MONTHNAMES function demonstrates how to return an array from a Function procedure:

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

Figure 25-6 shows a worksheet that uses the MONTHNAMES function. You enter the function by selecting A4:L4 and then entering the following formula:

{=MONTHNAMES()}

Figure 25-6: The MONTHNAMES function entered as an array formula.

Note 

As with any array formula, you must press Ctrl+Shift+Enter to enter the formula. Don't enter the brackets-Excel inserts the brackets for you.

The MONTHNAMES function, as written, returns a horizontal array in a single row. To display the array in a vertical range in a single column (as in A7:A18 in Figure 25-5), select the range and enter the following formula:

{=TRANSPOSE(MONTHNAMES())}

Alternatively, you can modify the function to do the transposition. The following function uses the Excel TRANSPOSE function to return a vertical array:

Function VMONTHNAMES() As Variant VMONTHNAMES = Application.Transpose(Array( _ "Jan", "Feb", "Mar", "Apr", _ "May", "Jun", "Jul", "Aug", _ "Sep", "Oct", "Nov", "Dec")) End Function

On the CD 

The workbook  monthnames.xlsm that demonstrates MONTHNAMES and VMONTHNAMES is available on the companion CD-ROM.

Returning an Array of Nonduplicated Random Integers

The RANDOMINTEGERS function returns an array of nonduplicated integers. This function is intended for use in a multicell array formula. Figure 25-7 shows a worksheet that uses the following formula in the range A3:D12:

{=RANDOMINTEGERS()}

Figure 25-7: An array formula generates nonduplicated consecutive integers, arranged randomly.

This formula was entered into the entire range by using Ctrl+Shift+Enter. The formula returns an array of nonduplicated integers, arranged randomly. Because 40 cells contain the formula, the integers range from 1 to 40. The following is the code for RANDOMINTEGERS:

Function RANDOMINTEGERS() Dim FuncRange As Range Dim V() As Integer, ValArray() As Integer Dim CellCount As Double Dim i As Integer, j As Integer Dim r As Integer, c As Integer Dim Temp1 As Variant, Temp2 As Variant Dim RCount As Integer, CCount As Integer Randomize ' Create Range object Set FuncRange = Application.Caller ' Return an error if FuncRange is too large CellCount = FuncRange.Count If CellCount > 1000 Then RANDOMINTEGERS = CVErr(xlErrNA) Exit Function End If ' Assign variables RCount = FuncRange.Rows.Count CCount = FuncRange.Columns.Count ReDim V(1 To RCount, 1 To CCount) ReDim ValArray(1 To 2, 1 To CellCount) ' Fill array with random numbers ' and consecutive integers For i = 1 To CellCount ValArray(1, i) = Rnd ValArray(2, i) = i Next i ' Sort ValArray by the random number dimension For i = 1 To CellCount For j = i + 1 To CellCount If ValArray(1, i) > ValArray(1, j) Then Temp1 = ValArray(1, j) Temp2 = ValArray(2, j) ValArray(1, j) = ValArray(1, i) ValArray(2, j) = ValArray(2, i) ValArray(1, i) = Temp1 ValArray(2, i) = Temp2 End If Next j Next i ' Put the randomized values into the V array i = 0 For r = 1 To RCount For c = 1 To CCount i = i + 1 V(r, c) = ValArray(2, i) Next c Next r RANDOMINTEGERS = V End Function

On the CD 

The workbook  random integers function.xlsm containing the RANDOMINTEGERS function is available on the companion CD-ROM.

Randomizing a Range

The following RANGERANDOMIZE function accepts a range argument and returns an array that consists of the input range in random order:

Function RANGERANDOMIZE(rng) Dim V() As Variant, ValArray() As Variant Dim CellCount As Double Dim i As Integer, j As Integer Dim r As Integer, c As Integer Dim Temp1 As Variant, Temp2 As Variant Dim RCount As Integer, CCount As Integer Randomize ' Return an error if rng is too large CellCount = rng.Count If CellCount > 1000 Then RANGERANDOMIZE = CVErr(xlErrNA) Exit Function End If ' Assign variables RCount = rng.Rows.Count CCount = rng.Columns.Count ReDim V(1 To RCount, 1 To CCount) ReDim ValArray(1 To 2, 1 To CellCount) ' Fill ValArray with random numbers ' and values from rng For i = 1 To CellCount ValArray(1, i) = Rnd ValArray(2, i) = rng(i) Next i ' Sort ValArray by the random number dimension For i = 1 To CellCount For j = i + 1 To CellCount If ValArray(1, i) > ValArray(1, j) Then Temp1 = ValArray(1, j) Temp2 = ValArray(2, j) ValArray(1, j) = ValArray(1, i) ValArray(2, j) = ValArray(2, i) ValArray(1, i) = Temp1 ValArray(2, i) = Temp2 End If Next j Next i ' Put the randomized values into the V array i = 0 For r = 1 To RCount For c = 1 To CCount i = i + 1 V(r, c) = ValArray(2, i) Next c Next r RANGERANDOMIZE = V End Function

The code closely resembles the code for the RANDOMINTEGERS function. Figure 25-8 shows the function in use. The following array formula, which is in C2:C11, returns the contents of A2:A11 in a random order:

{=RANGERANDOMIZE(A2:A11)}

Figure 25-8: The RANGERANDOMIZE function returns the contents of a range, but in a randomized order.

On the CD 

The workbook  range randomize function.xlsm, which contains the RANGERANDOMIZE function, is available on the companion CD-ROM.

Using Optional Arguments

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

LEFT(text,num_chars)

The first argument is required, but the second is optional. If you omit the optional argument, Excel assumes a value of 1.

Custom functions that you develop in VBA can also have optional arguments. You specify an optional argument by preceding the argument's name with the keyword Optional. The following is a simple function that returns the user's name:

Function USER() USER = Application.UserName End Function

Suppose that in some cases, you want the user's name to be returned in uppercase letters. The following function uses an optional argument:

Function USER(Optional UpperCase As Variant) As String If IsMissing(UpperCase) Then UpperCase = False If UpperCase = True Then USER = Ucase(Application.UserName) Else USER = Application.UserName End If End Function

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.

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

Optional arguments also allow you to specify a default value in the declaration, rather than testing it with the IsMissing function. The preceding function can be rewritten in this alternate syntax as

Function USER(Optional UpperCase As Boolean = False) As String If UpperCase = True Then USER = UCase(Application.UserName) Else USER = Application.UserName End If End Function

If no argument is supplied, UpperCase is automatically assigned a value of False. This has the advantage of allowing you type the argument appropriately instead of with the generic Variant data type. If you use this method, however, there is no way to tell whether the user omitted the argument or supplied the default argument.

All the following formulas are valid in either syntax (and the first two have the same effect):

=USER() =USER(False) =USER(True)

Using an Indefinite Number of Arguments

Some of the 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. Here's an example of a formula that uses the SUM function with four range arguments:

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

You can mix and match the argument types. For example, the following example uses three arguments-a range, followed by a value, and finally 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. It is always a variant data type, and it is always an optional argument (although you don't use the Optional keyword).

A SIMPLE EXAMPLE OF INDEFINITE ARGUMENTS

The following is a Function procedure that can have any number of single-value arguments. It simply returns the sum of the arguments.

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

The following formula returns the sum of the single-cell arguments:

=SIMPLESUM(A1,A5,12)

The most serious limitation of the SIMPLESUM function is that it does not handle multicell ranges. This improved version does:

Function SIMPLESUM(ParamArray arglist() As Variant) As Double Dim arg as Variant Dim cell as Range For Each arg In arglist If TypeName(arg) = "Range" Then For Each cell In arg SIMPLESUM = SIMPLESUM + cell.Value Next cell Else SIMPLESUM = SIMPLESUM + arg End If Next arg End Function

This function checks each entry in the Arglist array. If the entry is a range, then the code uses a For Each-Next loop to sum the cells in the range.

Even this improved version is certainly no substitute for the Excel SUM function. Try it out by using various types of arguments, and you'll see that it fails unless each argument is a value or a range reference. Also, if an argument consists of an entire column, you'll find that the function is very slow because it evaluates every cell-even the empty ones.

EMULATING THE EXCEL SUM FUNCTION

This section presents a Function procedure called MYSUM. Unlike the SIMPLESUM function listed in the previous section, MYSUM emulates the Excel SUM function perfectly.

Before you look at the code for the MYSUM function, take a minute to think about the Excel SUM function. This very versatile function can have any number of arguments (even missing arguments), and the arguments can be numerical values, cells, ranges, text representations of numbers, logical values, and even embedded functions. For example, consider the following formula:

=SUM(A1,5,"6",,TRUE,SQRT(4),B1:B5,{1,3,5})

This formula-which is a valid formula-contains all the following types of arguments, listed here in the order of their presentation:

The following is the listing for the MYSUM function that handles all these argument types:

Function MySum(ParamArray args() As Variant) As Variant ' Emulates Excel's ' Variable declarations Dim i As Variant Dim TempRange As Range, cell As Range Dim ECode As String Dim m, n MySum = 0 ' Process each argument For i = 0 To UBound(args) ' Skip missing arguments If Not IsMissing(args(i)) Then ' What type of argument is it? Select Case TypeName(args(i)) Case "Range" ' Create temp range to handle full row or column ranges Set TempRange = Intersect(args(i).Parent.UsedRange, args(i)) For Each cell In TempRange If IsError(cell) Then MySum = cell ' return the error Exit Function End If If cell = True Or cell = False Then MySum = MySum + 0 Else If IsNumeric(cell) Or IsDate(cell) Then _ MySum = MySum + cell End If Next cell Case "Variant()" n = args(i) For m = LBound(n) To UBound(n) MySum = MySum(MySum, n(m)) 'recursive call Next m Case "Null" 'ignore it Case "Error" 'return the error MySum = args(i) Exit Function Case "Boolean" ' Check for literal TRUE and compensate If args(i) = "True" Then MySum = MySum + 1 Case "Date" MySum = MySum + args(i) Case Else MySum = MySum + args(i) End Select End If Next i End Function

On the CD 

The workbook  mysum function.xlsm containing the MYSUM function is available on the companion CD-ROM.

As you study the code for MYSUM, keep the following points in mind:

You may be curious about the relative speeds of SUM and MYSUM. MYSUM, of course, is much slower, but just how much slower depends on the speed of your system and the formulas themselves. On my system, a worksheet with 1,000 SUM formulas recalculated instantly. After I replaced the SUM functions with MYSUM functions, it took about 12 seconds. MYSUM may be improved a bit, but it can never come close to SUM's speed.

By the way, I hope you understand that the point of this example is not to create a new SUM function. Rather, it demonstrates how to create custom worksheet functions that look and work like those built into Excel.

Related

Категории