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

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

Bartek

Board Regular
Joined
Jul 29, 2006
Messages
54
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 :)
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
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
 

tigrouh

New Member
Joined
Aug 10, 2005
Messages
33
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?
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
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
 

Forum statistics

Threads
1,137,330
Messages
5,680,857
Members
419,936
Latest member
rphill48

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
Top