Microsoft Office Tutorials and References

In Depth Information

**Emulating Excel’s SUM function**

Figure 10-7 shows a workbook with various formulas that use SUM and
MySum
. As you can see,

the functions return identical results.

Figure 10-7:
Comparing SUM with
MySum
.

If you’re interested in learning how this function works, create a formula that uses the function.

Then, set a breakpoint in the code and step through the statements line by line. (See “Debugging

Functions,” later in this chapter.) Try this for several different argument types, and you’ll soon

have a good feel for how this function works. As you study the code for
MySum
, keep the

following points in mind:

h
Missing arguments (determined by the
IsMissing
function) are simply ignored.

h
The procedure uses VBA’s
TypeName
function to determine the type of argument

(
Range
,
Error
, and so on). Each argument type is handled differently.

h
For a range argument, the function loops through each cell in the range, determines the

type of data in the cell, and (if appropriate) adds its value to a running total.

h
The data type for the function is
Variant
because the function needs to return an error

if any of its arguments is an error value.

h
If an argument contains an error (for example,
#DIV/0!
), the
MySum
function simply

returns the error — just as Excel’s SUM function does.

h
Excel’s SUM function considers a text string to have a value of 0 unless it appears as a

literal argument (that is, as an actual value, not a variable). Therefore,
MySum
adds the

cell’s value only if it can be evaluated as a number. (VBA’s
IsNumeric
function is used

to determine whether a string can be evaluated as a number.)

h
For range arguments, the function uses the
Intersect
method to create a temporary

range that consists of the intersection of the range and the sheet’s used range. This

technique handles cases in which a range argument consists of a complete row or column,

which would take forever to evaluate.

You may be curious about the relative speeds of SUM and
MySum
.
MySum
, of course, is much

slower, but just how much slower depends on the speed of your system and the formulas

themselves. On my system, a worksheet with 5,000 SUM formulas recalculates instantly. After I

replace the SUM functions with
MySum
functions, it takes about eight seconds.
MySum
may be

improved a bit, but it can never come close to SUM’s speed.