run macro at 10 mins past the hour

Posted on 2012-08-15
Last Modified: 2012-08-15
I want to run an excel macro at 10minutes past every hour while the workbook is open.  Is there a way to do this?
Question by:isense
    LVL 19

    Accepted Solution

    add a module to the workbook, and put the macro code in it.
    Make it a public sub.
    The sub can be called by using application.ontime

    Sub do_something()
        MsgBox "macro has been called"
       '-- schedule for next hour
       Application.OnTime TimeSerial(Hour(Now)+1, 10, 0), "do_something"
    End Sub
    Sub schedule_at_10_past()
        Application.OnTime TimeSerial(Hour(Now), 10, 0), "do_something"
    End Sub

    Open in new window

    LVL 24

    Assisted Solution

    this code for the application ontime event should do it...

    Public dTime As Date
    Sub RunOnTime()
        dTime = TimeSerial(Hour(Now), 10, 0)
        Application.OnTime dTime, "doMacro"
    End Sub
    Sub doMacro()
        MsgBox "Macro code here"
        dTime = TimeSerial(Hour(Now) +1, 10, 0)
        Application.OnTime dTime, "doMacro"
    End Sub

    Open in new window

    LVL 1

    Author Closing Comment

    Thanks guys.  That solved it

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Suggested Solutions

    Title # Comments Views Activity
    Detect file exist or not 3 37
    Excel add date to text 3 30
    IF OR formula Excel 8 22
    Excel IF/OR formula not working. 8 31
    Introduction This Article is a follow-up to my Mappit! Addin Article (, it was inspired by an email posting I made to EUSPRIG (, I will briefly cover: 1) An overvie…
    Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
    This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
    This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now