PDA

View Full Version : Solved: Execute macro every n minute



anandbohra
08-23-2007, 12:43 AM
:hi: 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
sub refresh()
appplication.calculate
end sub

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
08-23-2007, 01:00 AM
Add a public variable in your stanmdard code module



Public nTime As Double


amend your refresh code as follows



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


and in ThisWorkbook, add



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

Private Sub Workbook_Open()
Call refresh
End Sub


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
08-23-2007, 01:41 AM
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
08-23-2007, 02:13 AM
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
08-23-2007, 03:10 AM
Thanks XLD

u r always there when i need your help.


:rotlaugh:
:bow::bow::bow::bow::bow:

Bob Phillips
08-23-2007, 03:26 AM
With a little help from my friends!

unmarkedhelicopter
08-23-2007, 03:33 AM
Glug back some wine for the rest of us ;)

Bob Phillips
08-23-2007, 04:11 AM
That's an offer I cannot refuse!