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:
- 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...
Training Video on how to Run Code Every Hour Minute or Second in VBA: