VBA -Commission Function

Enter your search terms Submit search form

 


 

 

To day I came across someone who post a question at Yahoo!, asking help to rectify the problem. The question goes like these:


I have code that calculates commission based on the input of sales and now i need to add a command at the end that will increase commission by 1% for each year in the sales person is in service. How do I do this?

Function Commission2(Sales, Years)

Const Tier1 = 0.08
Const Tier2 = 0.105
Const Tier3 = 0.12
Const Tier4 = 0.14
'Calculate sales commission
Select Case Sales

Case 0 To 9999.99: Commission = Sales * Tier1
Case 10000 To 19999.99: Commission = Sales * Tier2
Case 20000 To 39999.99: Commission = Sales * Tier3
Case Is >= 40000: Commission = Sales * Tier4
End Select

Commission2 = [(1+(.01*Years))Commission]


End Function
 

I found the mistakes and my answer is shown on the right.

I think you didn't declare your commission as what data type and you used [ ] as bracket , it should be ( ) for commission2. You also didn't put the multiplication sign * for commission2.

I have corrected your code and it runs well.

For example, I put sales figure in B2 and Years in C2, it works fine.

Sales=10000
Years=2

In cell D2, I put =commission2(B2,C2) and the value is 1071, is is correct?

The corrected code is as follows:
Function commission2(Sales, Years)
Dim commission As Single

'You can use it in a worksheet formula or call the function from other
'VBA procedures
Const Tier1 = 0.08
Const Tier2 = 0.105
Const Tier3 = 0.12
Const Tier4 = 0.14
'Calculate sales commission
Select Case Sales

Case 0 To 9999.99: commission = Sales * Tier1
Case 10000 To 19999.99: commission = Sales * Tier2
Case 20000 To 39999.99: commission = Sales * Tier3
Case Is >= 40000: commission = Sales * Tier4
End Select


'didn't work...highlighted Commission(above)"argument not optional"
commission2 = ((1 + (0.01 * Years)) * commission)


End Function

 

 

 

 

 

 [Back to VBToday]