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