Application.OnTime firers early sometimes?

[zrx1200]

Hello Folks,

I have ten timers that run at given points. 95 % of the time they work fine, but once an awhile they will fire early why is this?
Here is my code. I'm I doing something wrong that allow for the once an awhile early firing?


In Userform1 module

Private Sub Benontimer()
     If Benlatetime = 0 Then
         Benlatetime = Date + CDate(UserForm1.TextBox46) + TimeValue("0:01") 'yellow
         Application.OnTime Benlatetime, "Module1.Benontimer3"
         If Benlatetime2 = 0 Then
          Benlatetime2 = Date + CDate(UserForm1.TextBox46) + TimeValue("0:05") 'red
         Application.OnTime Benlatetime2, "Module1.Benontimer30"
         End If
       End If
End Sub

In module1

Public Sub Benontimer3()
 UserForm1.Benontimer3
End Sub

Public Sub Benontimer30()
 UserForm1.Benontimer30
End Sub

In userform1

Public Sub BenOnTimeClear()
          On Error Resume Next
             Application.OnTime Benlatetime, "Module1.Benontimer3", Schedule:=False
               Benlatetime = 0
             Application.OnTime Benlatetime2, "Module1.Benontimer30", Schedule:=False
               Benlatetime2 = 0
End Sub

In Userform1

Public Sub Benontimer3()
'    UserForm1.Frame14.BorderColor = vbYellow
' UserForm1.Frame14.ForeColor = vbYellow
UserForm1.TextBox46.BackColor = vbYellow
' UserForm1.TextBox41.BackColor = vbYellow
End Sub

Public Sub Benontimer30()
'       UserForm1.Frame14.BorderColor = vbRed
' UserForm1.Frame14.ForeColor = vbRed
'UserForm1.TextBox41.BackColor = vbRed
 UserForm1.TextBox46.BackColor = vbRed
End Sub

 

[joeu2004]

"Firing early" might happen the first time that you set up OnTime. Since you do not "retrigger" OnTime in the event routines (benontimer3 and benontimer30), that is every time that you set up OnTime.

Also, you will notice the "early" time event more with the 1-sec timer than with the 5-sec, unless you measure the latter closely.

The "problem" is: benlatetime and benlatetime2 are type Date. Consequently, VBA sometimes truncates them to the second.

Normally, the system clock updates every 15.625 milliseconds (1/64 sec). So the more into the 1-sec epoch that you are (e.g. 63/64 sec), the sooner the time event will "fire" after triggering.

I prefer to use type Double. I don't know what is in TextBox46. But the following might be more precise:

Dim benontimer3 As Double, benontimer30 As Double
benontimer3 = CDbl(Date) + CDbl(CDate(UserForm1.TextBox46)) + (60 + Timer)/86400

Caveat: That might not work as intended if the current time of day is close to midnight. There is also a race condition when calling Date and Timer and combining the results. Again, the potential problem only occurs close to midnight. If that might be an issue for you, let me know. I can make the formula bullet-proof.

 

[zrx1200]

"Firing early" might happen the first time that you set up OnTime. Since you do not "retrigger" OnTime in the event routines (benontimer3 and benontimer30), that is every time that you set up OnTime.

Also, you will notice the "early" time event more with the 1-sec timer than with the 5-sec, unless you measure the latter closely.


The "problem" is: benlatetime and benlatetime2 are type Date. Consequently, VBA sometimes truncates them to the second.

Normally, the system clock updates every 15.625 milliseconds (1/64 sec). So the more into the 1-sec epoch that you are (e.g. 63/64 sec), the sooner the time event will "fire" after triggering.

I prefer to use type Double. I don't know what is in TextBox46. But the following might be more precise:

Dim benontimer3 As Double, benontimer30 As Double
benontimer3 = CDbl(Date) + CDbl(CDate(UserForm1.TextBox46)) + (60 + Timer)/86400

Caveat: That might not work as intended if the current time of day is close to midnight. There is also a race condition when calling Date and Timer and combining the results. Again, the potential problem only occurs close to midnight. If that might be an issue for you, let me know. I can make the formula bullet-proof.

Thanks for informative reply.

So, just in case this makes a difference to your answer we are dealing with minutes not seconds 0:01 and 0:05 minute intervals.

Textbox46 house a "return" time and once you pass this time by 0:01 textbox46 go yellow and 0:05 it goes red.

What I am seeing at times is it fires before the time is even reached of textbox46. So, for example textbox46 = 1:45 and actual system time is 1:29 it fires. It should not fire till 1:46 going yellow. Then 1:50 going red.

In the mean time I will try your double idea?

 

[joeu2004]

So, just in case this makes a difference to your answer we are dealing with minutes not seconds 0:01 and 0:05 minute intervals.

Sorry for the misdirection. Tired eyes. And the difference between type Date and using type Double for dates is one of my hot buttons. The internal repersentation is the same. But VBA treats them very differently and in subtle ways sometimes.

Oh well, something to keep in mind. But probably totally unrelated to your problem. Klunk!

What I am seeing at times is it fires before the time is even reached of textbox46. So, for example textbox46 = 1:45 and actual system time is 1:29 it fires. It should not fire till 1:46 going yellow. Then 1:50 going red.

How long "before the time ... [in] textbox46"? In particular, 1 or 5 min after triggering the OnTime event? Or some seemingly arbitrary time before the textbox46 time?

I cannot imagine why Date + CDate(textbox46) + "0:01" (or + "0:05") would not work, except within 1 min or 5 min of midnight.

For debugging purposes, I suggest that you add the following code. Use ctrl+G to see the results, and post them here.

Debug.Print Date, UserForm1.TextBox46, CDate(UserForm1.TextBox46), Format(Timevalue("00:01"), "0.00000000000000E+0")

That is 14 zeros after the decimal point.

-----

But I wonder if the problem is not with setting OnTime, but with clearing it in BenOnTimeClear.

If you posted of all of the related code, the problem might likely be: by default, benlatetime and benlatetime2 are local variables in benontimer and beontimeclear. Consequently, benlatetime and benlatetime2 are zero in benontimeclear, and the still-active OnTime events are not getting deleted.

So what appears to be an "early" OnTime event is really left over from a previous execution of benontimer.

The fix: add at least the following statement near the top of the userform1 module, which contains both benontimer and benontimeclear.

Dim benlatetime, benlatetime2

The data type As Variant is implicit. I prefer to enter the data type explicitly.

That should make those variables global to the module.

For debugging purposes, add the following statement to benontimeclear:

Debug.Print Format(benlatetime, "0.00000000000000E+0"), Format(benlatetime2, "0.00000000000000E+0")

 

[zrx1200]

Sorry for the misdirection. Tired eyes. And the difference between type Date and using type Double for dates is one of my hot buttons. The internal repersentation is the same. But VBA treats them very differently and in subtle ways sometimes.

Oh well, something to keep in mind. But probably totally unrelated to your problem. Klunk!




How long "before the time ... [in] textbox46"? In particular, 1 or 5 min after triggering the OnTime event? Or some seemingly arbitrary time before the textbox46 time?

I cannot imagine why Date + CDate(textbox46) + "0:01" (or + "0:05") would not work, except within 1 min or 5 min of midnight.

For debugging purposes, I suggest that you add the following code. Use ctrl+G to see the results, and post them here.

Debug.Print Date, UserForm1.TextBox46, CDate(UserForm1.TextBox46), Format(Timevalue("00:01"), "0.00000000000000E+0")

That is 14 zeros after the decimal point.

-----

But I wonder if the problem is not with setting OnTime, but with clearing it in BenOnTimeClear.

If you posted of all of the related code, the problem might likely be: by default, benlatetime and benlatetime2 are local variables in benontimer and beontimeclear. Consequently, benlatetime and benlatetime2 are zero in benontimeclear, and the still-active OnTime events are not getting deleted.

So what appears to be an "early" OnTime event is really left over from a previous execution of benontimer.

The fix: add at least the following statement near the top of the userform1 module, which contains both benontimer and benontimeclear.

Dim benlatetime, benlatetime2

The data type As Variant is implicit. I prefer to enter the data type explicitly.

That should make those variables global to the module.

For debugging purposes, add the following statement to benontimeclear:

Debug.Print Format(benlatetime, "0.00000000000000E+0"), Format(benlatetime2, "0.00000000000000E+0")

Here are debug findings in order

01/14/2018 6:48 PM 6:48:00 PM 6.94444444444444E-4
0.00000000000000E+0 0.00000000000000E+0

Looks to me zeroing out, yes. But maybe not a good thing?

In userform1 declaration section I had Public ben benlatetime, benlatetime2

I rem out and went variant as suggested.

 

[zrx1200]

Here are debug findings in order

01/14/2018 6:48 PM 6:48:00 PM 6.94444444444444E-4
0.00000000000000E+0 0.00000000000000E+0

Looks to me zeroing out, yes. But maybe not a good thing?

In userform1 declaration section I had Public ben benlatetime, benlatetime2

I rem out and went variant as suggested.

Meant to say In userform1 declaration section I had Public benlatetime, benlatetime2 as double even before we started our track down if this makes any difference.

 

[joeu2004]

In userform1 declaration section I had Public benlatetime, benlatetime2 as double even before we started our track down if this makes any difference.

I would keep As Double. But it should be:

Public benlatetime As Double, benlatetime2 as double

Anyway, that is unrelated to the probem, as I said before.

Here are debug findings in order
[....]
0.00000000000000E+0 0.00000000000000E+0

This demonstrates the crux of the problem: benlatetime and benlatetime2 used in benontimeclear no longer has the values set it benontimer.

Thus, you are failing to cancel timers. The timers that "fire early" are really old timers that do the same action as current timers. You cannot tell the difference.

I'm not sure how that can happen if you are using a Public declaration.

Please copy-and-paste the entire Userform1 module between code and /code tags (left-square-bracket code right-square-bracket), perhaps eliminating irrelevant code (e.g. form-building statements). But the latter is risky; you might eliminate something relevant inadvertently.

Better still for me: upload an example Excel file (redacted) that demonstrates the problem to a file-sharing website (e.g. box.net/files), and post the public/share URL in a response. Test the download URL first, being careful to log out of the file-sharing website. (If you use box.net/files, ignore any preview warnings and just download.)

Some participants object to that because they cannot or will not download external files. Of course, they are not obligated to participate in this discussion. The benefit for me is: the devil is in the subtle details sometimes.

 

[zrx1200]

I would keep As Double. But it should be:

Public benlatetime As Double, benlatetime2 as double

Anyway, that is unrelated to the probem, as I said before.



This demonstrates the crux of the problem: benlatetime and benlatetime2 used in benontimeclear no longer has the values set it benontimer.

Thus, you are failing to cancel timers. The timers that "fire early" are really old timers that do the same action as current timers. You cannot tell the difference.

I'm not sure how that can happen if you are using a Public declaration.

Please copy-and-paste the entire Userform1 module between code and /code tags (left-square-bracket code right-square-bracket), perhaps eliminating irrelevant code (e.g. form-building statements). But the latter is risky; you might eliminate something relevant inadvertently.

Better still for me: upload an example Excel file (redacted) that demonstrates the problem to a file-sharing website (e.g. box.net/files), and post the public/share URL in a response. Test the download URL first, being careful to log out of the file-sharing website. (If you use box.net/files, ignore any preview warnings and just download.)

Some participants object to that because they cannot or will not download external files. Of course, they are not obligated to participate in this discussion. The benefit for me is: the devil is in the subtle details sometimes.

So, bear with me as I try and explain a thought that you "pushed" me too.

I have listbox1 which holds names.
I have Ben which has a textbox2 which holds leave time, a textbox3 which holds return time and a listbox22 which excepts listbox1 names on transfer.
I have command buttons (name should give an idea what happens) Transfer, Clear, Remove, Remove/Post,Append
So, normally you would transfer names over to bens listbox from listbox1 and the transfer command button starts the timer. The timer watches the return value textbox3 and when it reach 1 minute past time textbox goes yellow, 5 min past red.

So, now the "push" I noticed that I may not be considering that when I append, remove, remove/post I don't reset timers and the values for leave and return will have now changed. Is this maybe a concern?

However I do a lot of appends, removes and remove/posts and never noticed a rhythm in error? As I said it seems too work flawless 95 % of the time.

I've been working on a log that catches the time and color of box comparing times then log a count so I may refer to how much this issue is actually happening.

But, also too my way of thinking when I transfer and clear these buttons to work on no "modified" arrive time and return. They start and stop timers Or still maybe not like you said.

 

[zrx1200]

Just did some experimenting...

Public Sub BenOnTimeClear()
          On Error Resume Next
          Debug.Print Format(benlatetime, "0.00000000000000E+0"), Format(benlatetime2, "0.00000000000000E+0")
             Application.OnTime benlatetime, "Module1.Benontimer3", Schedule:=False
               benlatetime = 0
             Application.OnTime benlatetime2, "Module1.Benontimer30", Schedule:=False
               benlatetime2 = 0
               Debug.Print Format(benlatetime, "0.00000000000000E+0"), Format(benlatetime2, "0.00000000000000E+0")
'Debug.Print Format(benlatetime2, "0.00000000000000E+0"), Format(benlatetime2, "0.00000000000000E+0")
End Sub

Would I not be correct in saying no wonder benlatetime and benlatetime2 are zero because in above benontimeclear i'm tell it that.

So I added the debug at the front and it has figures.

01/15/2018 9:32 PM 9:32:00 PM 6.94444444444444E-4
4.31158979166667E+4 4.31159006944444E+4 first debug
0.00000000000000E+0 0.00000000000000E+0 last debug

Just chewing........

 

[zrx1200]

With some more chewing... I believe you are close in saying the timers at times are not getting canceled.

Example, when I have a crash, one which happens now and then which is a type mismatch which I need to track down as well.

So, it looks like I need to track down the command buttons and make sure when new time are issued the timer cancels and re initializes again.

Stay tuned ...... going to take awhile LOL