Issue Recalculating VB Formulas in Excel 2003

[btray843]

I have created a small function in Excel using VB (see below). The function
works fine but it will not automatically recalculate. I have tried using
CRT+ALT+F9 (as well as other standard ways of recalculating methods), but the
formula still does not update.
If I select each cell where the formula is located and then press F2 follwed
by enter the formulas is executed and a correct answer is displayed. The
problem is that I have over 8000 rows that need to be calculated. Can't do
that for every cell.
Any help??

Function Revised_Patient_In_Room_Time()
'Variables
Patient_In_Room = ActiveCell.Offset(0, -1).Range("A1").Value
Patient_Out_Of_Room = ActiveCell.Offset(0, 1).Range("A1").Value
PrimeTime_Start_Time_MTWF = Range("R2").Value
PrimeTime_Start_Time_Thursday = Range("R3").Value
Prime_Time_End_Time_M_thru_F = Range("R4").Value
Number_For_Day_Of_Week = ActiveCell.Offset(0, -3).Range("A1").Value

'Check to see if Thursday
If Number_For_Day_Of_Week = 5 Then
If (Patient_In_Room < PrimeTime_Start_Time_Thursday And
Patient_Out_Of_Room > PrimeTime_Start_Time_Thursday) Then
Revised_Patient_In_Room_Time = PrimeTime_Start_Time_Thursday
Else
If (Patient_In_Room > Prime_Time_End_Time_M_thru_F And
Patient_Out_Of_Room > PrimeTime_Start_Time_Thursday) Then
Revised_Patient_In_Room_Time = PrimeTime_Start_Time_Thursday
Else
Revised_Patient_In_Room_Time = ""
End If
End If
'Not Thursday so use M,T,W,F Start Time
Else
If (Patient_In_Room < PrimeTime_Start_Time_MTWF And Patient_Out_Of_Room

PrimeTime_Start_Time_MTWF) Then

Revised_Patient_In_Room_Time = PrimeTime_Start_Time_MTWF
Else
If (Patient_In_Room > Prime_Time_End_Time_M_thru_F And
Patient_Out_Of_Room > PrimeTime_Start_Time_MTWF) Then
Revised_Patient_In_Room_Time = PrimeTime_Start_Time_MTWF
Else
Revised_Patient_In_Room_Time = ""
End If
End If
End If
End Function

Thanks

 

[Tom Ogilvy]

Excel determines function dependencies by looking at the ranges passed in as
arguments - so modify your function to have the cells it is dependent on
passed in as arguments.

The alternative is to put

Application.Volatile = True

at the top of the function. Then it should recalculate whenever there is a
calculation event (like RAND)

 

[btray843]

Hi Tom,

Thanks for the suggestion regarding arguments. I had tried passing a few
variables (based on suggestions in previous group disscussion threads) but it
did not work.
Based on your comment, I made all "floating" data an argument and that
seemed to fix the problem.

Bernie