Microsoft Office Tutorials and References

In Depth Information

**Function Examples**

Case 20000 To 39999.99: Commission2 = Sales * Tier3

Case Is >= 40000: Commission2 = Sales * Tier4

End Select

Commission2 = Commission2 + (Commission2 * Years / 100)

End Function

Pretty simple, eh? I just added the second argument (
Years
) to the
Function
statement and

included an additional computation that adjusts the commission.

Here’s an example of how you can write a formula using this function (it assumes that the sales

amount is in cell A1 and the number of years the salesperson has worked is in cell B1):

=Commission2(A1,B1)

All these commission-related procedures are available on the companion CD-ROM in a

file named
commission functions.xlsm
.

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.