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
SIMPLESUM = SIMPLESUM + arg
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:
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)