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:

run-code-every-hour-minute-or-second

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:

run-code-every-hour-minute-or-second

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:

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

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

Nick's picture

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?

Nick's picture

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

Nick's picture

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..