Is there a way to construct the WorksheetFuction call using any passed statistic name, rather than only allowing specific ones as in the following working example? Thanks in advance.
Code:
Function CalcStat(stat As String, col As Integer)
'
' Calculate statistics (from hidden Data sheet values)
'
On Error Resume Next
If col = 0 Then 'Entire Data sheet (all data sets)
firstcell = "$A$1"
lastrow = Worksheets("Data").UsedRange.Rows.Count
lastcol = Worksheets("Data").UsedRange.Columns.Count
lastcell = Worksheets("Data").UsedRange.Cells(lastrow, lastcol).Address
Else 'One column (one data set)
firstcell = Worksheets("Data").Cells(1, col).Address
lastrow = Worksheets("Data").Cells(Rows.Count, col).End(xlUp).row
lastcell = Worksheets("Data").UsedRange.Cells(lastrow, col).Address
End If
datarng = firstcell & ":" & lastcell
Select Case stat
Case "Min"
CalcStat = WorksheetFunction.Min(Worksheets("Data").Range(datarng))
Case "Max"
CalcStat = WorksheetFunction.Max(Worksheets("Data").Range(datarng))
Case "Average"
CalcStat = WorksheetFunction.Average(Worksheets("Data").Range(datarng))
Case "StDev"
CalcStat = WorksheetFunction.StDev(Worksheets("Data").Range(datarng))
End Select
CalcStat = Round(CalcStat, 1)
End Function