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:

1. TimeToRun is a global variable that we need to record and share between procedures
2. On opening the sheet, Auto_open is run and that schedules the copy over
3. Every second, the scheduler runs CopyPriceover
4. At the end of CopyPriceover, the scheduler is reset to run again the following second
5. Auto_close unschedules the CopyPriceover sub
6. 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: