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