I am a total newb in the world of VBA and a bit over my head. I have a background in Java, but I am wholly unfamiliar with VBA.
I'm working to improve on an already existing Excel workbook that is used to record time and attendance. Each pay period is recorded on a worksheet. I need to calculate the number of hours of "use or lose" leave that the employee has for the current year. Employees are allowed to carry over a maximum of 240 hours of annual leave from one year to the next. Anything above that is lost. I have a formula that works fine, but for each sheet I have to update the formula and I'm trying to make it so I don't have to do that. Hence, by dive into VBA.
I created a simple function that returns the correct formula as a string.
Function createFormula() As String
createFormula = "=SUM(" & "'" & ActiveSheet.Name & ":" & Sheets(Sheets.Count).Name & "'" & "!I24)" & "+I26-240"
End Function
This returns the correct formula, that looks something like this =SUM('PP03:PP26'!I24)+I26-240
It seems like I am on the right track but I obviously need the function to execute. I did some reading and I think what I need to do is to call Application.Evaluate to accomplish this. I modified the formula as follows, but it still just returns the string.
Function createFormula()
createFormula = "=SUM(" & "'" & ActiveSheet.Name & ":" & Sheets(Sheets.Count).Name & "'" & "!I24)" & "+I26-240"
Application.Evaluate (createFormula)
End Function
I've tried a bunch of other things that I won't bother to list. Posting this in the hope that someone can point me in the right direction.
***** Edit ***** It was suggested that I elaborate on the problem I'm trying to solve.
Each timesheet has several other calculations that involve bringing leave balances forward from the previous sheet. Past iterations of this timesheet, created by others and inherited by me, have formulas that are updated on each sheet. I was able to solve all of those except for the issue of calculating lose or use leave.
On each sheet we have:
Balance from Previous pay period, Annual Leave accrued, Annual leave used and Total to carry over to the next pay period
Because on each sheet, I know how many pay periods are left in the year, it would be easy to calculate the use or lose balance if the amount accrued was a constant but it is not. At least not always. I have formulas on each sheet that pre-populate the amount of leave accrued for that pay period. So what I need to do is iterate through all of the sheets and add those values up. Add that to the Total, subtract 240 and that is your use or lose.
I hope this helps to clarify what I'm trying to do.
I tried writing a comment but it is way too long. Your function when you use it in the worksheet is a formula itself. And it is already being evaluated since it is returning the string formula. That is what you've written using below lines:
createFormula = "=SUM(" & "'" & ActiveSheet.Name & _
":" & Sheets(Sheets.Count).Name & "'" & "!I24)" & "+I26-240"
If you want to return the value instead, then you should assign it to your function like this:
Function createFormula()
createFormula = "=SUM(" & "'" & ActiveSheet.Name & _
":" & Sheets(Sheets.Count).Name & "'" & "!I24)" & "+I26-240"
createFormula = Application.Evaluate(createFormula)
End Function
This is just answering your question but as what others commented, this is not the best approach. HTH.