OnTime method

tigrouh

New Member
Joined
Aug 10, 2005
Messages
33
Can anyone help me with this one please? What I want to do is automate recalculating a worksheet say every 5 seconds once it is opened. I have the code Application OnTime Now + TimeValue("00:00:05") but am unsure about how to tie this to the Calculate command and create a loop so that the code keeps running. Thanks in advance.
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
Hi,

Can anyone help me with this one please? What I want to do is automate recalculating a worksheet say every 5 seconds once it is opened. I have the code Application OnTime Now + TimeValue("00:00:05") but am unsure about how to tie this to the Calculate command and create a loop so that the code keeps running. Thanks in advance.

Application.OnTime requires a procedure. You may use something like this:

Code:
Sub MyRecalculate()

Application.Calculate
Application OnTime Now + TimeValue("00:00:05"), "MyRecalculate"

End Sub

Then, just run MyRecalculate once and everything will keep going on :)
 
Upvote 0
Reset Ontime in the called procedure. Example:

Code:
Private Sub Workbook_Open()
   Application.OnTime Now + TimeValue("00:00:05"), "DoCalc"
End Sub

Sub DoCalc()
   Application.Calculate 
   Application.OnTime Now + TimeValue("00:00:05"), "DoCalc"
End Sub
 
Upvote 0
Hi

You need to make it recursive ie:

Code:
Sub Recalc()
Application.Calculate
Application.OnTime Now + TimeValue("0:00:05"), "Recalc"

Note that while this will work, it has no automatic activation (ie you have to run the macro once you've opened your workbook). This could be fixed by including a script in the Workbook_Open event.

Also, even if you close the workbook, the Ontime will try to call the macro, so it will error out once you have closed the workbook (may not be a big issue for you), which requires more coding to avoid.

Best regards

Richard
 
Upvote 0
Thanks very much for all of your suggestions and they all work fine. The only problem I'm having is how to stop the application from running so that the workbook can be closed. Could anyone show me the code to do this please?
 
Upvote 0
Hi

Something like:

Code:
Public vSetTime As Variant   'in declarations section of a standard module (ie at the top)

Sub Recalc() 
Application.Calculate
vSetTime = Now+ TimeValue("0:00:05")
Application.OnTime vSetTime,"Recalc"
End Sub

Then, in the ThisWorkbook module include:

Code:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
Application.OnTime EarliestTime:=vSetTime, Procedure:="Recalc", Schedule:=False
On Error GoTo 0
End Sub

Best regards

Richard
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,218
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top