Running a subroutine periodically

[camylarde]

Hello,

I have a problem to have a sheet periodically recalculated every minute.

I have a simple procedure that calls itself every minute that goes basically like this:

Public Sub Recalculate(ByVal IDcko As Integer)
Calculate
    RecalculateTime = Now + TimeValue("00:01:00")
    Application.OnTime earliesttime:=RecalculateTime, _
    procedure:="'Recalculate 1'"
End Sub

The biggest problem with that construction is that if a user leaves excel open (with another workbook after it closes this workbook), this OnTime method will try to open the file back after a minute passes. A lot of problems are caused by this ranging from always reopening file, to runtime errors based on circumstances.

To tackle this i have decided that I need to move the procedure into Microsoft Excel Objects section under Sheet 1 and have it run for the first time when sheet gets activated, and reset the OnTime if the user deactivates the sheet. So I created the following :

Dim RecalculateRun As Boolean
Dim RecalculateTime As Variant
Private Sub Worksheet_Activate()
    Call Recalculate(0)
End Sub
 
Private Sub Worksheet_Deactivate()
 
If RecalculateRun Then
    RecalculateRun = False
Debug.Print "About to inactivate OnTime with " & RecalculateTime
Application.OnTime earliesttime:=RecalculateTime, _
    procedure:="'Recalculate 1'", Schedule:=False
    End If
End Sub

And I have run into another problem, that is that if I have the procedure Recalculate in the regular macro module, the variables RecalculateRun and RecalculateTime does not pass into the Sheet1 and I cannot close the OnTime prolerly,

Or

If i move everything into the Sheet1 code space the sub Recalculate cannot be found when the OnTime triggers.

I suspect a problem with properly defining global variables, or global subroutines or something, but can't figure it out on my own.

Please advise.

 

[NickHa]

You could put your variables into the ThisWorkbook object or a Module like this:

Public RecalculateRun As Boolean
Public RecalculateTime As Variant

Your Recalculate sub could go here as well.
If you use ThisWorkbook, you could put your Worksheet Activate/Deactivate code there as well, to generalise it for all worksheets:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Rem your code for sheet Sh Activate event
End Sub
Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
Rem your code for sheet Sh Deactivate event
End Sub

Why don't you use the Calculation Options in Excel to do what you need? I Excel 2007, Excel Options > Formulas > Calculation options > Automatic

 

[camylarde]

NickHa,

thank you very much, your advice was correct. Making the variables public and putting them to module header solved the problem.

Altough I now was able to turn the periodic recalculate on and off based on teh user presence in teh workbook, still excel had various issues when user hanged around in the various menues (like filtering) and got confused causing it to miss the switch off commands sometimes.

I discontinued the motion and use simple calculate when teh sheet is activated.

The reason i wanted this combo was because most of the users are viewing data in real time and some of them get updated in real time too and this recalculate loop was updating the data automatically.

Too buggy I'm afraid and so I've dropped it.

Thanks for the help anyway.