[lang_en]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.[/lang_en]
[lang_de]Ein Freund hatte mich einmal gefragt, ob es nicht irgendwie möglich sei, einzelne Arbeitsblätter in Excel automatisch periodisch aktualisieren zu lassen.
Das erste was ich dann tat, war im Internet nach einer entspr. Lösung in Visual Basic for Applications zu suchen, aber das einzige was ich fand, war ein ActiveX Timer Control eines Drittanbieters, welches man auf einer Userform platzieren muß. Dann fand ich zufällig den VBA Befehl “Application.onTime” und hatte eine Idee.
Hier ist nun meine Lösung, welche nur das VBA onTime Kommando verwendet. Kein ActiveX Control oder irgend ein anderes 3rd-Party Modul sind hierfür notwendig.[/lang_de]
ExcelTimerExample_1.0.1.zip (10,9 KiB, 22.349 hits)
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.
Excelent code.
Thank you so much .
Thanks for the example. I hooked the timer up to 2 buttons, so i could control when it was functioning.
very useful indeed.
greg
Super,
einfach und wirksam.
Kleiner Fehler in VB interval verweist auf D8,
da ist aber nichts
If close the workbook, how to stop the marco first?
thx!
How we can activate Timer in Excel so while we lock the system the timer got activated.
9212034544
Very helpful to me. Thanks!!
Great code – many thanks
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
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.
Super, vielen Dank. Kann ich sehr gut gebrauchen zum periodischen/stündlichen Auslesen eines Temperatursensors am Comport mit Excel.
that’s is goodcode!
excelent!
oh! yea!
thank!
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
*just <- early signs of retardation
am i jest being retarded, or has the download link been removed?
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
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!