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