1. How to add years into this vba code?

Hi guys,

I am hoping someone could help me make this code work a bit differently. Currently it calculates the monthly totals in a excel sheet but the problem occoured when we entered a new year. I would like to change the VBA so that is takes the year into account as well. Any suggestion is welcome!

Code:
```Public Function MProfit(mnth As String) As Currency

Application.Volatile
mon = Month([mnth] & " 1, 2015")
MProfit = 0

For I = 2 To 5000 Step 24
For J = 7 To 28 Step 7
If Not IsDate(Cells(I, J)) Then Exit Function
If Month(Cells(I, J)) = mon Then
MProfit = MProfit + Cells(I + 22, J)
End If
Next J
Next I

End Function```

2. Something like this:

Code:
```Public Function MProfit(mnth As String, lYear As Long) As Currency

Application.Volatile
mon = Month([mnth] & " 1, 2015")
MProfit = 0

For I = 2 To 5000 Step 24
For J = 7 To 28 Step 7
If Not IsDate(Cells(I, J)) Then Exit Function
If Month(Cells(I, J).Value) = mon And Year(Cells(I, J).Value) = lYear Then
MProfit = MProfit + Cells(I + 22, J).Value
End If
Next J
Next I

End Function```
Not sure why you'd use a volatile UDF for this though.

3. Originally Posted by rory
Something like this:

Code:
```Public Function MProfit(mnth As String, lYear As Long) As Currency

Application.Volatile
mon = Month([mnth] & " 1, 2015")
MProfit = 0

For I = 2 To 5000 Step 24
For J = 7 To 28 Step 7
If Not IsDate(Cells(I, J)) Then Exit Function
If Month(Cells(I, J).Value) = mon And Year(Cells(I, J).Value) = lYear Then
MProfit = MProfit + Cells(I + 22, J).Value
End If
Next J
Next I

End Function```
Not sure why you'd use a volatile UDF for this though.
This bit of code wasn't created by me and have to admit I am not confident with using it.

Would you have an easy-er suggestion? Also what is "volatile UDF"?

4. UDF = User Defined Function
Volatile means that it recalculates whenever the workbook does, even if none of its inputs have actually changed.

It appears to me that SUMIF or SUMPRODUCT formulas would achieve the same thing as your UDF does.

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•