Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Application.OnTime within a class module
Hi all
I'm trying to create a class module within Excel to encapsulate a timer function. The theory is that you can call a method to start a timer, passing it a duration and a procedure to call when that duration expires. You can also specify that the timer should loop (or not) if required. There should also be a method to stop the timer. However, I keep hitting a brick wall, whether I use Application.OnTime as the core method for the functionality or I use a call to the Win API SetTimer function. The problem is that in order to entirely encapsulate the functionality, I want to have an internal procedure called when the timer expires, rather than a procedure in a standard module. My code (pasted below) works if I move Sub DoCall to a standard module, but that rather defeats the object of encapsulation. Can anyone help?! One final note: There are additional property procedures, not pasted below, which allow the user to set the properties for the proc to call, the duration etc as separate properties rather than as arguments of the StartTimer method, which is why those arguments are optional and there is code to check if they were supplied. Many thanks in advance Andrew Richards Public TimerOn As Boolean Private strProcedureName As String Private sngSeconds As Single Private blnRepeat As Boolean Public Event TimerInterval() Sub StartTimer(Optional Seconds As Single, _ Optional ProcedureName As String) ' Turn on the timer TimerOn = True ' If arguments have been passed, use them If Seconds 0 Then sngSeconds = Seconds End If If Len(ProcedureName) 0 Then strProcedureName = ProcedureName End If ' Check in case there were no values as ' either parameters or properties If Len(strProcedureName) = 0 Or sngSeconds = 0 Then Err.Raise vbObjectError + 1, "clsTimer_StartTimer", _ "One or more of the required properties was missing. " _ & "You must set both a timer duration and a " _ & "procedure name to be called." Exit Sub End If ' If still running, all OK. Call the internal procedure ' which will call the external proc as well as handling looping Application.OnTime Now() + TimeSerial(0, 0, sngSeconds), "DoCall" End Sub '----------------------------------------------------------------------------- Sub StopTimer() ' Turn off the timer TimerOn = False End Sub '----------------------------------------------------------------------------- Public Sub DoCall() ' Check if timer enabled If TimerOn Then ' If so, run the supplied Proc name Application.Run strProcedureName ' Raise an event RaiseEvent TimerInterval ' If user wants to loop, start again If blnRepeat Then StartTimer End If End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Application.OnTime within a class module
Andrew,
I google grouped "ontime excel in class module" and what you've found seems to be a limitation of OnTime. There were a couple of posts where Chip Pearson responded to similar questions, saying that the OnTime procedure has to be in a standard module. hth, Doug "Andrew" wrote in message oups.com... Hi all I'm trying to create a class module within Excel to encapsulate a timer function. The theory is that you can call a method to start a timer, passing it a duration and a procedure to call when that duration expires. You can also specify that the timer should loop (or not) if required. There should also be a method to stop the timer. However, I keep hitting a brick wall, whether I use Application.OnTime as the core method for the functionality or I use a call to the Win API SetTimer function. The problem is that in order to entirely encapsulate the functionality, I want to have an internal procedure called when the timer expires, rather than a procedure in a standard module. My code (pasted below) works if I move Sub DoCall to a standard module, but that rather defeats the object of encapsulation. Can anyone help?! One final note: There are additional property procedures, not pasted below, which allow the user to set the properties for the proc to call, the duration etc as separate properties rather than as arguments of the StartTimer method, which is why those arguments are optional and there is code to check if they were supplied. Many thanks in advance Andrew Richards Public TimerOn As Boolean Private strProcedureName As String Private sngSeconds As Single Private blnRepeat As Boolean Public Event TimerInterval() Sub StartTimer(Optional Seconds As Single, _ Optional ProcedureName As String) ' Turn on the timer TimerOn = True ' If arguments have been passed, use them If Seconds 0 Then sngSeconds = Seconds End If If Len(ProcedureName) 0 Then strProcedureName = ProcedureName End If ' Check in case there were no values as ' either parameters or properties If Len(strProcedureName) = 0 Or sngSeconds = 0 Then Err.Raise vbObjectError + 1, "clsTimer_StartTimer", _ "One or more of the required properties was missing. " _ & "You must set both a timer duration and a " _ & "procedure name to be called." Exit Sub End If ' If still running, all OK. Call the internal procedure ' which will call the external proc as well as handling looping Application.OnTime Now() + TimeSerial(0, 0, sngSeconds), "DoCall" End Sub '----------------------------------------------------------------------------- Sub StopTimer() ' Turn off the timer TimerOn = False End Sub '----------------------------------------------------------------------------- Public Sub DoCall() ' Check if timer enabled If TimerOn Then ' If so, run the supplied Proc name Application.Run strProcedureName ' Raise an event RaiseEvent TimerInterval ' If user wants to loop, start again If blnRepeat Then StartTimer End If End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Application.OnTime within a class module
On 5 Oct, 00:46, "Doug Glancy" wrote:
Andrew, I google grouped "ontime excel in class module" and what you've found seems to be a limitation of OnTime. There were a couple of posts where Chip Pearson responded to similar questions, saying that the OnTime procedure has to be in a standard module. hth, Doug "Andrew" wrote in message oups.com... Hi all I'm trying to create a class module within Excel to encapsulate a timer function. The theory is that you can call a method to start a timer, passing it a duration and a procedure to call when that duration expires. You can also specify that the timer should loop (or not) if required. There should also be a method to stop the timer. However, I keep hitting a brick wall, whether I use Application.OnTime as the core method for the functionality or I use a call to the Win API SetTimer function. The problem is that in order to entirely encapsulate the functionality, I want to have an internal procedure called when the timer expires, rather than a procedure in a standard module. My code (pasted below) works if I move Sub DoCall to a standard module, but that rather defeats the object of encapsulation. Can anyone help?! One final note: There are additional property procedures, not pasted below, which allow the user to set the properties for the proc to call, the duration etc as separate properties rather than as arguments of the StartTimer method, which is why those arguments are optional and there is code to check if they were supplied. Many thanks in advance Andrew Richards Public TimerOn As Boolean Private strProcedureName As String Private sngSeconds As Single Private blnRepeat As Boolean Public Event TimerInterval() Sub StartTimer(Optional Seconds As Single, _ Optional ProcedureName As String) ' Turn on the timer TimerOn = True ' If arguments have been passed, use them If Seconds 0 Then sngSeconds = Seconds End If If Len(ProcedureName) 0 Then strProcedureName = ProcedureName End If ' Check in case there were no values as ' either parameters or properties If Len(strProcedureName) = 0 Or sngSeconds = 0 Then Err.Raise vbObjectError + 1, "clsTimer_StartTimer", _ "One or more of the required properties was missing. " _ & "You must set both a timer duration and a " _ & "procedure name to be called." Exit Sub End If ' If still running, all OK. Call the internal procedure ' which will call the external proc as well as handling looping Application.OnTime Now() + TimeSerial(0, 0, sngSeconds), "DoCall" End Sub '--------------------------------------------------------------------------*--- Sub StopTimer() ' Turn off the timer TimerOn = False End Sub '--------------------------------------------------------------------------*--- Public Sub DoCall() ' Check if timer enabled If TimerOn Then ' If so, run the supplied Proc name Application.Run strProcedureName ' Raise an event RaiseEvent TimerInterval ' If user wants to loop, start again If blnRepeat Then StartTimer End If End Sub- Hide quoted text - - Show quoted text - :-( Okay. Thanks for the input. Andrew |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Application.OnTime within a class module
You can do something of a work around. Only the procedure actually called by
OnTime must be in a standard, not a class, module. You can use CallByName to call a particular method of a specified instance of an class object. For example, in a standard code module, use Private OnTimeObj As Object Private OnTimeMethod As String Private OnTimeRunWhen As Double Sub AAA() Set OnTimeObj = New Class1 OnTimeMethod = "SomeMethod" OnTimeRunWhen = Now + TimeSerial(0, 0, 3) ' run in 3 seconds Application.OnTime OnTimeRunWhen, "RunMethodOfObject", , True End Sub Sub RunMethodOfObject() CallByName OnTimeObj, OnTimeMethod, VbMethod End Sub The procedure AAA sets up the Object and Method to be run when the OnTime pops. RunMethodOfObject uses CallByName to call the specified method of the specified object. You could wrap this up further into a master Timer class. In a standard code module, use '''''''''''''''''''''''''''''''''''''''''''''''''' '''''' ' Module1 '''''''''''''''''''''''''''''''''''''''''''''''''' '''''' Dim TimerClass As CTimerClass Dim ObjectClass As CObjectClass Sub BBB() If TimerClass Is Nothing Then Set TimerClass = New CTimerClass End If If ObjectClass Is Nothing Then ' CObjectClass contains the method to run Set ObjectClass = New CObjectClass End If With TimerClass Set .ObjectClass = ObjectClass .RunOnTime TimeFromNow:=TimeSerial(0, 0, 3), MethodName:="MethodInObjectClass" End With End Sub Sub GlobalCallByName() With TimerClass CallByName .ObjectClass, .pMethodName, VbMethod End With End Sub In a class name CTimerClass '''''''''''''''''''''''''''''''''''''''''''''''''' '''''' ' CTimerClass '''''''''''''''''''''''''''''''''''''''''''''''''' '''''' Private pRunWhen As Double Public pMethodName As String Public ObjectClass As Object Sub RunOnTime(TimeFromNow As Double, MethodName As String) If ObjectClass Is Nothing Then ' create instance of Class containing procedure to run Set ObjectClass = New CObjectClass End If pMethodName = MethodName pRunWhen = Now + TimeFromNow Application.OnTime pRunWhen, "GlobalCallByName", , True End Sub And finally in the class that cotnaisn the method you want to run, '''''''''''''''''''''''''''''''''''''''''''''''''' '''''' ' CObjectClass '''''''''''''''''''''''''''''''''''''''''''''''''' '''''' Public Sub MethodInObjectClass() MsgBox "Method Of ObjectClass" End Sub You can't use the Window SetTimer API in a class because you can't use AddressOf to get the address of a method in an object. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting www.cpearson.com (email on the web site) "Andrew" wrote in message ps.com... On 5 Oct, 00:46, "Doug Glancy" wrote: Andrew, I google grouped "ontime excel in class module" and what you've found seems to be a limitation of OnTime. There were a couple of posts where Chip Pearson responded to similar questions, saying that the OnTime procedure has to be in a standard module. hth, Doug "Andrew" wrote in message oups.com... Hi all I'm trying to create a class module within Excel to encapsulate a timer function. The theory is that you can call a method to start a timer, passing it a duration and a procedure to call when that duration expires. You can also specify that the timer should loop (or not) if required. There should also be a method to stop the timer. However, I keep hitting a brick wall, whether I use Application.OnTime as the core method for the functionality or I use a call to the Win API SetTimer function. The problem is that in order to entirely encapsulate the functionality, I want to have an internal procedure called when the timer expires, rather than a procedure in a standard module. My code (pasted below) works if I move Sub DoCall to a standard module, but that rather defeats the object of encapsulation. Can anyone help?! One final note: There are additional property procedures, not pasted below, which allow the user to set the properties for the proc to call, the duration etc as separate properties rather than as arguments of the StartTimer method, which is why those arguments are optional and there is code to check if they were supplied. Many thanks in advance Andrew Richards Public TimerOn As Boolean Private strProcedureName As String Private sngSeconds As Single Private blnRepeat As Boolean Public Event TimerInterval() Sub StartTimer(Optional Seconds As Single, _ Optional ProcedureName As String) ' Turn on the timer TimerOn = True ' If arguments have been passed, use them If Seconds 0 Then sngSeconds = Seconds End If If Len(ProcedureName) 0 Then strProcedureName = ProcedureName End If ' Check in case there were no values as ' either parameters or properties If Len(strProcedureName) = 0 Or sngSeconds = 0 Then Err.Raise vbObjectError + 1, "clsTimer_StartTimer", _ "One or more of the required properties was missing. " _ & "You must set both a timer duration and a " _ & "procedure name to be called." Exit Sub End If ' If still running, all OK. Call the internal procedure ' which will call the external proc as well as handling looping Application.OnTime Now() + TimeSerial(0, 0, sngSeconds), "DoCall" End Sub '--------------------------------------------------------------------------*--- Sub StopTimer() ' Turn off the timer TimerOn = False End Sub '--------------------------------------------------------------------------*--- Public Sub DoCall() ' Check if timer enabled If TimerOn Then ' If so, run the supplied Proc name Application.Run strProcedureName ' Raise an event RaiseEvent TimerInterval ' If user wants to loop, start again If blnRepeat Then StartTimer End If End Sub- Hide quoted text - - Show quoted text - :-( Okay. Thanks for the input. Andrew |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Application.OnTime within a class module
On Oct 6, 1:27 am, "Chip Pearson" wrote:
You can do something of a work around. Only the procedure actually called by OnTime must be in a standard, not a class, module. You can use CallByName to call a particular method of a specified instance of an class object. For example, in a standard code module, use Private OnTimeObj As Object Private OnTimeMethod As String Private OnTimeRunWhen As Double Sub AAA() Set OnTimeObj = New Class1 OnTimeMethod = "SomeMethod" OnTimeRunWhen = Now + TimeSerial(0, 0, 3) ' run in 3 seconds Application.OnTime OnTimeRunWhen, "RunMethodOfObject", , True End Sub Sub RunMethodOfObject() CallByName OnTimeObj, OnTimeMethod, VbMethod End Sub The procedure AAA sets up the Object and Method to be run when the OnTime pops. RunMethodOfObject uses CallByName to call the specified method of the specified object. You could wrap this up further into a master Timer class. In a standard code module, use '''''''''''''''''''''''''''''''''''''''''''''''''' '''''' ' Module1 '''''''''''''''''''''''''''''''''''''''''''''''''' '''''' Dim TimerClass As CTimerClass Dim ObjectClass As CObjectClass Sub BBB() If TimerClass Is Nothing Then Set TimerClass = New CTimerClass End If If ObjectClass Is Nothing Then ' CObjectClass contains the method to run Set ObjectClass = New CObjectClass End If With TimerClass Set .ObjectClass = ObjectClass .RunOnTime TimeFromNow:=TimeSerial(0, 0, 3), MethodName:="MethodInObjectClass" End With End Sub Sub GlobalCallByName() With TimerClass CallByName .ObjectClass, .pMethodName, VbMethod End With End Sub In a class name CTimerClass '''''''''''''''''''''''''''''''''''''''''''''''''' '''''' ' CTimerClass '''''''''''''''''''''''''''''''''''''''''''''''''' '''''' Private pRunWhen As Double Public pMethodName As String Public ObjectClass As Object Sub RunOnTime(TimeFromNow As Double, MethodName As String) If ObjectClass Is Nothing Then ' create instance of Class containing procedure to run Set ObjectClass = New CObjectClass End If pMethodName = MethodName pRunWhen = Now + TimeFromNow Application.OnTime pRunWhen, "GlobalCallByName", , True End Sub And finally in the class that cotnaisn the method you want to run, '''''''''''''''''''''''''''''''''''''''''''''''''' '''''' ' CObjectClass '''''''''''''''''''''''''''''''''''''''''''''''''' '''''' Public Sub MethodInObjectClass() MsgBox "Method Of ObjectClass" End Sub You can't use the Window SetTimer API in a class because you can't use AddressOf to get the address of a method in an object. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consultingwww.cpearson.com (email on the web site) "Andrew" wrote in message ps.com... On 5 Oct, 00:46, "Doug Glancy" wrote: Andrew, I google grouped "ontime excel in class module" and what you've found seems to be a limitation of OnTime. There were a couple of posts where Chip Pearson responded to similar questions, saying that the OnTime procedure has to be in a standard module. hth, Doug "Andrew" wrote in message roups.com... Hi all I'm trying to create a class module within Excel to encapsulate a timer function. The theory is that you can call a method to start a timer, passing it a duration and a procedure to call when that duration expires. You can also specify that the timer should loop (or not) if required. There should also be a method to stop the timer. However, I keep hitting a brick wall, whether I use Application.OnTime as the core method for the functionality or I use a call to the Win API SetTimer function. The problem is that in order to entirely encapsulate the functionality, I want to have an internal procedure called when the timer expires, rather than a procedure in a standard module. My code (pasted below) works if I move Sub DoCall to a standard module, but that rather defeats the object of encapsulation. Can anyone help?! One final note: There are additional property procedures, not pasted below, which allow the user to set the properties for the proc to call, the duration etc as separate properties rather than as arguments of the StartTimer method, which is why those arguments are optional and there is code to check if they were supplied. Many thanks in advance Andrew Richards Public TimerOn As Boolean Private strProcedureName As String Private sngSeconds As Single Private blnRepeat As Boolean Public Event TimerInterval() Sub StartTimer(Optional Seconds As Single, _ Optional ProcedureName As String) ' Turn on the timer TimerOn = True ' If arguments have been passed, use them If Seconds 0 Then sngSeconds = Seconds End If If Len(ProcedureName) 0 Then strProcedureName = ProcedureName End If ' Check in case there were no values as ' either parameters or properties If Len(strProcedureName) = 0 Or sngSeconds = 0 Then Err.Raise vbObjectError + 1, "clsTimer_StartTimer", _ "One or more of the required properties was missing. " _ & "You must set both a timer duration and a " _ & "procedure name to be called." Exit Sub End If ' If still running, all OK. Call the internal procedure ' which will call the external proc as well as handling looping Application.OnTime Now() + TimeSerial(0, 0, sngSeconds), "DoCall" End Sub '--------------------------------------------------------------------------**--- Sub StopTimer() ' Turn off the timer TimerOn = False End Sub '--------------------------------------------------------------------------**--- Public Sub DoCall() ' Check if timer enabled If TimerOn Then ' If so, run the supplied Proc name Application.Run strProcedureName ' Raise an event RaiseEvent TimerInterval ' If user wants to loop, start again If blnRepeat Then StartTimer End If End Sub- Hide quoted text - - Show quoted text - :-( Okay. Thanks for the input. Andrew- Hide quoted text - - Show quoted text - Thanks for that. Been away for a little while so not had time to try it out, but I'll take a look later today. Thanks again Andrew |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help with Application.OnTime | Excel Programming | |||
Application Event in Class Module | Excel Programming | |||
application.ontime | Excel Programming | |||
Application.OnTIme | Excel Programming | |||
Variable from a sheet module in a class module in XL XP | Excel Programming |