X
Xing Zhou
I have a VBA function in an Excel spreadsheet that is called on a
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
R
Robin Hammond
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/ED/Pages/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
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/ED/Pages/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 said: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 , prodedure:= "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]
T
Tom Ogilvy
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
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 said: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/ED/Pages/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 said: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 , prodedure:= "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]
R
Robin Hammond
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
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 said:runIf for some reason the routine doesn't run once, it will clearly never
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 said: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/ED/Pages/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 said: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 , prodedure:= "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]
T
Tom Ogilvy
You are correct that Ontime is not preemptive.
thanks for the further explanation of your concern - that is clearer.
--
Regards,
Tom Ogilvy
thanks for the further explanation of your concern - that is clearer.
--
Regards,
Tom Ogilvy
Robin Hammond said: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 said:runIf for some reason the routine doesn't run once, it will clearly never
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 said: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/ED/Pages/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
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 , prodedure:= "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]