Volatile UDF evaluates to zero on opening workbook until I press F9or change a cell

[angelasg]

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

 

[Joel]

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]

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.

 

[Joel]

When you save a workbook all the changes should be saved. When you open the
book again nothing would change except for a Date or time function was being
used by the workbook or if you changed the data in the workbook without
opening the workbook (by a link or a macro).

I think the solution would be to write a workbook open macro that forces a
change to the workbook which will trigger your function to be called.

 

[angelasg]

I figured out the problem. It wasn't just when I opened the
workbook. Anytime a recalculated on a sheet other than the one with
the UDF, it evaluated to zero when I came back to the sheet. It turns
out I didn't specify the worksheet when I referenced the Cells
function below. Once I added that, the code worked fine.