Excel 2007 Power Programming with VBA (Mr. Spreadsheets Bookshelf)

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.

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.