How to execute a macro every 1 minute?

VitaminB6

Board Regular
Joined
Oct 11, 2004
Messages
208
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
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

VitaminB6

Board Regular
Joined
Oct 11, 2004
Messages
208
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

Well-known Member
Joined
Jan 16, 2006
Messages
1,624

ADVERTISEMENT

try this ( different approach );
Code:
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

Board Regular
Joined
Oct 11, 2004
Messages
208
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

Well-known Member
Joined
Jan 16, 2006
Messages
1,624

ADVERTISEMENT

did you put the runme macro on your StockPriceMain macro?

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

VitaminB6

Board Regular
Joined
Oct 11, 2004
Messages
208
Hi agihcam,

It works. Thank you.



did you put the runme macro on your StockPriceMain macro?

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

Legacy 98055

Guest
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="filter:progid: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

Board Regular
Joined
Oct 11, 2004
Messages
208
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
 

Watch MrExcel Video

Forum statistics

Threads
1,130,329
Messages
5,641,546
Members
417,219
Latest member
FrancoisT

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top