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
 
Search JabSto ::




Custom Search