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 |
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 |