Microsoft Office Tutorials and References

In Depth Information

• 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.

• If an argument contains an error (for example, #DIV0!), the MYSUM function simply returns the error —

just like the Excel SUM function.

• The Excel 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 evalu-

ated as a number (VBA's IsNumeric function is used for this).

• Dealing with Boolean arguments is tricky. For MYSUM to emulate SUM exactly, it needs to test for a literal

TRUE in the argument list and compensate for the difference (that is, add 2 to –1 to get 1).

• 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 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 recalculated instantly. After I replaced the SUM functions with MYSUM

functions, it took about 8 seconds. MYSUM may be improved a bit, but it can never come close to SUM's

speed.

By the way, I hope you understand that the point of this example is not to create a new SUM function. Rather, it

demonstrates how to create custom worksheet functions that look and work like those built into Excel.