If / Else used within user defined VBA Function | Excel forum

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

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