Microsoft Office Tutorials and References

In Depth Information

**Emulating Excel’s SUM function**

The
SimpleSum
function is similar to Excel’s SUM function, but it’s not nearly as flexible. Try it

by using various types of arguments, and you’ll see that it fails if any of the cells contains a

nonvalue, or if you use a literal value for an argument.

Emulating Excel’s SUM function

In this section, I present a custom function called
MySum
. Unlike the
SimpleSum
function listed

in the previous section, the
MySum
function emulates Excel’s SUM function (almost) perfectly.

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

in fact, very versatile. It can have as many as 255 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(B1,5,”6”,,TRUE,SQRT(4),A1:A5,D:D,C2*C3)

This perfectly valid formula contains all the following types of arguments, listed here in the order

of their presentation:

h
A single cell reference

h
A literal value

h
A string that looks like a value

h
A missing argument

h
A logical
TRUE
value

h
An expression that uses another function

h
A simple range reference

h
A range reference that includes an entire column

h
An expression that calculates the product of two cells

The
MySum
function (see Listing 10-1) handles all these argument types.

A workbook containing the
MySum
function is available on the companion CD-ROM. The

file is named
mysum function.xlsm
.

Listing 10-1:
MySum Function

Function MySum(ParamArray args() As Variant) As Variant

‘ Emulates Excel’s SUM function

‘ Variable declarations