Microsoft Office Tutorials and References

In Depth Information

**A Multifunctional Function**

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 custom

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 drop-down list

generated by Excel’s Data

Data Tools

Data Validation command. The formula in cell C14 is as

➜

➜

follows:

=STATFUNCTION(C1:C12,B14)