Microsoft Office Tutorials and References

In Depth Information

The most serious limitation of the SIMPLESUM function is that it does not handle multicell ranges. This im-

proved version does:

Function SIMPLESUM(ParamArray arglist() As Variant) As Double

Dim arg as Variant

Dim cell as Range

For Each arg In arglist

If TypeName(arg) = “Range” Then

For Each cell In arg

SIMPLESUM = SIMPLESUM + cell.Value

Next cell

Else

SIMPLESUM = SIMPLESUM + arg

End If

Next arg

End Function

This function checks each entry in the Arglist array. If the entry is a range, then the code uses a For Each-Next

loop to sum the cells in the range.

Even this improved version is certainly no substitute for the Excel SUM function. Try it by using various types

of arguments, and you'll see that it fails unless each argument is a value or a range reference. Also, if an argu-

ment consists of an entire column, you'll find that the function is
very
slow because it evaluates every cell —

even the empty ones.

Emulating the Excel SUM function

This section presents a Function procedure called MYSUM. Unlike the SIMPLESUM function listed in the pre-

vious section, MYSUM emulates the Excel SUM function perfectly.

Before you look at the code for the MYSUM function, take a minute to think about the Excel SUM function.

This very versatile function can have any number of arguments (even missing arguments), and the arguments

can be numerical values, cells, ranges, text representations of numbers, logical values, and even embedded

functions. For example, consider the following formula:

=SUM(A1,5,”6”,,TRUE,SQRT(4),B1:B5,{1,3,5})

This formula — which is a valid formula — contains all the following types of arguments, listed here in the or-

der of their presentation:

• A single cell reference (A1)

• A literal value (5)

• A string that looks like a value (“6”)

• A missing argument

• A logical value (TRUE)

• An expression that uses another function (SQRT)