9. VBA Tips - Run Code Every Hour, Minute or Second

Nick's picture

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:



  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:

run-code-every-hour-minute-or-second.xls32.5 KB

Strangely yes..

Strangely enough it does, does the version of excel i have created the script in affect its operation?

The macro builder accepted the code without error or fault.

Nick's picture

start a new forum and attach

start a new forum and attach an example file...

Does not seem to work my Think or Swim DDE


I think I've resolved it

I think I've resolved it issue.

I had the code locally on the workbook; having taken a closer look at your example file, i noticed that your code was in a global module.

Having moved my code to a module, it has now rectified my initial concern; a few have thrown up that previously weren't a problem with the retrieve code being locally based, but i'm working through them now.

Thank you for your support


For columns?

Hello Nick

I just changed the cell numbers used in this code to cell ranges (eg c7 to c5:c35/d5:d35) this does not seem to work. Is there more to change to achieve this?


Range("d5:d35").value =

Range("d5:d35").value = range("c5:c35").value
should work..

Old price stopped working

Thanks Nick
Changing to the range worked until I opened my sheet three days later.
Any clues as to what I need to look at, to see what may have happened?

most likely you are not

most likely you are not enabling macros


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