Is there an Excel/VBA alternative to Application.OnTime that can be used within a class?

Posted on 2012-09-06
Last Modified: 2012-09-06
I'm trying to encapsulate a functionality within a class that can make the object perform a specific action with regular intervals. Usually one would use Application.OnTime or a alternatively a external COM object (unless its part of Windows), but neither of these options are feasible for my purpose.

Is there a way to implement this functionality within a class in Excel/VBA without Application.OnTime or a non windows class? Using a Windows component using Declare/Alias is perfectly acceptable as long as the component used is a part of Windows.
Question by:andreas_rafn
    LVL 85

    Expert Comment

    by:Rory Archibald
    You can use the SetTimer/KillTimer API for that:
    Declare Function SetTimer Lib "user32" ( _
                              ByVal hWnd As Long, ByVal nIDEvent As Long, _
                              ByVal uElapse As Long, ByVal lpTimerFunc As Long) As Long
    Declare Function KillTimer Lib "user32" ( _
                               ByVal hWnd As Long, ByVal uIDEvent As Long) As Long

    Open in new window

    See MSKB here:

    Author Comment

    I read about those, but I don't see how I can fire an event internally in the class using these. I'm looking for something that hypothetically can do that same as the below code:

    (In a class module)
    Private WithEvents mEventTimer As EventTimer
    Private Sub Class_Initialize()
        Set mEventTimer= New EventTimer
        mEventTimer.TimerInterval = 5
        mEventTimer.TimerEnabled = True
    End Sub
    Private Sub mmEventTimer_TimerAction()
        Debug.Print "Update"
    End Sub

    Open in new window

    LVL 85

    Accepted Solution

    Sorry - wasn't thinking clearly. Unless you want to write code to modify the project your class is in, I think you're out of luck, unless something like this works for you:

    Author Closing Comment

    Works like a charm after removing the small part that refered to an object not included in the Excel VBA model.

    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

    How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
    This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
    This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
    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

    19 Experts available now in Live!

    Get 1:1 Help Now