Microsoft Office Tutorials and References
In Depth Information
Case 20000 To 39999.99: Commission2 = Sales * Tier3
Case Is >= 40000: Commission2 = Sales * Tier4
Commission2 = Commission2 + (Commission2 * Years / 100)
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):
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
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