Advanced Function Techniques


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:

  • xlErrDiv0

  • xlErrNA

  • xlErrName

  • xlErrNull

  • xlErrNum

  • xlErrRef

  • xlErrValue

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

image from book
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 image from book 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()} 

image from book
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 image from book 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)} 

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

On the CD 

The workbook image from book 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:

  • A single cell reference (A1)

  • A literal value (5)

  • A string that looks like a value ("6")

  • A missing argument

  • A logical value (TRUE)

  • An expression that uses another function (SQRT)

  • A range reference (B1:B5)

  • An array ({1,3,5})

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 image from book 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:

  • Missing arguments (determined by the IsMissing function) are simply ignored.

  • The procedure uses the VBA TypeName function to determine the type of argument (Range, Error, or something else). Each argument type is handled differently.

  • For a range argument, the function loops through each cell in the range and adds its value to a running total.

  • The data type for the function is Variant because the function needs to return an error if any of its arguments is an error value.

  • If an argument contains an error (for example, #DIV0!), the MYSUM function simply returns the error-just like the Excel SUM function.

  • The Excel SUM function considers a text string to have a value of 0 unless it appears as a literal argument (that is, as an actual value, not a variable). Therefore, MYSUM adds the cell's value only if it can be evaluated as a number (VBA's IsNumeric function is used for this).

  • Dealing with Boolean arguments is tricky. For MYSUM to emulate SUM exactly, it needs to test for a literal TRUE in the argument list and compensate for the difference (that is, add 2 to –1 to get 1).

  • For range arguments, the function uses the Intersect method to create a temporary range that consists of the intersection of the range and the sheet's used range. This handles cases in which a range argument consists of a complete row or column, which would take forever to evaluate.

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.




Excel 2007 Formulas
Excel 2007 Formulas (Mr. Spreadsheets Bookshelf)
ISBN: 0470044020
EAN: 2147483647
Year: 2007
Pages: 212

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net