Ontime Calling Class Method

lswanson · Apr 28th 2006

Hi, sorry if this has already been dealt with, but I could not find anything about it with a search.

I am trying to use the application.ontime procedure to run a class method, but having a little trouble with what to use as my procedure name. Do I have to include the name of the class, or the name of the instantiation of the class, or what? Here is kind of what I have in the "myClass" class module:

Code

Option Explicit


Private WithEvents m_cmdButton As MSForms.CommandButton


Private m_iTimer As Double


Private Sub m_cmdButton_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)


'   Set the time with the long delay
    m_iTimer = Now + TimeSerial(0, 0, I_TIMER_DELAY_LONG)


'   Start the timer
    Call Application.OnTime(earliesttime:=m_iTimer, procedure:="ClickAgain", schedule:=True)
End Sub


Private Sub ClickAgain()


    Call MsgBox("ClickAgain successfully called.", , "Boo yeah!")
End Sub

Display More

I have checked and the MouseDown event is being registered: it will work if I use a procedure in a standard module rather than the class method"ClickAgain". (I am only including the code relevant to the "ontime" procedure. My actual class has other methods to deal with setting the control source for the command button & everything.) So I am kind of stumped, any help you guys could offer on this point would be very helpful, thanks.

**iwrk4dedpr** · Apr 28th 2006

Re: Ontime Calling Class Method

lswanson,

Hmmm. I'm not a big "CLASS" person but these are the thoughts that I have.

1. The routine you're trying to call is a private routine. Hence it shouldn't be available outside the object.

2. What is the reason behind this need to run a class procedure on a time frame. I'm thinking that what you're going to have to do. Is the following.

In the regular module sheets you may need to do the following.
1. Create a global variable of the object.
2. Create a small simple routine something like the following

Code

Dim gMyObject as ClassIDHere


Sub UpdateObject()
    gMyObject.ClickAgain
    Call Application.OnTime(earliesttime:=m_iTimer, procedure:="UpdateObject", schedule:=True)       
end Sub

Of course this is just a thought.

Bob Phillips · Apr 28th 2006

Re: Ontime Calling Class Method

Why would you want to fire Ontime based upon a mousedown event? That notwithstanding, just put the ClickAgain procedure in a standard class module.

You cannot call class methods in this way. You have to instantiate an instance of that class, and then call it via that instance. But that doesn't apply to events, as events are fired when the evnt happens. I suppose you could create a class method that calls that event, faking the parameters, but why would you?

lswanson · Apr 28th 2006

Re: Ontime Calling Class Method

What I am trying to do is have a command button work in such a way that after you click on it, every second after that it checks to see if the button is still being held down & if it is, then treat that as a new click on the button. What I want is a button that can tell when it is being clicked & "held down" & therefore run the response to being clicked repeatedly. If there is any way to do this without using the ontime procedure or that is just completely different from how I am doing, please let me know.

Otherwise, that part is not what I am having trouble with, so let us remove all references to the command button & the mousedown event & assume for the sake of argument that this is the code for the "MyClass" class:

Code

Option Explicit
 
Private m_iTimer As Double 
 
Private Sub SetTimer(iTimerDelay as Integer) 
     
     '   Set the time with the long delay
    m_iTimer = Now + TimeSerial(0, 0, iTimerDelay) 
     
     '   Start the timer
    Call Application.OnTime(earliesttime:=m_iTimer, procedure:="TimedEvent", schedule:=True) 
End Sub 
 
Sub TimedEvent() 
     
    Call MsgBox("Timer event successfully called.", , "Boo yeah!") 
End Sub

Display More

& then in a standard module I have:

Code

Option Explicit


Public MyClassInstance As MyClass


Sub UseClassTimer()


    Call MyClassInstance.SetTimer(10)
End Sub

Display More

I understand that the simple solution would be to put the timer event in a standard module, but that would greatly complicate the code of the timer event procedure itself as it needs access to private members of the class. Plus, I am stubborn & a big fan of classes & would really rather use a class member. Is there simply no way at all to do this with the ontime event? Would this work using the windows API timer object, since it uses the "AddressOf" function? I think I would rather use the ontime method since apparently the API timers do not work with older versions of Excel & backwards compatibility is really important to this project.

I guess one option would be to have the timer event in a standard module & pass it the instance of the class that set the timer as a parameter. Does anybody know anything about how to set parameters with the procedure name string you pass to the ontime procedure? Actually, any information (or links to sites with good information) about referring to procedures with strings would be very helpful.

Well, I have some ideas how I might be able to work around this problem that I am going to tinker with... but it sure would be nice to not have to deal with those & just be able to call the method directly with the ontime method.

Thanks

P.S. Sorry about the lengthiness of my posts, but I try to give a really detailed account of what I want to accomplish, how I have been trying to accomplish this, what problems I have, & what I have so far tried to overcome these problems. Avoiding vagueness is the goal, because I know that it is so much harder to help somebody when you are not even sure what they are trying to do.

Bob Phillips · Apr 29th 2006

Re: Ontime Calling Class Method

Quote from lswanson

What I am trying to do is have a command button work in such a way that after you click on it, every second after that it checks to see if the button is still being held down & if it is, then treat that as a new click on the button. What I want is a button that can tell when it is being clicked & "held down" & therefore run the response to being clicked repeatedly. If there is any way to do this without using the ontime procedure or that is just completely different from how I am doing, please let me know.

How about setting a boolean in MouseDown, and unset in MouseUp, and test that flag in the button code?

Quote from lswanson

I understand that the simple solution would be to put the timer event in a standard module, but that would greatly complicate the code of the timer event procedure itself as it needs access to private members of the class. Plus, I am stubborn & a big fan of classes & would really rather use a class member.

So expose the private members via a public property and let the Ontime procedure call into that instance to get the values, that is how classes work.

Quote from lswanson

Is there simply no way at all to do this with the ontime event? Would this work using the windows API timer object, since it uses the "AddressOf" function? I think I would rather use the ontime method since apparently the API timers do not work with older versions of Excel & backwards compatibility is really important to this project.

I don't really know whether the API timers would work as I haven't properly understood what you are trying to do, but AddressOf can be emulated in Excel 97 using the Getz/Gilbert method.

Quote from lswanson

I guess one option would be to have the timer event in a standard module & pass it the instance of the class that set the timer as a parameter. Does anybody know anything about how to set parameters with the procedure name string you pass to the ontime procedure? Actually, any information (or links to sites with good information) about referring to procedures with strings would be very helpful.

You are joking aren't you? Call a standard module procedure from a class passing it a reference to itself so it can call back into itself?

lswanson · May 8th 2006

Re: Ontime Calling Class Method

Quote from Bob Phillips

How about setting a boolean in MouseDown, and unset in MouseUp, and test that flag in the button code?

Would I not still need to use the "ontime" procedure to check the status of the flag every time interval?

[HR]-[/HR]

Hrm, well... you guys have given me a lot to think about. I am going to fiddle with this some more, but I think I am on my way to a solution to my problem. Sorry that I could not better explain what I was trying to do. When I get it working, I will post it here & hopefully that will clear things up some. Thanks for all of the help.

lswanson · May 9th 2006

Re: Ontime Calling Class Method

So I have gotten it to work. Sort of. The only problem remaining is that I cannot call the TimerEvent procedure less than a second apart.

Here is my worksheet:

Ontime Calling Class Method

Participate now!

Share