Microsoft Office Tutorials and References
In Depth Information
Some Useful Worksheet Functions
The SPELLDOLLARS function is too lengthy to list here, but you can view the complete
listing in worksheet function.xlsm on the companion CD-ROM.
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 = 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
 
Search JabSto ::




Custom Search