This VBA function always returns an answer of 0

Question

I am writing a function in excel to apply a commission rate to a profit dependent on a profit margin. The profit and profit margin are both inputs to the function. However whenever I run this function, it always returns 0 despite having inputs that should return other results.

I have already looked over the code multiple times and checked that my inputs are valid but cannot find the problem. The profit margin is a fraction in the form of a percentage.

A sample of my code is shown below:

    'If Profit Margin is below 20% then no commission
'If Profit Margin is above 20% commission is 5%
'If Profit Margin is above 25% commission is 10%
'If Profit Margin is above 30% commission is 15%
'If Profit Margin is above 40% commission is 25%
'If Profit Margin is above 50% commission is 33%





Function CommissionPicker(ProfitMargin, Profit)


If 0.25 > ProfitMargin >= 0.2 = True Then
   CommissionPicker = 0.05 * Profit

   ElseIf 0.3 > ProfitMargin >= 0.25 = True Then
   CommissionPicker = 0.1 * Profit

   ElseIf 0.4 > ProfitMargin >= 0.3 = True Then
   CommissionPicker = 0.15 * Profit

   ElseIf 0.5 > ProfitMargin >= 0.4 = True Then
   CommissionPicker = 0.25 * Profit

   ElseIf ProfitMargin >= 0.5 = True Then
   CommissionPicker = 0.33 * Profit

   ElseIf ProfitMargin < 0.2 = True Then
   CommissionPicker = 0 * Profit

   Else
   End If


End Function

I expect the output to be 0 if ProfitMargin is below 20%, 0.05 x the input profit value for ProfitMargin between 20% and 25%, 0.1 x the input profit value for ProfitMargin between 25% and 30%, 0.15 x the input profit value for ProfitMargin between 30% and 40%, 0.25 x the input profit value for ProfitMargin between 40% and 50% and 0.33 x the input profit value for ProfitMargin above 50%. However the equation consistently returns a value of 0.

Answer 1

@PEH's answer is correct for the syntax of the If-statement. However, I would suggest that you reorganize your code, this would simplify the logic and make it easier to read and maintain:

Function CommissionPicker(ProfitMargin, Profit)

    If ProfitMargin < 0.2 Then
        CommissionPicker = 0 * Profit
    ElseIf ProfitMargin < 0.25 Then
        CommissionPicker = 0.05 * Profit
    ElseIf ProfitMargin < 0.3 Then
        CommissionPicker = 0.1 * Profit
    ElseIf ProfitMargin < 0.4 Then
        CommissionPicker = 0.15 * Profit
    ElseIf ProfitMargin < 0.5 Then
        CommissionPicker = 0.25 * Profit
    Else
        CommissionPicker = 0.33 * Profit
    End If

End Function

Answer 2

You cannot perform multiple < or > checks at once. You need to use And:

If ProfitMargin < 0.25 And ProfitMargin >= 0.2 Then

Additionally I recommend to specify a type for all your variables:

Function CommissionPicker(ByVal ProfitMargin As Double, ByVal Profit As Double) As Double

Explanation

Why does If 0.25 > ProfitMargin >= 0.2 = True Then fail?

Because it checks at first 0.25 > ProfitMargin which result is True or False so the next check would be eg True >= 0.2 or False >= 0.2. While True is -1 and False is 0 this is -1 >= 0.2 or 0 >= 0.2 which both is False. The last check then is False = True, so the If statement is False.

Alternativley to your code I recommend something like this

Function CommissionPicker(ByVal ProfitMargin As Double, ByVal Profit As Double) As Double
    Select Case ProfitMargin
        Case Is < 0.2
            CommissionPicker = 0 * Profit
        Case Is < 0.25
            CommissionPicker = 0.05 * Profit
        Case Is < 0.3
            CommissionPicker = 0.1 * Profit
        Case Is < 0.4
            CommissionPicker = 0.15 * Profit
        Case Is < 0.5
            CommissionPicker = 0.25 * Profit
        Case Else
            CommissionPicker = 0.33 * Profit
    End Select
End Function