Excel VBA - Timer
 

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]

Excel VBA - Timer
 

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]

Excel VBA - Timer
 

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]

Excel VBA - Timer
 

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]

Excel VBA - Timer
 

You are correct that Ontime is not preemptive.

thanks for the further explanation of your concern - that is clearer.

--
Regards,
Tom Ogilvy

"Robin Hammond" wrote in message
...
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]