sebthom.de

Menu
  • Home
  • About me
  • Imprint
    • Datenschutzerklärung
  • Guestbook
Menu

Excel – VBA Timer Example v1.0.1

Posted on 2002-04-062013-01-12 by sebthom

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, 20,843 hits)

18 thoughts on “Excel – VBA Timer Example v1.0.1”

  1. hr says:
    27 February 2008 at 5:31 PM

    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 says:
    17 June 2008 at 10:49 PM

    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 says:
    22 October 2008 at 7:38 AM

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

  4. Andrew says:
    22 October 2008 at 7:39 AM

    *just <- early signs of retardation

  5. Hartmut Gierke says:
    6 November 2008 at 8:00 PM

    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 says:
    8 January 2009 at 11:43 AM

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

  7. Alex_F says:
    10 December 2009 at 11:25 AM

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

  8. Tony H says:
    10 February 2010 at 4:45 AM

    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) says:
    1 March 2010 at 1:30 AM

    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 says:
    20 July 2010 at 12:19 AM

    Great code – many thanks

  11. Sean says:
    31 March 2011 at 3:04 PM

    Very helpful to me. Thanks!!

  12. Chandra says:
    6 April 2011 at 9:33 PM

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

  13. muro says:
    28 July 2011 at 11:14 PM

    thx!

  14. SH says:
    18 December 2011 at 5:31 PM

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

  15. Adi says:
    25 January 2012 at 8:12 AM

    Super,

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

  16. greg says:
    11 March 2013 at 5:54 PM

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

    very useful indeed.

    greg

  17. David says:
    17 April 2014 at 11:21 AM

    Excelent code.
    Thank you so much .

  18. Luis Diego Villarreal says:
    13 May 2020 at 9:19 PM

    A query. If I have two templates that are using a timer like the example described here, each one independently acquiring electrical data from a PI Data (Excel) database, how can I avoid the two conflicting? in versions prior to office 2016 nothing else separated each excel in separate windows, but now, in 2016 it is not separating the timers and the VB runs them at the same time and the templates remain stuck.

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Language

    English English    Deutsch Deutsch    

Categories

  • IT (25)
    • Development (15)
      • Java (7)
      • Jython (3)
      • Visual Basic (5)
    • Freeware Tips (1)
    • Linux (3)
    • WebSphere Portal (2)
    • Windows (1)
    • Wordpress (4)
  • My Freeware (2)
  • My Music (3)

Recent Posts

  • [Solved] Windows 7 “Safely Remove Hardware” pop-up menu horrendously slow
  • Bash: Capturing stderr in a variable while still printing to the console.
  • Configuring EMF Teneo with Hibernate, Commons DBCP, Spring Hibernate Transaction Manager, and the OpenSessionInViewFilter
  • Using EMF ECore model objects with Wicket components
  • Installing Tomcat 6 on Debian Squeeze
  • Leveraging PyDev’s auto-completion for indirectly created objects
  • OVal 1.40 released
  • Installing WebSphere Portal in a local network
  • Comparing version numbers in Jython / Python
  • Running TomCat 6 in Debug Mode under Windows

Blogroll

  • E L S U A
  • elektrofever.de
  • OVal
  • Sweettt.com
  • Twins’ Running Blog

Recent Comments

  • Luis Diego Villarreal on Excel – VBA Timer Example v1.0.1
  • jonathan on Guestbook
  • Pierre on Lotus Notes’ [Send only] and [Send and File] buttons for Outlook 2003
  • Yereverluvinunclebert on MyPad v1.1.6 – a PHP Editor
  • Sebastian Thomschke on MyPad v1.1.6 – a PHP Editor

Archives

  • May 2012
  • January 2011
  • October 2010
  • September 2010
  • March 2010
  • February 2010
  • September 2009
  • July 2009
  • March 2009
  • February 2009
  • November 2008
  • September 2008
  • May 2008
  • April 2008
  • September 2007
  • July 2007
  • July 2004
  • March 2003
  • August 2002
  • April 2002
  • January 2002
© 2021 sebthom.de | Powered by Minimalist Blog WordPress Theme