## 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]