Microsoft Office Tutorials and References
In Depth Information
ant. 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 the 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 eas-
ily 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 use the most restrictive data types possible for your situation while providing for the most
user flexibility.
The workbook, statfunction function.xlsm, shown in Figure 26-2, is available on this
book's website.
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 ex-
pression 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.
Note that the Address property has an argument: external:=True. That argument con-
trols how the address is returned. The default value, FALSE, returns a simple range ad-
dress. When the external argument is TRUE, the address includes the workbook name
and worksheet name. This allows the function to use a range that's on a different work-
sheet.
Search JabSto ::




Custom Search