A multifunctional function

This example describes a technique that may be helpful in some situations: making a single worksheet function act like multiple functions. For example, the following VBA listing is for a custom function called StatFunction. It takes two arguments: the range (rng) and the operation (op). Depending on the value of op, the function returns a value computed 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 as follows:

=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, op) Select Case UCase(op) Case "SUM"

STATFUNCTION Case "AVERAGE"

STATFUNCTION Case "MEDIAN"

STATFUNCTION Case "MODE"

STATFUNCTION Case "COUNT"

STATFUNCTION Case "MAX"

STATFUNCTION Case "MIN"

STATFUNCTION

= WorksheetFunction.Sum(rng) = WorksheetFunction.Average(rng) = WorksheetFunction.Median(rng) = WorksheetFunction.Mode(rng) = WorksheetFunction.Count(rng) = WorksheetFunction.Max(rng) = WorksheetFunction.Min(rng)

Case "VAR"

STATFUNCTION = WorksheetFunction.Var(rng) Case "STDEV"

STATFUNCTION = WorksheetFunction.StDev(rng) Case Else

STATFUNCTION = CVErr(xlErrNA) End Select End Function

0 0

Post a comment