Emulating Excels SUM Function

In this section, I present a custom function called MySum. Unlike the SimpleSum function listed in the previous section, the MySum function emulates Excel's SUM function perfectly.

Before you look at the code for MySum, take a minute to think about the Excel SUM function. It is, in fact, very versatile. It can have as many as 30 arguments (even "missing" arguments), and the arguments can be numerical values, cells, ranges, text representations of numbers, logical values, and even embedded functions. For example, consider the following formula:

This formula, which is a perfectly valid formula, contains all the following types of arguments, listed here in the order of their presentation:

♦ A single cell reference

♦ A string that looks like a value

♦ A missing argument

♦ A logical TRUE value

♦ An expression that uses another function

♦ A range reference

The MySum function (see Listing 10-1) handles all these argument types.

A workbook containing the MySum function is available on the companion CD-ROM.

Listing 10-1: MySum Function

Function MySum(ParamArray args() As Variant) As Variant ' Emulates Excel's SUM function

' Variable declarations Dim i As Variant

Dim TempRange As Range, cell As Range Dim ECode As String MySum = 0

' Process each argument For i = LBound(args) To UBound(args) ' Skip missing arguments

If Not IsMissing(args(i)) Then ' What type of argument is it? Select Case TypeName(args(i)) Case "Range"

Create temp range to handle full row or column ranges Set TempRange = Intersect(args(i).Parent.UsedRange, args(i)) For Each cell In TempRange

Select Case TypeName(cell.Value) Case "Double"

MySum = MySum + cell.Value Case "String"

'MySum = MySum + Evaluate(cell.Value) Case "Error"

Select Case cell.Text Case "#DIV/0!"

MySum = CVErr(xlErrDiv0) Case "#N/A"

MySum = CVErr(xlErrNA) Case "#NAME?"

MySum = CVErr(xlErrName) Case "#NULL!"

MySum = CVErr(xlErrNull) Case "#NUM!"

MySum = CVErr(xlErrNum) Case "#REF!"

MySum = CVErr(xlErrRef) Case "#VALUE!"

MySum = CVErr(xlErrValue) End Select Exit Function Case "Date"

MySum = MySum + cell.Value

Case "Empty" Case "Boolean"

MySum = MySum + 0 End Select Next cell Case "Null" 'ignore it Case "Error" 'return the error MySum = args(i) Exit Function Case "Boolean"

Check for literal TRUE and compensate If args(i) = "True" Then MySum = MySum + 1 Case "Date"

End Select End If Next i End Function

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 VBA's TypeName function to determine the type of argument (Range, Error, and so on). Each argument type is handled differently.

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

♦ 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, #DIV/0!), the MySum function simply returns the error—just like Excel's SUM function.

♦ 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 for this.)

♦ 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 might 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 1,000 SUM formulas recalculated instantly. After I replaced the SUM functions with MySum functions, it took about eight 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.

0 0