Microsoft Office Tutorials and References

In Depth Information

**A Multifunctional Function**

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

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.