auto run a macro

[garima agrawal]

Hi,

I want to run a macro inMS excel, in specific time
intervals automatically. I need to update my data and
calculations in every five minutes for that I am using
formulas and macros.

Will it be possible to run a macro automaically, like auto
refresh facility in MS excel.

Thanks

Garima

 

[John Green]

Garima,

Use the OnTime method to schedule a macro. The following code (from Bill Manville, originally) shows how to repeat the same code
every ten seconds:

Dim NextTime As Date

Sub DoItRepeatedly()
DoItAgain
End Sub

Sub DoItAgain()
DoIt ' this is your procedure
NextTime = Now + TimeValue("00:00:10") ' 10 seconds on
Application.OnTime NextTime, "DoItAgain"
End Sub

Sub StopDoingIt()
Application.OnTime NextTime, "DoItAgain", schedule:=False
End Sub

Sub Auto_Close() ' otherwise it will re-open and do it again!
StopDoingIt
End Sub

 

[Neil]

Chong/Garima

Have a look at Chip Pearsons's Ontime page
http://www.cpearson.com/excel/ontime.htm

Neil