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)

Search JabSto ::

Custom Search