A Multifunctional Function

This section demonstrates a technique that may be helpful in some situations-the technique of making a single worksheet function act like multiple functions. The following VBA function, named STATFUNCTION, takes two arguments-the range (rng) and the operation (op). Depending on the value of op, the function returns a value computed by using any

of the following worksheet functions: AVERAGE, COUNT, MAX, MEDIAN, MIN, MODE, STDEV, SUM, or VAR. For example, you can use this function in your worksheet:

 =STATFUNCTION(B1:B24,A24) 

The result of the formula depends on the contents of cell A24, which should be a string, such as Average, Count, Max, and so on. You can adapt this technique for other types of functions.

 Function STATFUNCTION(rng As Variant, op As String) As Variant     Select Case UCase(op)         Case "SUM"             STATFUNCTION = Application.Sum(rng)         Case "AVERAGE"             STATFUNCTION = Application.Average(rng)         Case "MEDIAN"             STATFUNCTION = Application.Median(rng)         Case "MODE"             STATFUNCTION = Application.Mode(rng)         Case "COUNT"             STATFUNCTION = Application.Count(rng)         Case "MAX"             STATFUNCTION = Application.Max(rng)         Case "MIN"             STATFUNCTION = Application.Min(rng)         Case "VAR"             STATFUNCTION = Application.Var(rng)         Case "STDEV"             STATFUNCTION = Application.StDev(rng)         Case Else             STATFUNCTION = CVErr(xlErrNA)     End Select End Function 

Figure 25-2 shows the STATFUNCTION function that is used in conjunction with a dropdown list generated by Excel's Data Data Tools Data Validation command. The formula in cell C14 is as follows:

 =STATFUNCTION(C1:C12,B14) 

Figure 25-2: Selecting an operation from the list displays the result in cell C14.

On the CD 

The workbook,  statfunction function.xlsm, shown in Figure 25-2, is available on the companion CD-ROM.

The following STATFUNCTION2 function is a much simpler approach that works exactly like the STATFUNCTION function. It uses the Evaluate method to evaluate an expression.

 Function STATFUNCTION2(rng As Range, op As String) As Double     STATFUNCTION2 = Evaluate(Op & "(" & _          rng.Address(external:=True) & ")") End Function 

For example, assume that the rng argument is C1:C12 and also that the op argument is the string SUM. The expression that is used as an argument for the Evaluate method is

 SUM(C1:C12) 

The Evaluate method evaluates its argument and returns the result. In addition to being much shorter, a benefit of this version of STATFUNCTION is that it's not necessary to list all the possible functions.

Worksheet Function Data Types

You may have noticed some differences in the data types used for functions and arguments so far. For instance, in STATFUNCTION, the variable rng was declared as a Variant, while the same variable was declared as a Range in STATFUNCTION2. Also, the former's return value was declared as a Variant, while the latter's is a Double data type.

Data types are two-edged swords. They can be used to limit the type of data that can be passed, or returned from, a function, but they can also reduce the flexibility of the function. Using Variant data types maximizes flexibility but slows execution speed.

One of the possible return values of STATFUNCTION is an error, in the Case Else section of the Select Case statement. That means that the function can return a Double data type or an Error. The most restrictive data type that can hold both an Error and a Double is a Variant (which can hold anything), so the function is typed as a Variant. On the other hand, STATFUNCTION2 does not have any provision for returning an error, so it's typed as the more restrictive Double data type. Numeric data in cells is treated as a Double even if it looks like an Integer.

The rng arguments are also typed differently. In STATFUNCTION2, the Address property of the Range object is used. Because of this, you must pass a Range to the function, or it will return an error. However, there is nothing in STATFUNCTION that forces rng to be a Range. By declaring rng as a Variant, the user has flexibility to provide inputs in other ways. Excel will happily try to convert whatever it's given into something it can use. If it can't convert it, the result will surely be an error. A user can enter the following formula:

 =STATFUNCTION({123.45,643,893.22},"Min") 

Neither argument is a cell reference, but Excel doesn't mind. It can find the minimum of an array constant as easily as a range of values. It works the other way too, as in the case of the second argument. If a cell reference is supplied, Excel will try to convert it to a String and will have no problem doing so.

In general, you should endeavor to use the most restrictive data types possible for your situation while providing for the most user flexibility.