I understand what you are saying, but once I added the
Application.Volatile line, it does recalculate when I change a cell
anywhere in the workbook. The function works fine except for when I
first open the workbook.

On Dec 30, 6:11*am, Joel wrote:
A UDF only recalculates when cell are passed as part of the parameter list. *
You are reading cell values directly from the worksheet so excel doesn't
recaluclate the UDF when these cells change.



"angelasg" wrote:
I wrote a UDF that wasn't recalculating when I needed it to. *I added
Application.Volatile at the beginning of the code and that fixed it
except...when I saved the file, all the values turned to zeroes. *I
didn't understand why but I added Workbooks.Application.Calculate to
my Workbook_BeforeSave event procedure and that fixed it. *Then I
closed the workbook and reopened it, and the cells with the function
were back to zero. *I still didn't understand why but added the
Workbooks.Application.Calculate to the Workbook_Open event procedure
thinking this would fix the problem. *It didn't. *I tried variations
of the calculate method. *Still didn't help. *I tried changing the
calculation setting to manual then automatic in the code. *That still
didn't force a calculation. *Once I open the workbook and hit F9 or
change a cell, my numbers pop up.

Can anyone give me some advice? *The code is below should it help.
Thanks.

Public Function OCP_Hours(AgentNum As Double, PaidHrsPerDay As Double,
FloorDate As Date, Optional WorkDays As Integer = 5)

* * Application.Volatile

* * Dim RangeStart As Date
* * Dim WeeklyHours As Double
* * Dim DayNum As Integer
* * Dim HolidayFactor As Double
* * Dim ColumnIndex As Integer
* * Dim TrackerDate As Date
* * Dim Wk1Start As Date
* * Dim Wk1Stop As Date
* * Dim Wk2Start As Date
* * Dim Wk2Stop As Date
* * Dim Wk3Start As Date
* * Dim Wk3Stop As Date
* * Dim Wk4Start As Date
* * Dim Wk4Stop As Date
* * Dim Wk5Start As Date
* * Dim Wk5Stop As Date
* * Dim Wk6Start As Date
* * Dim Wk6Stop As Date
* * Dim OCPWk1Start As Date
* * Dim OCPWk1Stop As Date

* * Wk1Start = ThisWorkbook.Worksheets("Input").Range("Wk1Start")
* * Wk1Stop = ThisWorkbook.Worksheets("Input").Range("Wk1Stop")
* * Wk2Start = ThisWorkbook.Worksheets("Input").Range("Wk2Start")
* * Wk2Stop = ThisWorkbook.Worksheets("Input").Range("Wk2Stop")
* * Wk3Start = ThisWorkbook.Worksheets("Input").Range("Wk3Start")
* * Wk3Stop = ThisWorkbook.Worksheets("Input").Range("Wk3Stop")
* * Wk4Start = ThisWorkbook.Worksheets("Input").Range("Wk4Start")
* * Wk4Stop = ThisWorkbook.Worksheets("Input").Range("Wk4Stop")
* * If IsNumeric(ThisWorkbook.Worksheets("Input").Range(" Wk5Start"))
Then
* * * * Wk5Start = ThisWorkbook.Worksheets("Input").Range("Wk5Start")
* * * * Wk5Stop = ThisWorkbook.Worksheets("Input").Range("Wk5Stop")
* * End If 'IsNumeric(Worksheets("Input").Range("Wk5Start"))

* * If IsNumeric(ThisWorkbook.Worksheets("Input").Range(" Wk6Start"))
Then
* * * * Wk6Start = ThisWorkbook.Worksheets("Input").Range("Wk6Start")
* * * * Wk6Stop = ThisWorkbook.Worksheets("Input").Range("Wk6Stop")
* * End If 'IsNumeric(Worksheets("Input").Range("Wk6Start"))
* * ColumnIndex = ThisWorkbook.Application.ThisCell.Column
* * WeeklyHours = AgentNum * PaidHrsPerDay * 5
* * RangeStart = ThisWorkbook.ActiveSheet.Range("N3")
* * DayNum = Weekday(Cells(3, ColumnIndex))
* * HolidayFactor = Cells(1, ColumnIndex)
* * TrackerDate = Cells(3, ColumnIndex)

* * Select Case FloorDate
* * * * Case Wk1Start To Wk1Stop
* * * * * * OCPWk1Start = Wk1Start
* * * * * * OCPWk1Stop = Wk1Stop
* * * * Case Wk2Start To Wk2Stop
* * * * * * OCPWk1Start = Wk2Start
* * * * * * OCPWk1Stop = Wk2Stop
* * * * Case Wk3Start To Wk3Stop
* * * * * * OCPWk1Start = Wk3Start
* * * * * * OCPWk1Stop = Wk3Stop
* * * * Case Wk4Start To Wk4Stop
* * * * * * OCPWk1Start = Wk4Start
* * * * * * OCPWk1Stop = Wk4Stop
* * * * Case Wk5Start To Wk5Stop
* * * * * * OCPWk1Start = Wk5Start
* * * * * * OCPWk1Stop = Wk5Stop
* * * * Case Wk6Start To Wk6Stop
* * * * * * OCPWk1Start = Wk6Start
* * * * * * OCPWk1Stop = Wk6Stop
* * End Select 'FloorDate

* * If TrackerDate < FloorDate Then
* * * * OCP_Hours = 0
* * Else
* * If TrackerDate = OCPWk1Start And TrackerDate <= OCPWk1Stop Then
* * * * Select Case WorkDays
* * * * * * Case 5
* * * * * * * * Select Case DayNum
* * * * * * * * * * Case 2 To 6
* * * * * * * * * * * * OCP_Hours = WeeklyHours / 5 * HolidayFactor
* * * * * * * * * * Case Else
* * * * * * * * * * * * OCP_Hours = 0
* * * * * * * * End Select
* * * * * * Case 6
* * * * * * * * Select Case DayNum
* * * * * * * * * * Case 2 To 7
* * * * * * * * * * * * OCP_Hours = WeeklyHours / 6 * HolidayFactor
* * * * * * * * * * Case Else
* * * * * * * * * * * * OCP_Hours = 0
* * * * * * * * End Select
* * * * * * Case 7
* * * * * * * * Select Case DayNum
* * * * * * * * * * Case 1 To 7
* * * * * * * * * * * * OCP_Hours = WeeklyHours / 7 * HolidayFactor
* * * * * * * * * * Case Else
* * * * * * * * * * * * OCP_Hours = 0
* * * * * * * * End Select
* * * * * * Case Else
* * * * * * * * Select Case DayNum
* * * * * * * * * * Case 2 To 6
* * * * * * * * * * * * OCP_Hours = WeeklyHours / 5 * HolidayFactor
* * * * * * * * * * Case Else
* * * * * * * * * * * * OCP_Hours = 0
* * * * * * * * End Select
* * * * * * End Select 'Case WorkDays
* * End If 'TrackerDate = OCPWk1Start And TrackerDate <= OCPWk1Stop
* * End If 'TrackerDate < FloorDate

End Function- Hide quoted text -

- Show quoted text -