A function with an array argument

A Function procedure also can accept one or more arrays as arguments, process the array(s), and return a single value. The array can also consist of a range of cells.

The following function accepts an array as its argument and returns the sum of its elements:

Function SumArray(List) As Double Dim Item As Variant

SumArray = 0

For Each Item In List

If WorksheetFunction.IsNumber(Item) Then SumArray = SumArray + Item Next Item End Function

Excel's ISNUMBER function checks to see whether each element is a number before adding it to the total. Adding this simple error-checking statement eliminates the type-mismatch error that occurs when you try to perform arithmetic with something other than a number.

The following procedure demonstrates how to call this function from a Sub procedure. The MakeList procedure creates a 100-element array and assigns a random number to each element. Then the MsgBox function displays the sum of the values in the array by calling the SumArray function.

Sub MakeList()

Dim Nums(1 To 100) As Double Dim i as Integer For i = 1 To 100

MsgBox SumArray(Nums) End Sub

Notice that the SumArray function doesn't declare the data type of its argument (it's a variant). Because it's not declared as a specific numeric type, the function also works in your worksheet formulas in which the argument is a Range object. For example, the following formula returns the sum of the values in A1:C10:

=SumArray(A1:C10)

You might notice that, when used in a worksheet formula, the SumArray function works very much like Excel's SUM function. One difference, however, is that SumArray does not accept multiple arguments. Understand that this example is for educational purposes only. Using the SumArray function in a formula offers absolutely no advantages over the Excel SUM function.

CD This example, named array argument.xlsm, is available on the companion CD-ROM.

0 0