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:B2 4,A2 4)

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 = WorksheetFunction.Sum(rng) Case "AVERAGE"

StatFunction = WorksheetFunction.Average(rng) Case "MEDIAN"

StatFunction = WorksheetFunction.Median(rng) Case "MODE"

StatFunction = WorksheetFunction.Mode(rng) Case "COUNT"

StatFunction = WorksheetFunction.Count(rng) Case "MAX"

StatFunction = WorksheetFunction.Max(rng) Case "MIN"

StatFunction = 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