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.