CODEWITHSUNDEEP
excelvba
Sterling Mateo
Sterling Mateo

Reputation: 29

VBA Function Return no Value

I need help with this function. This function is not returning value.

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

Upvotes: 2

Views: 247

Answers (3)

VBasic2008
VBasic2008

Reputation: 55073

Calculate Commission (UDF)

Option Explicit

Function CalcComm( _
    ByVal SalesDate As Date, _
    ByVal SalesAmount As Double) _
As Double
    
    Dim CommRate As Double
    
    Select Case SalesAmount
    Case Is < 10000
        CommRate = 0.03
    Case Is < 25000
        CommRate = 0.04
    Case Else
        CommRate = 0.06
    End Select
    
    Select Case Month(SalesDate)
    Case 1, 2, 12
        CommRate = CommRate + 0.015
    End Select
    
    CalcComm = SalesAmount * CommRate

End Function

Upvotes: 0

Raymond Wu
Raymond Wu

Reputation: 3387

You did not assign the function the value you want to return. e.g. CalcComm = Commission.

sales_A is not declared and I believe should be salesamount, saledate is a typo and should be salesdate. Please insert Option Explicit at the top of your module to enforce variable declaration (and indirectly helps you catch typo).

Your code logic could make use of Select Case statements, the code below will return:

  • 1.5% if in the month of Jan, Feb and Dec;
  • For other months
    • 3% for < 10000;
    • 4% for 10000-25000 and;
    • 6% for >= 25000 and above.
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

Upvotes: 5

Solver Max
Solver Max

Reputation: 391

In addition to not returning the value in the function name, both of the If functions contain errors:

  • sales_A isn't a value passed into the function
  • saledate should be salesdate

Upvotes: 0

Related Questions