Microsoft Office Tutorials and References
In Depth Information
Figure 26-1: The CELLTYPE function returns a string that describes the contents of a cell.
A Multifunctional Function
This section demonstrates a technique that may be helpful in some situations — the technique of making a
single worksheet function act like multiple functions. The following VBA custom function, named
STATFUNCTION, takes two arguments: the range (rng) and the operation (op). Depending on the value of op,
the function returns a value computed by 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 work-
sheet:
=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 As Variant, op As String) As Variant
Select Case UCase(op)
Case “SUM”
STATFUNCTION = Application.Sum(rng)
Case “AVERAGE”
Search JabSto ::




Custom Search