Solved: Execute macro every n minute

[anandbohra]

all

is there is any way to execute macro every 1 minutes.
like in web query we can set time to update query every n minutes.

the concept behind this is
I had downloaed smf_addin which fetch the yahoo live data thgrough custom function (without web query) now to refresh the data i have to calculate the sheet.
simple code
[vba]sub refresh()
appplication.calculate
end sub
[/vba]
now i want to run this code for every n seconds / minutes (user defined)
& also needs the way to stop doing this (say end schedule).


pl help me.

[Bob Phillips]

Add a public variable in your stanmdard code module

[vba]

Public nTime As Double
[/vba]

amend your refresh code as follows

[vba]

Sub refresh()
Appplication.Calculate
ntime = Now + TimeSerial(0, 1, 0)
Application.OnTime ntime, "refresh"
End Sub
[/vba]

and in ThisWorkbook, add

[vba]

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.OnTime ntime, "refresh",,false
End Sub

Private Sub Workbook_Open()
Call refresh
End Sub
[/vba]

This is workbook event code.
To input this code, right click on the Excel icon on the worksheet
(or next to the File menu if you maximise your workbooks),
select View Code from the menu, and paste the code

[unmarkedhelicopter]

surely the second "=" should be a "+" ???
Bob, your typo's are getting worse, when are you due for a holiday ?

maybe he should have a sub called refresh, and in that (along with his refresh code) have the "ntime = Now + TimeSerial(0, 1, 0)" again ???

[Bob Phillips]

You are right. Hopefully I have improved it now.

Problem is these things are so second-nature to us, we just bang them out without thinking. Sometimes the mind is ten steps ahead of the fingers.

My holiday is start of Sep, a week in Brittany. As you say, certainly due.

[anandbohra]

Thanks XLD

u r always there when i need your help.

[Bob Phillips]

With a little help from my friends!

[unmarkedhelicopter]

Glug back some wine for the rest of us

[Bob Phillips]

That's an offer I cannot refuse!