excel - Scheduled VBA task and 'Application.OnTime' -

i have following vba code working well. it's calling vba sub without trouble:

public sub autoprintmissinghistoric()     dim qdf dao.querydef     dim rcs dao.recordset     dim db dao.database     dim j integer     dim flag boolean     dim long     dim value_start, value_end string     dim tmp date     dim wbriskedge workbook     dim wsaccueil worksheet     dim wshistoric worksheet      set wbriskedge = workbooks(strwbriskedge)     set wsaccueil = wbriskedge.worksheets(strwsaccueil)     set wshistoric = wbriskedge.worksheets(strwshistoricmissing)     if fisttime = true         call initialisation.cleantab     else         fisttime = true         call initialisation.initialisation     end if     vdelay = 5     cpt = cpt + 1     set db = dbengine.opendatabase(strdb)     set qdf = db.querydefs("get_missing_fixings")     if cpt <= wsaccueil.range(manuallistletter & "1").end(xldown).row         application.statusbar = wsaccueil.cells(cpt, manuallistletter).text         qdf.parameters("arg1") = wsaccueil.cells(cpt, manuallistletter).value         set rcs = qdf.openrecordset         j = 0         = 1         flag = false         if not rcs.eof             rcs.movelast             rcs.movefirst             while not rcs.eof                 j = 0                 while j < rcs.fields.count                     if flag = false                         cells(i, j + 1)                             if .value = ""                                 .value = rcs(j).name                                 .font.bold = true                                 .horizontalalignment = xlcenter                                 .verticalalignment = xlbottom                             end if                         end                     else                         cells(i, j + 1).value = rcs(j).value                     end if                     j = j + 1                 wend                 if flag = false                     flag = true                 end if                 = + 1                 rcs.movenext             wend             call changeminmax(rcs.recordcount, cellmindate, cellmaxdate, wshistoric)             call parseparameters             call setreutersfunction         end if         rcs.close         qdf.close         db.close         wshistoric.calculate         application.statusbar = wsaccueil.cells(cpt, manuallistletter).text & " - next function: findmissingvalue.autofindmissingvalue"         stocall = "findmissingvalue.autofindmissingvalue"         mtimegt = time + timevalue("00:00:" & vdelay)         application.ontime mtimegt, stocall     end if end sub 

i put execution of process in scheduled task. apparently code not executed: findmissingvalue.autofindmissingvalue sub not called because excel closes.

i think it's because of application.ontime mtimegt, stocall... reason?

here you've code of findmissingvalue.autofindmissingvalue

sub autofindmissingvalue()     dim wbriskedge workbook     dim wsaccueil worksheet     dim wshistoric worksheet     dim i, nbresult long      set wbriskedge = workbooks(strwbriskedge)     set wsaccueil = wbriskedge.worksheets(strwsaccueil)     set wshistoric = wbriskedge.worksheets(strwshistoricmissing)     if left(wshistoric.range(reutersformula).text, 13) "retrieving...*" = true         stocall = "findmissingvalue.autofindmissingvalue"         mtimegt = time + timevalue("00:00:05")         application.ontime mtimegt, stocall         exit sub     end if     = worksheetfunction.counta(columns(datecolumn & ":" & datecolumn))     if worksheetfunction.counta(columns(columnresearchvresult & ":" & columnresearchvresult)) > 0         wshistoric.range(firstcellresearchvresult & ":" & columnresearchvresult & worksheetfunction.counta(columns(columnresearchvresult & ":" & columnresearchvresult))).clearcontents     end if     nbresult = wshistoric.range(firstresult).end(xldown).row     wshistoric.range(columnresearchvresult & lineresearchvresult - 1).value = "results"     if worksheetfunction.counta(columns(datecolumn & ":" & datecolumn)) > 1         wshistoric.range(firstcellresearchvresult & ":" & columnresearchvresult & i).formulalocal = "=recherchev($" & datecolumn & "$" & lineresearchvresult & ":$" & datecolumn & "$" & & ";" & firstlockresult & ":$" & valueresultcolumn & "$" & nbresult & ";2;0)"     end if     application.statusbar = wsaccueil.cells(cpt, manuallistletter).text & " - next function: findmissingvalue.autoputresultindb"     stocall = "findmissingvalue.autoputresultindb"     mtimegt = time + timevalue("00:00:01")     application.ontime mtimegt, stocall end sub 

the application.ontime part right , findmissingvalue.autofindmissingvalue should called without problem (after 5 seconds). might happen that, during 5 seconds period, code continues running, goes autoprintmissinghistoric called from, , workbook might closed before these 5 seconds have passed (although, depending upon exact conditions, function should called despite workbook closed).

you can either reduce waiting period (vdelay = 1, example) or call function directly (call findmissingvalue.autofindmissingvalue). actually, not sure why calling function relying on application.ontime; using fine "starting process" (e.g., "i want macro executed every day @ 00:00"), might drive "messy situations" in case of being used on regular basis.

if nothing of works, please, provide code of findmissingvalue.autofindmissingvalue take @ it.

note: after further tests/discussions, can confirm behavior of ontime under these specific conditions "too irregular". should come different approach allow waiting period need or, in case of having rely on ontime, intensive trial-and-error make sure behaviour under control. function expected called once (opening spreadsheet @ time, example) , have pay lots of attention when using on different contexts (like one: calling inside function).