If for some reason the routine doesn't run once, it will clearly never run
again, since it is not rescheduled.
Seems like any approach would have difficulties if the code to start it is
never run.
--
Regards,
Tom Ogilvy
"Robin Hammond" wrote in message
...
If for some reason the routine doesn't run once, it will clearly never run
again, since it is not rescheduled. If you would like to try a more
reliable
timer that doesn't open you up to the perils of API calls, have a look
here
for an activex control you can use in Excel:
http://www.enhanceddatasystems.com/E...ExcelTimer.htm
Since this is an activex control it needs to be associated with a form in
some way, so in Excel 2000 or above, you can do the following
1. Create a form called frmTimer, and add the VBATimer control to the form
(you might have to browse to the control under Tools References)
In a general module enter the following:
Sub Initialise()
frmTimer.Show vbModeless
frmTimer.Hide
End Sub
In the form's code, enter this
Private Sub Timer1_Timer()
Debug.Print "timer ran at: " & Now
'run your code here
'how to disable it after a certain time
If Now DateValue(Now) + TimeValue("18:00") Then
Timer1.Enabled = False
'application.ontime call here to restart it at a later time if you
want
to (hopefully)
'or you could put it in an add-in with an auto-open macro so that it
start whenever excel opens
Unload Me
End If
End Sub
Private Sub UserForm_Initialize()
Timer1.Interval = 5000 '5 seconds
Timer1.Enabled = True
End Sub
It might be worth a try. I've never like the OnTime method.
Robin Hammond
www.enhanceddatasystems.com
"Xing Zhou" <Xing wrote in message
...
I have a VBA function in an Excel spreadsheet that is called on a
timer as follows:
Function f()
scheduleNextCallToF
' CATCH ANY ERRORS
On Error GoTo SomethingHappened:
' DO SOME WORK
SomethingHappened:
MsgBox "We've had a problem publishing the curves :
write this down - " & Err.Number & " " & Err.Description & "
" & Err.Source
End Function
Function scheduleNextCallToF()
Private runWhen As Double
runWhen = Now + TimeSerial ( 0 , 0 , 30) 'RUNS EVERY 30 SECS
Application.OnTime earliesttime:=runWhen , prodedu= "f" ,
schedule
:=False
End Function
This function runs fine for the majority of the day, however each
morning for _some_ users, the function stops working.
However no error appears to take place as the MsgBox never appears.
[Note: The DO SOME WORK section takes less than a second to be
performed]