Autor: Peter Haserodt  --- Aus Excel VBA - Gruppe: Tutorials

Applicaton.OnTime - Zeitgesteuerte Makros

Autor: Peter Haserodt - Erstellt: --      - Letzte Revision: --

Application.OnTime

Haben Sie etwas Zeit oder müssen Sie OnTime (~Zu einer bestimmten Zeit) weg?
Im letzeren Fall kommen Sie einfach nochmal später wieder, denn unser OnTime Ausflug dauert schon etwas.

Grundlage ist der Wunsch, ein Makro zu einer bestimmten Zeit oder nach Ablauf einer bestimmten Zeit zu starten.

Hierzu bietet Excel die Prozedur Application.OnTime an.
So einfach diese auch auf den ersten Blick aussieht, soviele Tücken hat diese auch.

Ich möchte sie Ihnen etwas genauer vorstellen - aber auf die Tücken reinfallen müssen Sie dann schon selber

Wichtige Hinweise:

1. Starten Sie die Makros nur einmal, wenn ich schreibe starten - außer ich will es ausdrücklich anders.
Denn diese sind mehrfach hintereinander ausführbar und können dabei am Anfang Verwirrung stiften.

2. Lesen Sie diesen Artikel bis zum Ende - oder gar nicht. Denn Application.OnTime hat es in sich und wenn Sie damit arbeiten wollen, sollten Sie sich genau damit beschäftigen.


Das Grundgerüst:

Die Excelhilfe bietet dazu folgendes an:
Ausdruck
.OnTime(EarliestTime, Procedure, LatestTime, Schedule)

Wobei Ausdruck für ein Application Objekt steht - in den meisten Fällen also unsere Application - und die Klammern für die Füße sind. (Außer ich setze ein Call davor, dann bitte mit Klammern)

Wir wollen uns dies mal übersetzen - so frei Schnauze - so halt wie ich's mir denke - einfach so halt:

Application.OnTime Wann, WelchesMakro ,[BisWannSollIchsVersuchen] ,[SetzenOderLöschen]

Damit ist es hoffentlich etwas besser zu verstehen. (Wir werden später noch sehen, dass unser Wann nicht ein so glücklicher Begriff ist)

Die Argumente in den eckigen Klammern sind optional anzugeben.
Und wenn etwas optional ist, dann fangen wir doch mal mit den Zwingenden an.

Schauen wir zuerst mal auf Wann und WelchesMakro

Dazu bauen wir uns eine Testumgebung auf.
Bitte seien Sie sehr sorgfältig mit den nachfolgenden Anweisungen, sonst funktioniert es nicht so, wie es soll.

Der erste Code

Erstellen Sie nachfolgenden Code in einem allgemeinen Modul
Public Sub StartZeitGeber()
 Application.OnTime Now + TimeValue("0:0:3"), "Ausfuehrende"
End Sub
 
Private Sub Ausfuehrende()
 MsgBox Format(Now, "hh:nn:ss")
End Sub

Führen Sie nun die Prozedur StartZeitGeber aus und warten Sie 3 Sekunden.
Und Hurra - wenn Sie schön brav waren, hat auch alles geklappt.

Dann schauen wir mal:

Unsre Wann ist hier der Ausdruck : Application.OnTime Now + TimeValue("00:00:03"),
Wann -EarliestTime- will einen genauen Zeitpunkt haben, an welchem etwas getan werden soll.

Tatsächlich hätte ich auch schreiben können: Application.OnTime TimeValue("17:30:00"), "Ausfuehrende" aber dies wäre für Sie fatal, wenn Sie diesen Artikel um 12:30 Uhr lesen (oder gar noch früher) denn dann hätte dies seine Zeit gedauert, bis die MsgBox erschienen wäre und Sie hätten mich wahrscheinlich GeOnTimed.

Also haben wir den auszuführenden Zeitpunkt einfach um 3 Sekunden nach der aktuellen Zeit festgelegt, durch den Ausdruck Now + TimeValue("00:00:03") - Merken Sie es ? In der Prozedur habe ich nur 0:0:3 geschrieben!
Frisst diese auch.
Wer will kann auch mal ausprobieren:
Application.OnTime Now + 3 / 86400, "Ausfuehrende"
und sich Gedanklich damit auseinandersetzen.

Bevor ich noch näher auf das
Wann eingehe, kurz zum WelchesMakro.
Dies ist sicherlich einfach zu verstehen. Hier schreibe ich die Prozedur hin, welche ausgeführt werden soll. Und zwar in doppelten Anführungszeichen. Wichtig ist natürlich, dass die Prozedur erreichbar sein muss. Hier habe ich sie als Private deklariert und das ist ok so, da sie ja im selben Modul steht. Nur so als Hinweis.
 Peter Haserodt
  • Formelhilfe
  • Makroentwicklung
  • VBA-Programmierung
  • + + + + + + + + +

Wann - EarliestTime-unter der Lupe

Dies ist so wichtig zu verstehen, dass es einen eigenen Kasten bekommt.

Tatsächlich habe ich EarliestTime mit Wann sehr schlecht übersetzt - es sollte ungefähr heißen: FrühesterZeitpunkt
Aber ich habe dies bewusst so gewählt, damit das Grundverständnis erweckt wird.

Tatsächlich ist unser Wann auch ersteinmal der Zeitpunkt, an welchem die Ausführung erfolgen soll.
Und wird auch in der Regel so sein.
Nur gibt es viele Situationen, in welchen dies für Excel nicht möglich ist.

Ein Beispiel:
Starten Sie die StartZeitgeber und klicken Sie dann ganz schnell in eine Zelle, damit Sie in der Zelle sind (also der Cursor blinkt) und warten Sie ca. 10 Sekunden.
Sie merken, während dieser Zeit passiert nichts.
Verlassen Sie nun die Zelle und die MsgBox erscheint sofort.
(Wenn dies nicht so richtig mit Ihrer Grundschnelligkeit des Zellenauswählens funktioniert, erhöhen Sie auf 10 Sekunden, also Now + TimeValue("00:00:10") - aber das haben Sie auch selber rausgefunden - oder)

Sie merken, dass der Begriff EarliestTime gar nicht so dämlich ist.
Denn ExcelVBA versucht die Anweisung zum angegebenen Zeitpunkt auszuführen, kann dies aus irgendwelchen Gründen nicht geschehen, wartet ExcelVBA so lange mit der Ausführung, bis Excel wieder für Makrooperationen bereit ist.

Und damit kommen wir zum:

BisWannSollIchsVersuchen - LatestTime

Aus dem zuvor Erlernten ist dies nun einfach zu erschließen:

Wir haben gesehen, dass ExcelVBA praktisch den nächsten freien Termin zu/nach unserem Wann wahrnimmt, um die Prozedur zu starten.
Dies ist wie der Flug von Timbuktu auf den Mond. Sagen wir der ist für 12:54:30 angesetzt, dann wird er auch zu diesem Zeitpunkt starten, wenn nichts dazwischen kommt. Ansonsten wartet der Pilot solange, bis er die Startfreigabe erhält.

Jetzt kann es aber passieren, dass man sagt: Ne, wenn der Flug nicht nach 3 Stunden gestartet ist, will ich gar nicht mehr (Weil der Pilot mittlerweile soviele Whiskeys in sich hat, dass selbst die mondsüchtige Stewardess Angst bekommt) .
Genau dies stellt uns unsere OnTime Anweisung mit dem optionalen Parameter LatestTime zur Verfügung.

Hier kann ich angeben, wann der Versuch abgebrochen werden soll.
Beispiel:
Wir ändern unsere Prozedur StartZeitGeber wie folgt:

Application.OnTime Now + TimeValue("00:00:03"), "Ausfuehrende", Now + TimeValue("00:00:30")

Nun probieren Sie wieder die Tests mit der Zelle und warten mal länger als 30 Sekunden.
Sie werden feststellen, dass das Makro dann nicht ausgeführt wird.
Und wer jetzt glaubt, dass ich zum vierten Argument übergehe, der irrt sich gewaltig.
Jetzt schauen wir ersteinmal was das mit dem Application auf sich hat.

Wo Application draufsteht ist auch Application drinne

Bitte ändern Sie wieder die StartZeitGeber damit dort die Zeile so heißt:

Application.OnTime Now + TimeValue("00:00:30"), "Ausfuehrende"


Genau so und nicht anders.
Ach, weil Sie es sind und bis jetzt durchgehalten haben, nochmal der ganze Code schön buntig:
Public Sub StartZeitGeber()
 Application.OnTime Now + TimeValue("00:00:30"), "Ausfuehrende"
End Sub
 
Private Sub Ausfuehrende()
 MsgBox Format(Now, "hh:nn:ss")
End Sub

Und jetzt speichern Sie die Mappe bitte - machen Sie ja sowieso immer - oder?
Aber diesmal wirklich!

Der Ablauf ist jetzt wie folgt, lesen Sie erst bist zu Ende:
Starten der StartZeitGeber
Dann die Mappe schließen, aber Excel unbedingt geöffnet lassen.
Dann genüsslich am Kaffee nuckeln.
Dann überrascht sein.

Also jetzt dürfen Sie loslegen.

Ja was war den dies nun. Spinnt Excel jetzt komplett oder wie oder was?
Nein, überhaupt nicht!
Es heißt doch Application.OnTime und nicht Workbook.OnTime!
Das ist an die Anwendung gekoppelt und nicht an den Mond.

Selbstverständlich haben Sie sofort ausprobiert, wie das ist, wenn Sie Excel schließen.
Klar, da wird der Prozess beendet!

Aber nun Schluss mit lustig. Können Sie denn nicht ernst bleiben. Das ist nicht zum Lachen. Das ist bittere Wahrheit und nichts als die Wahrheit so wie mir meine Oma das immer gesagt hat (mütterlicherseits).

Dies ist der Moment für:

SetzenOderLöschen - Schedule oder Kill the biest

Der Zauberlehrling: Walle Walle Walle - bis ich in die Falle falle
OK - Goethe würde es vielleicht nicht gefallen - aber ich bin ja nur ein armer P[o]eter

Hier kommen wir nun zum schwierigsten Teil des Ganzen. (Haben Sie wirklich geglaubt es bliebe so einfach wie bisher?)
Etwas starten ist ja ganz lustig, aber etwas aufzuhalten dann ..

Das vierte Argument - klingt fast nach Science Fiction - ist für das Stoppen einer angestossenen zeitgesteuerten Prozedur.
Lasse ich es weg - oder setze es auf True - dann bedeutet es: Starten.
Setze ich es hingegen auf False, dann bedeutet es: Ne, wird nicht mehr gebraucht.

Dies ist aber viel komplizierter als man denkt. Denn ExcelVBA muss ja auch erkennen, welches Ereignis es weghauen soll.

Nehmen wir an, Sie starten unsere StartZeitGeber zweimal.
Jetzt gibt es für Excel zwei Aufträge. Und zwar jeweils den Auftrag die Prozedur Ausführende durchzuführen, nur zu verschiedenen Zeitpunkten.
Um einen solchen Auftrag zu stoppen, brauchen wir also zwei Angaben, nämlich welche Prozedurausführung soll gestoppt werden und zu welchem Zeitpunkt sollte sie gestartet werden.
Wir brauchen also Informationen, um zu stoppen.
Dies ist bei einer festverdrahteten Ausführung nicht so schwierig - also wenn wir den Zeitpunkt auf z.B. 17:00:00 festlegen aber bei Zeitpunkten, die wir abhängig von der aktuellen Zeit - wie bisher benutzt - brauchen wir diese Information.
Sehr verwirrend - dann schauen wir es uns besser an einem Beispiel an:
Bitte nehmen Sie sich ein neues allgemeines Modul und :

Option Explicit
Dim iTimerSet As Double
 
Public Sub StartEs()
 iTimerSet = Now + TimeValue("00:00:15")
 Application.OnTime iTimerSet, "MachEs"
End Sub
 
Public Sub StopEs()
 Application.OnTime iTimerSet, "MachEs", , False
End Sub
 
Private Sub MachEs()
 MsgBox Format(Now, "hh:nn:ss")
End Sub


Verschiedene Experimente

Wir haben schon viele Worte verloren - beim Fundbüro abgegeben, deswegen nun einfach Experimente, an welchen Sie das ganze verstehen lernen.
Lesen Sie das Experiment immer erst durch, bevor Sie es ausführen.

1. Experiment:
Führen Sie StartEs aus und warten Sie 15 Sekunden.
Wie wir erwartet haben, erscheint unsere MsgBox

2.Experiment
Führen Sie StartEs aus und nach kurzer Zeit führen Sie auch StopEs aus.
Warten Sie nun - und Sie können warten wie Sie wollen, unsere MsgBox erscheint nicht.
Wir haben erfolgreich den Auftrag abgeschossen.

3.Experiment
Führen Sie StartEs aus und nach 2 drei Sekunden führen Sie StartEs nochmals aus.
Warten Sie nun und die MsgBox erscheint und nach Bestätigung der MsgBox erscheint die nächste MsgBox, denn wir haben ja zwei mal angekickt.

4.Experiment
Führen Sie StartEs aus und nach 2 drei Sekunden führen Sie StartEs nochmals aus.
Und gleich darauf führen Sie StopEs aus
Warten Sie nun und Überraschung: Die MsgBox erscheint - Einmal!

Dieses bedarf einer kurzen Erklärung:
In unserer Variablen iTimerSet speichern wir den Ausführungszeitpunkt.
Aber beim zweiten Starten der StartEs wird sie ja mit dem neuen Startwert überschrieben.
D.h. in unserer Sub StopEs wird nur die Aufgabe mit dem letzten Startwert gekillt.

5.Experiment
Führen Sie nur die StopEs aus.
Es erscheint eine Fehlermeldung. Es gibt nunmal keine Aufgabe mit den Angaben.

6.Experiment
Führen Sie StartEs aus und nach 2 drei Sekunden führen Sie StartEs nochmals aus.
Und gleich darauf führen Sie StopEs aus und gleich nochmals StopEs
Beim zweiten StopEs erscheint eine Fehlermeldung, denn in der Variablen iTimerSet haben wir ja nur den Wert vom zweiten Mal ausführen.
Beim Ausführen der StopEs wird dieser Auftrag gekillt.
(Und natürlich wird einmal die MsgBox angezeigt)
Und nun sind Sie dran

Mit diesen Experimenten sollten Sie ein Gefühl für die ganze Sache bekommen.
Wiederholen Sie diese, bis Sie es wirklich verstehen.
Denken Sie sich selbst neue Experimente aus. Versuchen Sie eine zweite Variable einzubauen.
Und und und..
Denn irgendwann muss ich nunmal Ihr Händchen loslassen und Sie müssen ganz alleine in die weite weite Welt hinaus.

Aber bevor dies geschieht, habe ich noch eine Kleinigkeit für Sie, in welchem wir das Ganze etwas zusammenfassen und auch ein Dauerfeuer uns anschauen. Sie sind schon gespannt?
Sie dürfen aber erst weiterlesen, wenn Sie fleißig waren.

Wiederholende

Nehmen Sie sich eine neue leere Arbeitsmappe.
Beachten Sie die Codeplazierung.
Einmal im Modul diese Arbeitsmappe und einmal in einem allgemeinen Modul!

' **************************************************************
'  Modul:  DieseArbeitsmappe  Typ = Element der Mappe(Sheet, Workbook, ...)
' **************************************************************

Option Explicit
 
Private Sub Workbook_BeforeClose(Cancel As Boolean)
 EndeUhr
End Sub


' **************************************************************
'  Modul:  mdlSekunden  Typ = Allgemeines Modul
' **************************************************************

Option Explicit
Dim iTimerSet As Double
 
Public Sub SekundenZaehler()
 ThisWorkbook.Worksheets(1).Range("a1").Value = Format(Now, "hh:nn:ss")
 iTimerSet = Now + TimeValue("00:00:01")
 Application.OnTime iTimerSet, "SekundenZaehler" 'Selbstaufruf
End Sub
 
Public Sub EndeUhr()
 On Error Resume Next ' Sehr faul programmiert
 Application.OnTime iTimerSet, "SekundenZaehler", , False
End Sub

Diese kleine Spielerei soll das ganze mal zusammenfassen und auch die Möglichkeit zeigen, wie man eine Aktion immer wieder startet.
Gleichzeitig sorgen wir beim Schließen der Arbeitsmappe dafür, dass unsere Stop Prozedur ausgeführt wird.
Damit aber keine Fehlermeldung auftritt, wenn überhaupt nicht gestartet wurde, habe ich dort einfach ein OnError Resume Next reingehauen. Nicht schön - aber wirksam.

OnTime für mich zu gehen

Ich hoffe, dass ich Ihnen die OnTime etwas näher bringen konnte.
Aber die ganzen Fallstricke müssen Sie nun selbst herausfinden.

Gestatten Sie mir aber noch einen Hinweis:


So schön die OnTime auch sein mag, gibt es bessere Methoden - nämlich mit den entsprechenden API - Timerfunktionen.
Diese finden Sie sehr schön in folgendem Artikel demonstriert:

Stoppuhr in Excel

Weitere Artikel der Gruppe: Tutorials Aus Excel VBA
Nach oben