此VBA函数始终返回0的答案 [英] This VBA function always returns an answer of 0
问题描述
我正在用Excel编写一个函数,以将佣金率应用于取决于利润率的利润.利润和利润率都是该功能的输入.但是,每当我运行此函数时,尽管输入应返回其他结果,但它始终返回0.
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.
下面是我的代码示例:
'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
如果ProfitMargin低于20%,我期望输出为0,0.05 x ProfitMargin的输入利润值在20%和25%之间,0.1 x ProfitMargin的输入利润值在25%和30%之间,0.15 xProfitMargin的输入利润值在30%到40%之间,0.25 x ProfitMargin的输入利润值在40%和50%之间,0.33 x ProfitMargin的输入利润值在50%以上.但是,方程始终返回0.
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.
推荐答案
您不能一次执行多个<
或>
检查.您需要使用 And
:
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
说明
为什么如果0.25>ProfitMargin> = 0.2 = True那么
失败了吗?
因为它首先检查 0.25>ProfitMargin
的结果是 True
或 False
,因此下一个检查将是例如 True> = 0.2
或 False> = 0.2
.当 True
是 -1
和 False
是 0
时,这是 -1> = 0.2
或 0> = 0.2
,两者均为 False
.那么最后的检查是 False = True
,因此 If
语句是 False
.
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
.
替代代码,我推荐类似的
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
这篇关于此VBA函数始终返回0的答案的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!