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]