How to execute a macro every 1 minute?

[VitaminB6]

I have a macro which pulls data out from a database and I would like it to refresh the data every 1 minute. Below is my macro, does anyone know what commands should I use to run this macro every 1 minute.

Sub StockPriceMain()
Sheets("Temp").Select
Call StockPrice("9915")
End Sub

 

[texasalynn]

take a look at this
http://www.mrexcel.com/board2/viewtopic.php?t=222846

 

[Joe4]

You want the OnTime event. Searching the board return numerous hits, some that were even tied to Stock Market updates. Here are a few:

http://www.mrexcel.com/board2/viewtopic.php?t=28097&highlight=ontime+ticker

http://www.mrexcel.com/board2/viewtopic.php?t=120873&highlight=ontime+ticker

http://www.mrexcel.com/board2/viewtopic.php?t=121337&highlight=ontime+stock

 

[VitaminB6]

Thank you for all of your replies. This is helpful to me.
I would like it to refresh once a minute between 8:30am and 13:00pm. So, I modified one of the code as the following. However, it seems that the TimeSet is always smaller than TimeStart and TimeStop becasue of the TimeNow added before Timevalue("08:30:00") and Timevalue("13:30:00"). Without adding the TimeNow, the values are 0.35 and 0.59, respectively. I am confused with my code. Can someone help me with this. The intention is to refresh once a minute between 08:30am and 13:00pm.

Dim TimeNow As Double
Dim TimeStart As Double
Dim TimeStop As Double
Dim TimeSet As Double

Sub RunMeFirst()
TimeNow = Now
TimeStart = TimeNow + TimeValue("08:30:00") 'this sets the time at which all macros will Start
TimeStop = TimeNow + TimeValue("13:30:00") 'this sets the time at which all macros will stop
TimeSet = TimeNow + TimeValue("00:00:15")
Set_OnTime
End Sub

Sub Set_OnTime()

If TimeSet >= TimeStart And TimeSet <= TimeStop Then
Application.OnTime TimeSet, "TestMacro"
End If

End Sub

Sub TestMacro()
ActiveCell.Value = 1
ActiveCell.Offset(1, 0).Select
TimeSet = TimeSet + TimeValue("00:00:15")
Set_OnTime
End Sub

 

[agihcam]

try this ( different approach );

Dim timelimit, go, tm
Sub runme()
tm = Format(Now, "hh:mm:ss")
If tm > "13:30:00" Then Exit Sub
If tm > "08:29:59" Then
timelimit = 60
    go = Timer
    Do While Timer < go + timelimit
        DoEvents
    Loop
    StockPriceMain
    End If
End Sub


Sub StockPriceMain() 
Sheets("Temp").Select 
Call StockPrice("9915") 
runme
End Sub

 

[VitaminB6]

Hi, agihcam

Thank you for the reply. I put it in my macro and tested it. However, I found two issues about this macro,
1. It only ran once and then exit the sub. What I want is to refresh the data once a minute in between 8:30am and 13:30pm.
2. I was thinking to replace the second IF condition with Do while, but it seems using this way, it will consumes too much CPU resources.

Thanks

 

[agihcam]

did you put the runme macro on your StockPriceMain macro?

Sub StockPriceMain() 
Sheets("Temp").Select 
Call StockPrice("9915") 
runme 'did you include this macro?
End Sub

 

[VitaminB6]

Hi agihcam,

It works. Thank you.

did you put the runme macro on your StockPriceMain macro?

Sub StockPriceMain() 
Sheets("Temp").Select 
Call StockPrice("9915") 
runme 'did you include this macro?
End Sub

 

[Legacy 98055]

agihcam. Your creative code will eventually produce an "Out of stack space" error. Run it for several moments, pause your code, click on "View, Call Stack".

VitaminB6. Start this by running "Sub StartOnTime()" Assuming that the procedure, "StockPrice", is located within a standard module...

<table width="100%" border="1" bgcolor="White" style="filterrogid:DXImageTransform.Microsoft.Gradient(endColorstr='#C0CFE2', startColorstr='#FFFFFF', gradientType='0');"><tr><TD><font size="2" face=Courier New>  <font color="#0000A0">Option</font> <font color="#0000A0">Explicit</font>

  <font color="#0000A0">Private</font> <font color="#0000A0">Const</font> StartTime <font color="#0000A0">As</font> <font color="#0000A0">Date</font> = #8:30:00 AM#
  <font color="#0000A0">Private</font> <font color="#0000A0">Const</font> EndTime <font color="#0000A0">As</font> <font color="#0000A0">Date</font> = #1:30:00 PM#
  <font color="#0000A0">Private</font> <font color="#0000A0">Const</font> IntervalInSeconds <font color="#0000A0">As</font> <font color="#0000A0">Double</font> = 5
  <font color="#0000A0">Private</font> <font color="#0000A0">Const</font> ProcedureToRun <font color="#0000A0">As</font> <font color="#0000A0">String</font> = "StockPriceMain"
  <font color="#0000A0">Private</font> NextRunTime <font color="#0000A0">As</font> <font color="#0000A0">Date</font>

  <font color="#0000A0">Sub</font> StartOnTime()
       <font color="#0000A0">If</font> (TimeValue(Now) < StartTime) <font color="#0000A0">Or</font> (TimeValue(Now) > EndTime) <font color="#0000A0">Then</font>
           StopOnTime
           <font color="#0000A0">Exit</font> <font color="#0000A0">Sub</font>
       <font color="#0000A0">End</font> <font color="#0000A0">If</font>
       NextRunTime = DateAdd("s", IntervalInSeconds, Now)
       Application.OnTime NextRunTime, ProcedureToRun, , <font color="#0000A0">True</font>
  <font color="#0000A0">End</font> <font color="#0000A0">Sub</font>

  <font color="#0000A0">Sub</font> StopOnTime()
       <font color="#0000A0">On</font> <font color="#0000A0">Error</font> <font color="#0000A0">Resume</font> <font color="#0000A0">Next</font>
       Application.OnTime NextRunTime, ProcedureToRun, , <font color="#0000A0">False</font>
  <font color="#0000A0">End</font> <font color="#0000A0">Sub</font>

  <font color="#0000A0">Sub</font> StockPriceMain()
       StartOnTime
       Sheets("Temp").Select
       <font color="#0000A0">Call</font> StockPrice("9915")
  <font color="#0000A0">End</font> <font color="#0000A0">Sub</font>
</FONT></td></tr></table><button onclick='document.all("9112006223923750").value=document.all("9112006223923750").value.replace(/<br \/>\s\s/g,"");document.all("9112006223923750").value=document.all("9112006223923750").value.replace(/<br \/>/g,"");window.clipboardData.setData("Text",document.all("9112006223923750").value);'>Copy to Clipboard</BUTTON><textarea style="position:absolute;visibility:hidden" name="9112006223923750" wrap="virtual">
Option Explicit

Private Const StartTime As Date = #8:30:00 AM#
Private Const EndTime As Date = #1:30:00 PM#
Private Const IntervalInSeconds As Double = 5
Private Const ProcedureToRun As String = "StockPriceMain"
Private NextRunTime As Date

Sub StartOnTime()
If (TimeValue(Now) < StartTime) Or (TimeValue(Now) > EndTime) Then
StopOnTime
Exit Sub
End If
NextRunTime = DateAdd("s", IntervalInSeconds, Now)
Application.OnTime NextRunTime, ProcedureToRun, , True
End Sub

Sub StopOnTime()
On Error Resume Next
Application.OnTime NextRunTime, ProcedureToRun, , False
End Sub

Sub StockPriceMain()
StartOnTime
Sheets("Temp").Select
Call StockPrice("9915")
End Sub</textarea>

 

[VitaminB6]

Hi Tom,

Thank you for your reply. But I have one small question for you. Why do we start from "StartOnTime" instead of "StockPriceMain"?

Thanks