9. VBA Tips - Run Code Every Hour, Minute or Second
In this VBA tip, we will learn how to run a piece of code every hour, minute, second or indeed any time:
- Suppose you have a live feed coming in for a share price
- You want to record your own price history for later analysis
- This tip will show you the code you need for this.
Here's our data:
So for demonstration purposes, lets look at how to copy over the price every second, and calculate the price change.
Here's a screen shot of the VBA we need:
Explanation:
- TimeToRun is a global variable that we need to record and share between procedures
- On opening the sheet, Auto_open is run and that schedules the copy over
- Every second, the scheduler runs CopyPriceover
- At the end of CopyPriceover, the scheduler is reset to run again the following second
- Auto_close unschedules the CopyPriceover sub
- We can set the time between runs using TimeValue:
- Now + TimeValue("00:00:01") - runs every second
- Now + TimeValue("00:00:10") - runs every 10 seconds
- Now + TimeValue("00:01:00") - runs every minute
- Now + TimeValue("01:00:00") - runs every hour
- Now + TimeValue("24:00:00") - runs every day... etc...
Download sheet to practise how to Run Code Every Hour Minute or Second in VBA
Training Video on how to Run Code Every Hour Minute or Second in VBA:
| Attachment | Size |
|---|---|
| run-code-every-hour-minute-or-second.xls | 32.5 KB |
»
- Nick's blog
- Login or register to post comments
- 281338 reads
Thank you and a small change
Thank you for the great tips. I am currently learning VBA and these are very useful.
May I suggest a small change to make sure the old share is written, put the calculate after the substitution:
Sub CopyPriceOver()
Range("c7").Value = Range("d7").Value
Calculate
Call ScheduleCopyPriceOver
End Sub
Thank you again.
Run Code Every Time period
Excellent example code and video!
Create a schedule to run Without Opening workbook
Hi Nick,
Is there a way to run this macro every 24hours without having to open the workbook.
Since I will not be in the office everyday, I will not be able to guarentee the reference workbook I created will be opened everyday to run this macro. This reference sheet will be used by other workbooks, used by other people.
Thanks,
Rob
have a google on: "schedule
have a google on: "schedule task windows"
Macro
Does this work with macro's? I.e. can i call a sorting macro to run every 30 seconds?
macros
yes.. just replace the "CopyPriceOver" with your sorting code.
Not working with my Think or Swim DDE spreadsheet
My apologies....total nube on this.
I have a TOS DDE running quotes into Excel. These quotes are coming into the spreadsheet in real-time milliseconds. I wanted to record values from one column over to another every 10 seconds for the purpose of comparison.
I tried adding this code below to make it happen, but nothing happens. Column E that I want the data to arrive into is staying blank and not changing. Help on this is appreciated.
Code:
Dim TimeToRun
Sub auto_open()
Call ScheduleCopyPriceOver
End Sub
Sub ScheduleCopyPriceOver()
TimeToRun = Now + TimeValue("00:00:10")
Application.OnTime TimeToRun, "CopyPriceOver"
End Sub
Sub CopyPriceOver()
Range("e4:e35").Value = Range("d4:d35").Value
Calculate
Call ScheduleCopyPriceOver
End Sub
Sub auto_close()
Application.OnTime TimeToRun, "CopyPriceOver", , False
End Sub
assuming your data is in col
assuming your data is in col D, that looks fine..
the only other thing is that "calculate" does not force DDE links to update..