[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

Re: Excel Macro timed execution



PureBytes Links

Trading Reference Links


At 10:27 AM 11/26/99 -0800, you wrote:
>What's the best way to get an Excel Macro to execute once a minute without 
>having to remember to got to the Excel window and press the associated
button?
>
>

Ullrich -

Here is some vba code to do what you want. There are 2 macro's : 
1 to run on a timed interval and a second one to turn off the timer. 
I assign them both to buttons on the sheet to make it easy to
run them. 

regards,
Rich



Dim NextTime As Date

Sub mytimer()
  NextTime = Now + TimeValue("00:01:00")
  
  'put your macro code here
  
  'test code below increments a cell by 10 on the timer
  mynum = Cells(1, 2).Text
  mynum = mynum + 10
  Cells(1, 2).Formula = mynum
  
  Application.OnTime NextTime, "mytimer"
End Sub

Sub StopIt() 'turns off the timed macro above
  Application.OnTime NextTime, "mytimer", schedule:=False
End Sub