Using Application.OnTime within a class module

[Andrew]

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

 

[Doug Glancy]

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]

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




















- Show quoted text -

:-(

Okay. Thanks for the input.

Andrew

 

[Chip Pearson]

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,

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




















- Show quoted text -

:-(

Okay. Thanks for the input.

Andrew

 

[Andrew]

You can do something of a work around. Only the procedure actually calledby
OnTime must be in a standard, not a class, module. You can use CallByNameto
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)






:-(

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