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

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.
andreas_rafnAsked:
Who is Participating?
 
Rory ArchibaldCommented:
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: http://www.vbforums.com/showthread.php?527281-VB6-SelfTimer-class-module-2008-06-15
0
 
Rory ArchibaldCommented:
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: http://support.microsoft.com/kb/180736
0
 
andreas_rafnAuthor Commented:
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

0
 
andreas_rafnAuthor Commented:
Works like a charm after removing the small part that refered to an object not included in the Excel VBA model.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.