Emulating Excel s SUM Function

Emulating Excel's 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 (almost) 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 255 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:

 =SUM(B1,5,"6",,TRUE,SQRT(4),A1:A5,D:D,C2*C3) 

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

• A single cell reference

• A literal value

• A string that looks like a value

• A missing argument

• A logical TRUE value

• An expression that uses another function

• A simple range reference

• A range reference that includes an entire column

• An expression that calculates the product of two cells

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

CD-ROM  

A workbook containing the MySum function is available on the companion CD-ROM. The file is named  mysum function.xlsm .

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   Dim m, n   MySum = 0 ' Process each argument   For i = 0 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             If IsError(cell) Then               MySum = cell ' return the error               Exit Function             End If             If cell = True Or cell = False Then               MySum = MySum + 0             Else               If IsNumeric(cell) Or IsDate(cell) Then _                  MySum = MySum + cell               End If           Next cell         Case "Variant()"             n = args(i)             For m = LBound(n) To UBound(n)                MySum = MySum(MySum, n(m)) 'recursive call             Next m         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"           MySum = MySum + args(i)         Case Else           MySum = MySum + args(i)       End Select     End If   Next i End Function 

 

Figure 10-6 shows a workbook with various formulas that use SUM and MySum . As you can see, the functions return identical results.

Figure 10-6: 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:

• 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 as Excel's SUM function does.

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

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.