Tom,
Of course.
I am assuming that the original scheduling call is made effectively, but
that something gets in the way of the OnTime call which is never
particularly reliable.
The advantage of the Timer approach I suggested is that even if the Timer
does not fire once, say when you are editing a cell in Excel thereby
temporarily disabling VBA, it still fires at the next correctly at the next
timer interval.
I am sure you could also do this using API calls but whenever I have tried
to implement more effective timers that way I've run into hangs and crashes
when the callback (or whatever it is called) isn't able to fire.
It is still a mystery to me why Excel doesn't have it's own timer control.
Robin Hammond
www.enhanceddatasystems.com
"Tom Ogilvy" wrote in message
...
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]