14 responses to “Excel – VBA Timer Example v1.00”

1. 

   hr 17:31 at 17:31

   Das Programm verwendet die dafür vorgesehene Excel-VBA-Funktion
   Application.OnTimer(AufrufUhrzeit as Date, NameMeinerSub as String)
   wie in der Excel-Hilfe vorgeschlagen:
   Application.OnTimer( now()+TimeSerial(0,0,5) , NameMeinerSub as String) ‘ 5 Sekunden
   Bug/Problem der Funktion: Wenn die Uhr des PC um 1 Stunde vorgestellt wird, dauert der nächste Aufruf 1 Stunde!

2. 

   Alain Bigio 22:49 at 22:49

   Dear Sebastian,

   I am trying to update my portfolio by picking up the quotations at Yahoo every 20 minutes. I would like to understand the way this macro works. the button start timer activates which macro. the code I have to put where you say “put your code here” does not seem to execute. Can you please help

   Thank you

   Alain

3. 

   Andrew 7:38 at 7:38

   am i jest being retarded, or has the download link been removed?

4. 

   Andrew 7:39 at 7:39

   *just <- early signs of retardation

5. 

   Hartmut Gierke 20:00 at 20:00

   Hi Andrew,

   try the following as VBA code on a worksheet (e.g. table1):
   ”————————————–
   ”Require that all variables be declared
   Option Explicit
   Dim Execute_TimerDrivenMacro As Boolean
   ‘

   Sub Start_OnTimerMacro()
   Execute_TimerDrivenMacro = True
   Application.OnTime Time + TimeValue(“00:00:01″), ActiveSheet.Name & “.OnTimerMacro”
   End Sub

   Sub Stop_OnTimerMacro()
   Execute_TimerDrivenMacro = False
   End Sub

   Public Sub OnTimerMacro()
   If Execute_TimerDrivenMacro Then
   ‘ Do something e.g. put the actual time into cell A1 of the active sheet
   ActiveSheet.Cells(1, 1).Value = Time

   ‘ At the end restart timer
   Application.OnTime Time + TimeValue(“00:00:01″), ActiveSheet.Name & “.OnTimerMacro”
   End If
   End Sub

   Private Sub Worksheet_Activate()
   ‘Start the timer driven method when opening the sheet
   Start_OnTimerMacro
   End Sub

   Private Sub Worksheet_Deactivate()
   ‘Stop the timer driven method when opening the sheet
   Stop_OnTimerMacro
   End Sub
   ”————————————–

   Hope that helps.
   Kind regards,
   Hartmut

6. 

   devit 11:43 at 11:43

   that’s is goodcode!
   excelent!
   oh! yea!
   thank!

7. 

   Alex_F 11:25 at 11:25

   Super, vielen Dank. Kann ich sehr gut gebrauchen zum periodischen/stündlichen Auslesen eines Temperatursensors am Comport mit Excel.

8. 

   Tony H 4:45 at 4:45

   Thanks for posting this code. I was looking for an example of a on_timer function for Excel that matches the functionality of MFC. This example was more helpful then the Microsoft help.

   Appreciate it.
   T.H.

9. 

   Chris (Atlanta GA) 1:30 at 1:30

   Very cool procedures. You need to edit this line of code to make it work:
   CHANG Cell Reference in “interval = CDbl(Range(“D8″).Value)” to
   interval = CDbl(Range(“D7″).Value)

   Very elegant and nice. Solved my problem

10. 

    Graham 0:19 at 0:19

    Great code – many thanks

11. 

    Sean 15:04 at 15:04

    Very helpful to me. Thanks!!

12. 

    Chandra 21:33 at 21:33

    How we can activate Timer in Excel so while we lock the system the timer got activated.
    9212034544

13. 

    muro 23:14 at 23:14

    thx!

14. 

    Adi 8:12 at 8:12

    Super,

    einfach und wirksam.
    Kleiner Fehler in VB interval verweist auf D8,
    da ist aber nichts