IT Consultant – Java, J2EE, IBM WebSphere Portal, Lotus Notes/Domino
RSS icon Home icon
  • Excel – VBA Timer Example v1.0.1

    (9 votes) 1 Star2 Stars3 Stars4 Stars5 Stars
    Loading...
    Posted on 6 April 2002 Sebastian Thomschke*/?> 17 comments

    A friend of mine asked me about how to let excel periodically update some worksheets.
    So the first thing I did was searching the web for a solution in Visual Basic for Applications. But the only one I found was to use a 3rd party ActiveX timer control which you have to place onto a userform. Then I found the VBA command “Application.onTime” and had an idea.

    Here is my solution only utilizing the VBA onTime command. No need for ActiveX control or any other 3rd party module.

    ExcelVBATimer-ScreenShot

      ExcelTimerExample_1.0.1.zip (10.9 KiB, 19,146 hits)

     

    17 responses to “Excel – VBA Timer Example v1.0.1” RSS icon

    • 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!

    • 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

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

    • *just <- early signs of retardation

    • Hartmut Gierke

      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

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

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

    • 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.

    • 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

    • Great code – many thanks

    • Very helpful to me. Thanks!!

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

    • If close the workbook, how to stop the marco first?

    • Super,

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

    • Thanks for the example. I hooked the timer up to 2 buttons, so i could control when it was functioning.

      very useful indeed.

      greg

    • Excelent code.
      Thank you so much .

    Leave a reply