Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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] |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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] |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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] |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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] |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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] |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Timer in Excel | Excel Discussion (Misc queries) | |||
Pop up timer for excel | Excel Discussion (Misc queries) | |||
Stopping a Timer / Running a timer simultaneously on Excel | Excel Discussion (Misc queries) | |||
Timer control for Excel VBA | Excel Programming | |||
Excel functions?????????FIRST TIMER | Excel Programming |