Martin has attended:
Excel Advanced course
Excel Advanced - Formulas & Functions course
Excel Advanced - For Power Users course
Excel VBA Intro Intermediate course
Excel VBA Advanced course
If / Else used within user defined VBA Function
Hi Support,
I have some problems with the following code in VBA. I am trying to create a function that will allow for the calculation of sales bonus's. Example code below:-
Function SalesBonus(BonusAmount As Currency, SalesTarget As Currency, AchievedSales As Currency) As Currency
' This function calculates the Bonus payable to Sales staff at various % achieved against sales targets
If AchievedSales / SalesTarget < 0.9499 Then SalesBonus = 0
ElseIf AchievedSales / SalesTarget >= 0.95 Then SalesBonus = BonusAmount * 0.5
ElseIf AchievedSales / SalesTarget >= 0.96 Then SalesBonus = BonusAmount * 0.6
ElseIf AchievedSales / SalesTarget >= 0.97 Then SalesBonus = BonusAmount * 0.7
ElseIf AchievedSales / SalesTarget >= 0.98 Then SalesBonus = BonusAmount * 0.8
ElseIf AchievedSales / SalesTarget >= 1 Then SalesBonus = BonusAmount * 1
Else: SalesBonus = BonusAmount * 2
End If
End Function
RE: If / Else used within user defined VBA Function
Hi Martin
Thanks for getting in touch.
There's a couple of issues with the function. The way the the IF has been laid out means that each line is treated individually. In other words, the next line does not conditionally depend on the next. You need to break the IF statement after the word THEN each time to make the logic flow.
Secondly I'm not sure when the BonusAmount * 2 will be triggered. I can only think that will happen in the result of an error, as the first line deals with everything less than 0.9499 (will include negative numbers) and the last line deals with everything greater than 1 (up to 'infinity').
It's easier to follow the logic of a nested answer like this with a SELECT CASE statement. Here's a suggestion:
Function SalesBonus2(BonusAmount As Currency, SalesTarget As Currency, AchievedSales As Currency) As Currency
' This function calculates the Bonus payable to Sales staff at various % achieved against sales targets
Dim SalesRatio As Single
SalesRatio = AchievedSales / SalesTarget
Select Case SalesRatio
Case Is >= 1
SalesBonus2 = BonusAmount * 1
Case Is >= 0.98
SalesBonus2 = BonusAmount * 0.8
Case Is >= 0.97
SalesBonus2 = BonusAmount * 0.7
Case Is >= 0.96
SalesBonus2 = BonusAmount * 0.6
Case Is >= 0.95
SalesBonus2 = BonusAmount * 0.5
Case 0 To 0.9499
SalesBonus2 = BonusAmount * 0
Case Else
SalesBonus2 = BonusAmount * 2
End Select
End Function
Give this a try and let me know how you get on.
Kind regards
Gary Fenn
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
RE: If / Else used within user defined VBA Function
Message from Delegate Martin...
Hi Gary,
Thank you for your help that worked a treat. Using the case is was so much cleaning than using elseIf.
Out of interest, is there a way to add ‘auto quick info’ to your own UDF?
Thanks
Martin
RE: If / Else used within user defined VBA Function
Hi Martin
I'm glad you like it. It does make things easier to read and decode. Our forum has truncated the indenting, if you indent (tab) everything inside the SELECT CASE and further indent each resulting action, re-reading it in 6 months time will be straightforward.
By 'auto quick info' do you mean the tooltip that pops up when you use say, VLOOKUP? Unfortunately it's absurdly complicated, if you search around for "udf custom tooltip" you will find some extremely indepth guides that have you hacking Windows DLLs. Not recommended and probably not worth the effort.
Kind regards
Gary Fenn
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
