Suggesties Application.Ontime


Wat is application.ontime

1 Eenmalige aktie
1.0 Vier argumenten

1.1 EarliestTime
1.1.1 absoluut tijdstip
1.1.2 relatief tijdstip

1.2 Procedure: macronaam
1.2.1 in hetzelfde werkboek
1.2.2 in ander werkboek
1.2.3 in gesloten werkboek
1.2.4 inhoud van macro

1.3 LatestTime

1.4 Schedule

1.5 Aanroepende macro

2 zichzelf aanroepende macro

2.1 Variabele voor tijdstipopslag
2.1.1 In werkblad
2.1.2 In werkboek
2.1.3 In macromodule
2.1.4 BeforeClose

2.2 Tijdstipopslag in Excel
2.2.1 Application 'Name'
2.2.2 Statusregel (Statusbar)
2.2.3 'Aangepaste lijst'

2.3. Tijdstipopslag in werkboek
2.3.1 Werkboek 'Name'
2.3.2 Gebruikerseigenschap
2.3.3 Cel in werkblad

3 Simultane akties
3.1 eenmalig
3.2 herhalend

Wat is application.ontime

Application.Ontime is een methode in Excel waarmee je een macro in een werkboek (Excel) kunt laten uitvoeren op een vooraf bepaald tijdstip

Ontime is een onderdeel van de applicatie (Excel) en werkt dus onafhankelijk van ieder werkboek.
Zolang de applicatie (Excel) geopend is blijft de ontime methode aktief.
Ook al is een werkboek gesloten dan start de Applicatie de macro toch door eerst het werkboek met de macro te openen en dan de macro uit te voeren.
Zo gauw de applicatie (Excel) wordt gesloten worden alle ontime akties opgeheven.

1 Eenmalige ontime aktie

Bijv. start de macro M_snb om 12.45 uur
Sub M_snb_ontime_starten()
Application.OnTime TimeSerial(12, 45, 0), "M_snb"
End Sub

Sub M_snb()
MsgBox "bijvoorbeeld"
End Sub

1.0 De vier argumenten van de methode ontime

De methode OnTime heeft 4 argumenten: EarliestTime, Procedure, LatestTime, Schedule

1.1 EarliestTime: het tijdstip waarop de macro gestart moet worden

1.1.1 een absoluut tijdstip

voorbeeld: om 12.45 uur
Sub M_snb_ontime_starten()
Application.OnTime TimeSerial(12, 45, 0), "M_snb"
End Sub
Sub M_snb_ontime_starten()
Application.OnTime TimeValue("12:45:00"), "M_snb"
End Sub
Sub M_snb_ontime_starten()
Application.OnTime CDate("12:45:00"), "M_snb"
End Sub
Een tekstreeks, die Excel als een tijdstip kan interpreteren kan ook ingevoerd worden.
Sub M_snb_ontime_starten()
Application.OnTime "12:45", "M_snb"
End Sub
Sub M_snb_ontime_starten()
Application.OnTime "12.45", "M_snb"
End Sub
Omdat Excel niet altijd consistent met tijdstipgegevens omgaat (soms Amerikaans, soms conform de internationale instellingen van Windows) is het gebruik van tijdfunkties als cdate, timeserial en timevalue het meest betrouwbaar.

1.1.2 een relatief tijdstip

voorbeeld: vier uur na nu
Sub M_snb_ontime_starten()
Application.OnTime DateAdd("h", 4, Time), "M_snb"
End Sub
Sub M_snb_ontime_starten()
Application.OnTime DateAdd("h", 4, Now), "M_snb"
End Sub
Sub M_snb_ontime_starten()
Application.OnTime Time + 4 / 24, "M_snb"
End Sub
Sub M_snb_ontime_starten()
Application.OnTime Now + 4 / 24, "M_snb"
End Sub

1.2 Procedure: de naam van de aan te roepen macro

Een macro kan zich in verschillende codemodules bevinden:
In Excel: het werkboek, een werkblad, een macromodule, een userform, of een klasse
Een te starten macro in een userform of een klasse laten we buiten beschouwing.

De naam van de macro wordt als tekst ingevoerd (zie de voorbeelden), net zoals bij de methode Application.Run.
Als de macro in een macromodule staat, hoeft de naam van de macromodule niet toegevoegd te worden, tenzij in een andere macromodule een macro met dezelfde naam staat.
Daarom is het verstandig om altijd de 'codename' van de codemodule toe te voegen aan de macronaam.

1.2.1 de aan te roepen macro staat in hetzelfde werkboek als de aanroepende macro

De aan te roepen macro staat in macromodule 'module2'
Sub M_snb_ontime_starten()
Application.Ontime DateAdd("h", 4, Time), "module2.M_snb"
End Sub
De aan te roepen macro staat in de codemodule van ThisWorkbook
Sub M_snb_ontime_starten()
Application.Ontime DateAdd("h", 4, Time), "ThisWorkbook.M_snb"
End Sub
De aan te roepen macro staat in de codemodule van Blad3
Sub M_snb_ontime_starten()
Application.Ontime DateAdd("h", 4, Time), "Blad3.M_snb"
End Sub

1.2.2 De aan te roepen macro staat in een ander werkboek

In een applicatie zijn alle macro's beschikbaar die zich in geladen bestanden bevinden (zichtbare bestanden, verborgen bestanden (zoals bijv. het persnlk.xlsb ) of geladen Add-Ins).
Gebruik dan ook de bestandsnaam om de macro op te roepen.

De macro 'M_evenaar' in de codemodule van 'sheet2' van bestand 'Voorbeeld.xlsb'
Sub M_snb_ontime_starten()
Application.Ontime DateAdd("s", 1, Time), "Voorbeeld.xlsb!sheet2.M_evenaar"
End Sub
De macro 'M_equator' in de codemodule van 'sheet1' van bestand 'Persnlk.xlsb'
Sub M_snb_ontime_starten()
Application.Ontime DateAdd("s", 1, Time), "Persnlk.xlsb!sheet2.M_equator"
End Sub
De macro 'M_equator' in de codemodule 'Module1' 'sheet1' van AddIn 'snb_addin.xlam'
Sub M_snb_ontime_starten()
Application.Ontime DateAdd("s", 1, Time), "snb_addin.xlam!Module1.M_equator"
End Sub

1.2.3 De aan te roepen macro staat in een gesloten werkboek

Ook een macro in een gesloten werkboek kan worden gestart met de methode Ontime.
Het werkboek wordt dan eerst geopend en vervolgens wordt de macro uitgevoerd

Bijv. de macro "M_equator" in macromodule Module1 in het gesloten werkboek "G:\OF\__ontime geslotensnb.xlsb"
Sub tst()
Application.Ontime DateAdd("s", 1, Time), "G:\OF\__ontime geslotensnb.xlsb!Module1.M_equator"
End Sub

1.2.4 De inhoud van de aan te roepen macro

De aan te roepen macro wordt uitgevoerd op een moment dat andere werkboeken geladen / geopend / aktief zijn dan op het moment dat de macro is aangeroepen.
Dat is geen probleem als de uit te toe voeren code onafhankelijk is van het aktieve werkboek, bijv.
Sub M_snb() MsgBox "bijvoorbeeld"
End Sub
Dat wordt anders bij
Sub M_snb()
Range("A1:C10").copy range("K1")
End Sub
De macro wordt nu uitgevoerd in het werkblad in het werkboek dat juist op het moment van uitvoeren aktief is.
Dat zal meestal niet de bedoeling zijn.
Om ervoor te zorgen dat de macro precies daar wordt uitgevoerd waar je het wil moet je zowel het werkboek als het werkblad specificeren. Bijv.
Sub M_snb()
workbooks("voorbeeld.xlsx").sheets("data").range("A1:K10").clearcontents
End Sub

1.3 LatestTime

De aan te roepen macro kan niet altijd worden uitgevoerd, bijv. omdat een gebruiker juist iets in een cel aan het invoeren is, er een ander macro aktief is, etc.
In tegenstelling tot wat de naam van het argument doet vermoeden kun je hier aangeven hoelang de ontime methode moet proberen de macro toch uit te voeren.
Als dit argument geen waarde bevat of wordt weggelaten wordt de macro uitgevoerd zo gauw dat mogelijk is.

1.4 Schedule

Dit argument schakelt de methode in (true) of uit (false).
Omdat de defaultwaarde 'True' is, is alleen de uitschakeloptie interessant.
Schakel de ontime aktie in:
Sub M_snb_ontime_starten()
Application.Ontime TimeSerial(12, 45, 0), "M_snb"
End Sub

Sub M_snb()
MsgBox "voorbeeldtekst"
End Sub
Schakel de ontime aktie uit::
Sub M_snb_ontime_sluiten()
Application.Ontime TimeSerial(12, 45, 0), "M_snb", , False
End Sub
NB. De uitschakeling werkt natuurlijk alleen als het ontime-tijdstip nog niet is geweest.
De uitschakelmacro vereist de exact identieke weergave van tijdstip en macronaam

Start de uitschakelmacro niet via alt-F8; dat leidt vaak tot een foutmelding Error 400.
Koppel de macro aan een werkbladgebeurtenis, een werkboekgebeurtenis, een ActiveX-control, een afbeelding, etc. ter vermijding van deze foutmelding.

1.5 De aanroepende macro

De aanroepende macro kan overal geplaatst worden:
- in de codemodule van een werkboek
- in de codemodule van een werkblad
- in de codemodule van een userform
- in de codemodule van een klasse

De aanroepende macro kan een zelfstandige macro zijn (zoals in de voorbeelden hierboven)

De aanroepende macro kan onderdeel zijn van een gebeurteniscode van een:

- werkboek
Private Sub Workbook_Open()
Application.OnTime DateAdd("s", 4, Time), "ThisWorkbook.M_snb"
end sub
- werkblad
Private Sub Worksheet_BeforeDoubleClick()
Application.OnTime DateAdd("s", 4, Time), "ThisWorkbook.M_snb"
end sub
- activeX-control
Private Sub Commandbutton1_Click()
Application.Ontime DateAdd("s", 4, Time), "ThisWorkbook.M_snb"
end sub

2 een zichzelf aanroepende macro met application.ontime instructie

Een macro met ontime-instructie kan zichzelf oproepen.

De macro wordt iedere dag om 12.45 uur uitgevoerd
Sub M_snb_ontime_starten() MsgBox "voorbeeldtekst"
Application.Ontime TimeSerial(12, 45, 0), "M_snb_ontime_starten"
End Sub
Als deze macro eenmaal is gestart zal deze steeds weer een nieuwe aktie aanmaken en blijven doorlopen totdat Excel (de applicatie) wordt gesloten.
Als je daarop niet wil wachten of dat onwenselijk vindt zul je de aktie moeten sluiten.
De enige manier om een ontime-aktie te stuiten is om het argument 'Schedule' met exact dezelfde (qua naam én tijd ) in te stellen op FALSE.

Stop de geplande aktie voordat het 12.45 uur is:
Sub M_snb_ontime_sluiten()
Application.Ontime TimeSerial(12, 45, 0), "M_snb_ontime_starten", , False
End Sub
In dit geval is dat eenvoudig omdat het om een vast tijdstip gaat.

Bij een variabel tijdstip - bijv. dateadd("s",10,time) - werkt dit niet; daarvoor zul je ergens die tijd moeten registreren om de ontime-aktie te kunnen stoppen.

Dat kan op 2 manieren:
- gebruik een variabele die in beide macro's (de start-macro en de sluit-macro) beschikbaar is
- gebruik een eigenschap van de application (Excel), van een werkboek of een werkblad of een van de objecten die deel uitmaken van de application, een werkboek of een werkblad

2.1 Variabele om tijdstip te registreren

Om een variabele beschikbaar te maken in verschillende macro's, moet je hem declareren als Private of Public.
Het heeft de voorkeur de variabele als Private te declareren, zodat er zo min mogelijk interferentie met andere code optreedt.
Private betekent in dit geval: alle macro's die in dezelfde codemodule staan (van het werkboek, van één werkblad, van één userform), kunnen van de variabele gebruik maken.

2.1.1 In een werkblad

De code komt te staan in de codemodule van werkblad sheet1.
De aktie wordt iedere 5 seconden uitgevoerd.
Dim t

Sub M_snb_ontime_starten()
MsgBox "voorbeeldtekst"

t = DateAdd("s", 5, Time)
Application.Ontime t, "sheet1.M_snb_ontime_starten"
End Sub

Sub M_snb_ontime_sluiten()
Application.Ontime t, "sheet1.M_snb_ontime_starten", , False
End Sub

2.1.2 In het werkboek

De code komt te staan in de codemodule van ThisWorkbook.
De aktie wordt iedere 5 seconden uitgevoerd.
Dim t

Sub M_snb_ontime_starten()
MsgBox "voorbeeldtekst"

t = DateAdd("s", 5, Time)
Application.Ontime t, "Thisworkbook.M_snb_ontime_starten"
End Sub

Sub M_snb_ontime_sluiten()
Application.Ontime t, "ThisWorkbook.M_snb_ontime_starten", , False
End Sub

2.1.3 In een macromodule

De code komt te staan in een 'gewone' macromodule.
De aktie wordt iedere 5 seconden uitgevoerd.
Dim t

Sub M_snb_ontime_starten()
MsgBox "voorbeeldtekst"

t = DateAdd("s", 5, Time)
Application.Ontime t, "M_snb_ontime_starten"
End Sub

Sub M_snb_ontime_sluiten()
Application.Ontime t, "M_snb_ontime_starten", , False
End Sub

2.1.4 In de BeforeClose gebeurtenis van het werkboek

Als je de ontime aktie pas wil sluiten als het werkboek wordt gesloten is het voor de hand liggend gebruik te maken van de gebeurtenis 'BeforeClose'.
Omdat de gebeurtenis 'BeforeClose' een werkboek-gebeurtenis is, is het handig gebruik te maken van een variabele met de starttijd in dezelfde codemodule als waarin de Beforeclose-gebeurtenis plaatsvindt: de 'ThisWorkbook' codemodule.

de code komt er dan zo uit te zien in de codemodule van 'ThisWorkbook':
Dim t

Sub M_snb_ontime_starten()
MsgBox "voorbeeldtekst"

t = DateAdd("s", 5, Time)
Application.Ontime t, "Thisworkbook.M_snb_ontime_starten"
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.Ontime t, "ThisWorkbook.M_snb_ontime_starten", , False
End Sub

2.2 Excel (Applicatie) eigenschap/object-eigenschap om een tijdstip te registreren

2.2.1 Een Application 'Name'

Maak voor het starttijdstip een 'name' aan.
Het 'toevoegen van een 'name' die al bestaat leidt tot de vervanging van die 'name'.
De waarde in de 'name' moet in Excel 'geëvalueerd' worden; bijv. de waarde 12:00:00 wordt nl. opgeslagen als "=0.5".
In Excel kan die evaluatie met vierkante haken [...] of met Evaluate(...) uitgevoerd worden.
Sub M_snb_ontime_starten()
Sheet1.TextBox1.Text = Time

Application.Names.Add "n_time", DateAdd("s", 1, Time)
Application.Ontime [n_time], "Thisworkbook.M_snb_ontime_starten"
End Sub

Sub M_snb_ontime_sluiten()
Application.Ontime [n_time], "Thisworkbook.M_snb_ontime_starten", , False
End Sub

2.2.2 De statusregel (Statusbar)

De statusregel hoeft niet zichtbaar te zijn om hem te kunnen gebruiken voor de opslag van een starttijdstip.
De statusregel kan alleen tekst bevatten; een daar opgeslagen tijdstip dient voor gebruik in de ontime instructie eerst geconverteerd te worden naar een tijdwaarde.
Sub M_snb_ontime_starten()
Sheet1.TextBox1.Text = Time

Application.StatusBar = DateAdd("s", 1, Time)
Application.Ontime CDate(Application.StatusBar), "Thisworkbook.M_snb_ontime_starten"
End Sub

Sub M_snb_ontime_sluiten()
Application.Ontime CDate(Application.StatusBar), "Thisworkbook.M_snb_ontime_starten", , False
End Sub

2.2.3 Een 'aangepaste lijst' (customlist)

Sub M_snb_ontime_starten()
Sheet1.TextBox1.Text = Time

If IsDate(Application.GetCustomListContents(Application.CustomListCount)(1)) Then Application.DeleteCustomList Application.CustomListCount
Application.AddCustomList Array(DateAdd("s", 1, Time))
Application.Ontime Application.GetCustomListContents(Application.CustomListCount)(1), "Thisworkbook.M_snb_ontime_starten"
End Sub

Sub M_snb_ontime_sluiten()
Application.Ontime Application.GetCustomListContents(Application.CustomListCount)(1), "Thisworkbook.M_snb_ontime_starten", , False
End Sub

2.3. Een werkboekeigenschap/ werkboek object om het tijdstip te registreren.

2.3.1 een werkboek name

Sub M_snb_ontime_starten()
Sheet1.TextBox1.Text = Time

Thisworkbook.Names.Add "wn_time", DateAdd("s", 1, Time)
Application.Ontime [wn_time], "Thisworkbook.M_snb_ontime_starten"
End Sub

Sub M_snb_ontime_sluiten()
Application.Ontime [wn_time], "Thisworkbook.M_snb_ontime_starten", , False
End Sub

2.3.2 een gebruikerseigenschap (customdocumentproperty)

Een documenteigenschap kan ingesteld worden als een tijdwaarde.
De documenteigenschap hoeft daarom voor gebruik niet naar een tijdwaarde geconverteerd te worden.
De toevoeging van een gebruikerseigenschap met dezelfde naam leidt niet tot vervanging van de gebruikerseigenschap.
Controle of de gebruikerseigenschap al bestaat is daarom noodzakelijk.
Sub M_snb_ontime_starten()
On Error Resume Next
Sheet1.TextBox1.Text = Time

ThisWorkbook.CustomDocumentProperties("c_time") = DateAdd("s", 1, Time)
If Err.Number <> 0 Then ThisWorkbook.CustomDocumentProperties.Add "c_time", 0, 3, DateAdd("s", 1, Time)
Application.Ontime ThisWorkbook.CustomDocumentProperties("c_time"), "Thisworkbook.M_snb_ontime_starten"
End Sub

Sub M_snb_ontime_sluiten()
Application.Ontime ThisWorkbook.CustomDocumentProperties("c_time"), "Thisworkbook.M_snb_ontime_starten", , False
End Sub

2.3.3 een cel in een werkblad

Natuurlijk kan het tijdstip in een cel in een werkblad worden geregistreerd.
Dan moet je echter vervolgens maar zien of de gebruiker die niet verwijdert.
Het lijkt me voor het doel een te kwetsbare plaats.

3 Simultane ontime akties

Excel kan verschillende ontime instructies tegelijkertijd uitvoeren.
Dat geldt niet alleen voor eenmalige akties, maar ook voor zichzelf-aanroepende akties.

3.1 eenmalige simultane akties

Sub M_snb_ontime_starten()
Application.Ontime DateAdd("s", 1, Time), "sheet1.M_equator_1"

Application.Ontime DateAdd("s", 4, Time), "sheet1.M_equator_2"

Application.Ontime DateAdd("s", 10, Time), "sheet1.M_equator_3"
End Sub

Sub M_equator_1()
MsgBox "equator_1"
End Sub

Sub M_equator_2()
MsgBox "equator_2"
End Sub

Sub M_equator_3()
MsgBox "equator_3"
End Sub

3.2 herhalende simultane akties

Dim sn

Sub M_snb_ontime_starten_0()
sn = Array(DateAdd("s", 1, Time), DateAdd("s", 3, Time), DateAdd("s", 6, Time))
Application.Ontime sn(0), "M_snb_ontime_starten_1"
Application.Ontime sn(1), "M_snb_ontime_starten_2"
Application.Ontime sn(2), "M_snb_ontime_starten_3"
End Sub

Sub M_snb_ontime_sluiten_0()
Application.Ontime sn(0), "M_snb_ontime_starten_1", , False
Application.Ontime sn(1), "M_snb_ontime_starten_2", , False
Application.Ontime sn(2), "M_snb_ontime_starten_3", , False
End Sub

Sub M_snb_ontime_starten_1()
ThisWorkbook.Sheets("sheet1").TextBox1.Text = Time

sn(0) = DateAdd("s", 1, Time)
Application.Ontime sn(0), "M_snb_ontime_starten_1"
End Sub

Sub M_snb_ontime_starten_2()
ThisWorkbook.Sheets("sheet1").TextBox2.Text = Time

sn(1) = DateAdd("s", 3, Time)
Application.Ontime sn(1), "M_snb_ontime_starten_2"
End Sub

Sub M_snb_ontime_starten_3()
ThisWorkbook.Sheets("Sheet1").TextBox3.Text = Time

sn(2) = DateAdd("s", 6, Time)
Application.Ontime sn(2), "M_snb_ontime_starten_3"
End Sub