You are right, the ActivateTotal is simply: If Now < reactivateTime Then Application.OnTime EarliestTime:=reactivateTime, Procedure:="ActivateTotal", Schedule:=False If Time <= time_Copydata And Weekday(Now, 2) < 6 Then Application.OnTime EarliestTime:=time_Copydata, Procedure:="Copydata", Schedule:=False If Now < time_new_month_update Then Application.OnTime EarliestTime:=time_new_month_update, Procedure:="new_month_update", Schedule:=False If Now < time_e_check Then Application.OnTime EarliestTime:=time_e_check, Procedure:="e_check", Schedule:=False If Now < time_d_check Then Application.OnTime EarliestTime:=time_d_check, Procedure:="d_check", Schedule:=False Sub Workbook_BeforeClose(cancel As Boolean) The following has been working great for long (The reactivateTime is work in progress) Indeed, my BeforeClose event is full of OnTime cancellations I am evaluating the appropriateness of using If so, that is quite a limitation, because when one works with frequent OnTime processes the chances of that happening are not trivial (it happens to me frequently enough to be a problem) May be the margin has to be greater than one second. What I think happens is that if a cancellation request (of the outstanding OnTime process) is before but close to the EarliestTime the system does not let you. I have experienced problems (memory leakage ?) with processes that are not closed properly, this happens with OnTime procedures not cancelled, that leave an Excel process active in the background that causes issues. I prefer to know why the Error occurs and try to address it properly. Yes, that is what I am doing at the moment.īut I would like to avoid the On Error Resume Next. One way to address this would be to use higher precission timing but, I think, this is not possible because of the limitation of OnTimeĬonsequently, another possibility that I would like to try is to round Now up to 1 second and/or reActivateTime down to 1 second in theĬomparison (I f Now < reactivateTime Then). This can only happen due to issues with the accuracy of measuring Now versus reActivateTime (and then shown as the same in the error handler with accuracy to the second). However, as per the (If Now < reactivateTime Then) comparison, Now is evaluated as lower than reActivateTime This means that Now (measured with infinite precision) is higher than reActivateTime, and thus the previous OnTime has already expired. If this happens it is because there was no outstanding However, sometimes the MsgBox (in the errorhandler) appears. Let me explain further:Įvery time the Workbook_SheetActivate is called I try to first cancel any previous OnTime method. However, it seems enough, because the issue exists. I agree that the difference that you mention may be negligible. ![]() Thank you for your inputs and for your analysis. MsgBox ("Time Now= " & Now & " reactivateTime= " & reactivateTime) ReactivateTime = Now + TimeValue("00:00:30")Īpplication.OnTime EarliestTime:=reactivateTime, Procedure:="ActivateTotal" Sub Workbook_SheetActivate(ByVal Sh As Object)Īpplication.OnTime EarliestTime:=reactivateTime, Procedure:="ActivateTotal", Schedule:=False TimeInMS = Strings.Format(Now, "HH:nn:ss") & "." & Strings.Right(Strings.Format(Timer, "#0.00"), 2)īut then, how can I use the Timer in the OnTime and the checking? It does not seem to work And this following function confirms that: I know the Timer function is more accurate. Other than using an OnError Resume Next (which I prefer to avoid because I want to know what is happening), what other alternative do I have? How do I force the comparison Now < reactivateTime to be done with accuracy to the second? ![]() Since it then shows both the same in the MsgBox, down to second accurancy, it may mean that the comparison happens with values accurate to more than a second. The fact that the ErrorHandler is shown means that Now was actually < reactivateTime. When it does, the MsgBox shows the same time for Now and reactivateTime. The sub at the end occassionaly throws the errorhandler.
0 Comments
Leave a Reply. |