VBA Loop and UDF

[STEVEMILLS04]

Good morning/afternoon!

I am trying to create a button that will run a calculation for me based on a custom function. It keeps returning 0, which is my Case Else statement.

I debugged to make sure my data is in the appropriate columns. Hiredate is column J and Potential is column T.

Here is my button click code:

Private Sub CommandButton1_Click()
lastRow = 32 'Cells(Rows.Count, 21).End(xlUp).Row




For nRow = 15 To lastRow
    Dim sDate As Date
    sDate = Cells(nRow, 10)
    
    Dim pBonus As Currency
    pBonus = Cells(nRow, 20)
    
    Cells(nRow, 21) = prbonus(sDate, pBonus)
    
Next nRow


End Sub

and here is my UDF with a case statement:

Function prbonus(HireDate As Date, Potential As Currency) As Currency
Dim result As Currency
Select Case HireDate
    Case Month(HireDate) = 1 And Year(HireDate) = 2015
        result = Potential * (11 / 12)
    Case Month(HireDate) = 2 And Year(HireDate) = 2015
        result = Potential * (10 / 12)
    Case Month(HireDate) = 3 And Year(HireDate) = 2015
        result = Potential * (9 / 12)
    Case Month(HireDate) = 4 And Year(HireDate) = 2015
        result = Potential * (8 / 12)
    Case Month(HireDate) = 5 And Year(HireDate) = 2015
        result = Potential * (7 / 12)
    Case Month(HireDate) = 6 And Year(HireDate) = 2015
        result = Potential * (6 / 12)
    Case Month(HireDate) = 7 And Year(HireDate) = 2015
        result = Potential * (5 / 12)
    Case Month(HireDate) = 8 And Year(HireDate) = 2015
        result = Potential * (4 / 12)
    Case Month(HireDate) = 9 And Year(HireDate) = 2015
        result = Potential * (3 / 12)
    Case Month(HireDate) = 10 And Year(HireDate) = 2015
        result = Potential * (2 / 12)
    Case Month(HireDate) = 11 And Year(HireDate) = 2015
        result = Potential * (1 / 12)
    Case Month(HireDate) = 12 And Year(HireDate) = 2015
        result = 0
    Case Year(HireDate) < 2015
        result = Potential
    Case Else
        result = 0
    End Select
    
    prbonus = result
     
End Function

 

[Sektor]

Should we guess about why it keeps returning 0? How we can know about it - did you think?

 

[STEVEMILLS04]

Should we guess about why it keeps returning 0? How we can know about it - did you think?

Of course. My ELSE statement is returning 0, but the function should be returning the "Potential" as the dates in my list have a year less than 2015. I verified that the data types are accurate, but cannot figure out why it is not working.

Any recommendations would be appreciated. I am just learning VBA and used this real world example of mine as a good way to jump in.

 

[Sektor]

I verified that the data types are accurate

But I didn't verified! Could you put your real file to some cloud storage?

 

[STEVEMILLS04]

But I didn't verified! Could you put your real file to some cloud storage?

Oops, sorry. Here is the file on my dropbox:
https://www.dropbox.com/s/qk5co0kpnwggpij/UDF Example.xlsm?dl=0

 

[Norie]

Have you tried stepping through the code with F8 to see what's actually happening, what values are being passed etc

PS It's not possible to download the file, seems to be some sort of problem when I try anyway.

 

[JoeMo]

Your Select Case is a date (hire date), not a month or a year. The only case listed that fits is Case Else which returns 0. Maybe:

Select Case Year(HireDate)
Case 2015
result = potential*(12- Month(HireDate))/12
Case Is <2015
result = potential
End Select

 

[STEVEMILLS04]

Your Select Case is a date (hire date), not a month or a year. The only case listed that fits is Case Else which returns 0. Maybe:

Select Case Year(HireDate)
Case 2015
result = potential*(12- Month(HireDate))/12
Case Is <2015
result = potential
End Select

Typically in formulas in a data cell, I would use

=IF(MONTH(F2)=1,"January","Not January")

where F2 might be 01/20/2015 and it returns January. Does this function not work in VBA?

 

[STEVEMILLS04]

Your Select Case is a date (hire date), not a month or a year. The only case listed that fits is Case Else which returns 0. Maybe:

Select Case Year(HireDate)
Case 2015
result = potential*(12- Month(HireDate))/12
Case Is <2015
result = potential
End Select

Ooops, sorry, I misunderstood your post. I will give that a try! That makes sense.

 

[Sektor]

I would re-write this function as follows:

Function prbonus(HireDate As Date, Potential As Currency) As Currency
    If Year(HireDate) <> 2015 Then Exit Function
    Select Case Month(HireDate)
        Case 1: prbonus = Potential * (11 / 12)
        Case 2: prbonus = Potential * (10 / 12)
        Case 3: prbonus = Potential * (9 / 12)
        Case 4: prbonus = Potential * (8 / 12)
        Case 5: prbonus = Potential * (7 / 12)
        Case 6: prbonus = Potential * (6 / 12)
        Case 7: prbonus = Potential * (5 / 12)
        Case 8: prbonus = Potential * (4 / 12)
        Case 9: prbonus = Potential * (3 / 12)
        Case 10: prbonus = Potential * (2 / 12)
        Case 11: prbonus = Potential * (1 / 12)
        Case 12: prbonus = 0
    End Select
End Function