Use case select statement to call common functions : WorksheetFunction « Excel « VBA / Excel / Access / Word






  1. VBA / Excel / Access / Word
  2. Excel
  3. WorksheetFunction

Use case select statement to call common 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








Related examples in the same category

1.Finding the maximum value in a range
2.Use the LARGE function to determine the kth-largest value in a range
3.Calculating a mortgage payment
4.Insert the values directly as the function arguments:
5.Using a lookup function
6.A function with a range argument
7.Test the effect of the Option Base statement: declare that arrays are 1-based
8.Use fully qualified properties:
9.use the Range property as an argument within another Range property
10.Use the Transpose function to turn the one column into one row