PDA

View Full Version : OnTime expertise needed



RyderS2
10-21-2015, 09:33 AM
Hi all... Excel 2010...

I've researched this for hours... and for the life of me I can't see why this is broken... this is standard stuff:

I have a userform application, and I'm running a repeating OnTime routine that fires once per second. This routine is placed in a MODULE... and it does function properly...



Public AlertTime as Double 'or as date

Sub Pulse()
If PulseActive = True Then
AlertTime = Now + TimeValue("00:00:01")
Application.OnTime AlertTime, "Pulse"
'add code here to do fun stuff every second
Else
Application.OnTime AlertTime, "Pulse", , False 'cancel the OnTime
End If
End Sub



the ONLY issue is that Application.OnTime AlertTime, "Pulse", , False causes an error:

Run-time error '1004':
Method 'OnTime' of object' _Application' failed


I also tried the common variant: Application.OnTime EarliestTime:=AlertTime, Procedure:="Pulse", Schedule:=False

Same error.

What in blazes is going on???

Thanks for you kind help.

R

RyderS2
10-21-2015, 10:48 AM
Here is an example sheet that generates the error...

14621


Note that this doesn't even contain a userform... so it can't be related to that.

snb
10-21-2015, 11:32 AM
FYI:

http://www.snb-vba.eu/VBA_Application.OnTime_en.html

RyderS2
10-21-2015, 12:20 PM
Thank you for the link... it's an amazing collection of info... although it seems to verify that I'm doing it right.

Perhaps there is a detail you know of that I'm missing?

RyderS2
10-21-2015, 01:11 PM
FYI:
snb-vba.eu/VBA_Application.OnTime_en.html



In desperation, I did a cut/past job on code examples on the page... and they too don't work... failing for the same reason.

snb
10-21-2015, 01:56 PM
All the code you need in your situation:


Public c00

Sub M_Pulse()
c00 = DateAdd("s", 1, Now)
Application.OnTime c00, "Pulse"
End Sub

Sub M_Pulse_out()
Application.OnTime c00, "Pulse", , False
End Sub

Private Sub CommandButton1_Click()
M_Pulse
End Sub

Private Sub CommandButton2_Click()
M_Pulse_out
End Sub

RyderS2
10-21-2015, 03:11 PM
Thx snb...

I took the appropriate lines, and grafted it on to my existing code... and even changed the variable names to match my original...

This means that the one difference is way c00 is constructed... and one other... I did not set schedule to False INSIDE the called routine itself. I pulled it out as you indicated.

I'm thinking this solved it.

I appreciate your kind assistance!

RyderS2
10-22-2015, 09:30 AM
Ha! Wouldn't you know it... it started failing again... in the exact same way. No clue as to why. It did work for a time... but I never changed anything related to it.

I see that lots of people have encountered this problem, though generally for understandable reasons.

The code this is part of is rather more complex... there's a user form, and it's doing serial communications to a motor controller using the API. OnTimer just seems fragile somehow.