excel - VBA 函数无返回值

Question

我需要有关此功能的帮助。该函数没有返回值。

Function CalcComm(salesdate As Date, salesamount As Double) As Double

If sales_A < 10000 Then
Commission = salesamount * 0.03
ElseIf salesamount >= 10000 And salesamount < 25000 Then
Commission = salesamount * 0.04
ElseIf salesamount >= 25000 Then
Commission = salesamount * 0.06
Else: Commission = salesamount * 0.06
End If

If month(saledate) = 1 Or month(saledate) = 2 Or month(saledate) = 12 Then
Commission = salesamount * 0.015

End If
End Function

Answer 1

您没有为函数分配您想要返回的值。例如CalcComm = Commission .

sales_A未声明，我认为应该是 salesamount , saledate是一个错字，应该是 salesdate 。请插入Option Explicit在模块顶部强制执行变量声明(并间接帮助您捕获拼写错误)。

您的代码逻辑可以利用Select Case语句，下面的代码将返回:

• 如果是一月、二月和十二月，则为 1.5%；
• 其他月份
  • 3% 为 < 10000 ;
  • 4% 为 10000-25000和；
  • 6% 为 >= 25000及以上。

Option Explicit

Function CalcComm(salesdate As Date, salesamount As Double) As Double
    Dim Commission As Double
    
    Select Case Month(salesdate)
        Case 1, 2, 12: Commission = salesamount * 0.015
        Case Else
            Select Case salesamount
                Case Is < 10000: Commission = salesamount * 0.03
                Case Is < 25000: Commission = salesamount * 0.04
                Case Else: Commission = salesamount * 0.06
            End Select
    End Select
    
    CalcComm = Commission
End Function