Microsoft Office Tutorials and References

In Depth Information

Figure 26-10:
Comparing Excel's SUM function with a custom function.

MYSUM is a close emulation of the SUM function, but it's not perfect. It cannot handle operations on arrays.

For example, this array formula returns the sum of the squared values in range A1:A4:

{=SUM(A:A4^2)}

This formula returns a #VALUE! error:

{=MYSUM(A1:A4^2)}

As you study the code for MYSUM, keep the following points in mind:

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

• The procedure uses the VBA TypeName function to determine the type of argument (Range, Error, or

something else). Each argument type is handled differently.

• For a range argument, the function loops through each cell in the range and adds its value to a running total.