Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1728
  • Last Modified:

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.
0
andreas_rafn
Asked:
andreas_rafn
  • 2
  • 2
1 Solution
 
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
 
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
 
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now