macro to recalculate every 10 seconds

[Paul Moles]

I am trying to use an Excel worksheet as a backdrop to a rolling Powerpoint
presentation. Their is no human intervention with the sheet or the
presentation once it is running.

I need a macro to recalcualate the sheet every few seconds or at least once
a minute to keep the presentation updated.

The links all work, (Excel highlighted cells, Copy, Powerpoint, Paste
special link) it is just the sheet update I need.

The sheet will be running in the background and the macro could be manually
started or an autorun when the workbook is opened.

Many Thanks

Paul Moles

 

[Gary''s Student]

Public RunWhen As Double
Public Const cRunIntervalSeconds = 10
Public Const cRunWhat = "The_Sub"

Sub StartTimer()
RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds)
Application.OnTime earliesttime:=RunWhen, procedure:=cRunWhat, _
schedule:=True
End Sub

Sub The_Sub()
Calculate
StartTimer
End Sub

Sub StopTimer()
On Error Resume Next
Application.OnTime earliesttime:=RunWhen, _
procedure:=cRunWhat, schedule:=False
End Sub

begin by running StartTimer. The_Sub will run every 10 seconds.
finish by running StopTimer.

 

[Jarek Kujawa]

see Chip Pearson's examples at
http://www.cpearson.com/excel/TimedClose.htm

one possible solution might be to use those 2 macros (tested in Excel
2003 and they worked)

Sub delay()
runwhen = Now + TimeSerial(0, 0, 10)
Application.OnTime runwhen, "recount", , True
End Sub

Sub recount()
For i =1 to 100
Call delay
Calculate
Next i
End Sub