Microsoft Office Tutorials and References
In Depth Information
When a Select Case structure is evaluated, program control exits the Select Case struc-
ture when the first true Case is encountered.
A function for a more complex commission structure
If the commission structure is more complex, you may need to use additional arguments for your
COMMISSION function. Imagine that the aforementioned sales manager implements a new policy to help re-
duce turnover: The total commission paid increases by 1 percent for each year that a salesperson stays with the
company.
The following is a modified COMMISSION function (named COMMISSION2). This function now takes two
arguments: the monthly sales (sales) and the number of years employed (years).
Function COMMISSION2(Sales As Double, Years As Long) As Double
‘ Calculates sales commissions based on
‘ years in service
Const Tier1 As Double = 0.08
Const Tier2 As Double = 0.105
Const Tier3 As Double = 0.12
Const Tier4 As Double = 0.14
Select Case Sales
Case Is >= 40000
COMMISSION2 = Sales * Tier4
Case Is >= 20000
COMMISSION2 = Sales * Tier3
Case Is >= 10000
COMMISSION2 = Sales * Tier2
Case Is < 10000
COMMISSION2 = Sales * Tier1
End Select
COMMISSION2 = COMMISSION2 + (COMMISSION2 * Years / 100)
End Function
Figure 26-3 shows the COMMISSION2 function in use. The formula in cell D2 is
=COMMISSION2(B2,C2)
Search JabSto ::

Custom Search