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
- Add new comment
- 10296 reads

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.
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"
Run Code Every Time period
Excellent example code and video!
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.
Calculate
So typing in Calculate on its own line will just calculate all formulas on the spreadsheet, thus resetting the "=C7+RAND()"? Just checking to make sure I understand Calculate.
in VBA - yep
in VBA - yep