Tom Ogilvy - Timer help

[Kragelund]

Tom, you posted this brilliant piece of code (below) in a response to a
question with the title " Do while loop using time as the counter". I found
it (potentially) extremely useful and intricate. So much so that I don't know
how to adapt it to my own purpose, which is to initiate a validation
procedure on a sheet at user given intervals, for instance every two minutes.
Can you (or somebody equally gifted) explain how I modify the code to loop
every e.g. 120 seconds until the user specifically orders the procedure to
stop?

Your help would be greatly appreciated!

Henrik



Public Declare Function SetTimer Lib "user32" ( _
ByVal hwnd As Long, ByVal nidevent As Long, _
ByVal uElapse As Long, ByVal lpTimerFunc As Long) As Long
Public Declare Function KillTimer Lib "user32" ( _
ByVal hwnd As Long, ByVal nidevent As Long) As Long

Public timerid As Long
Public timerseconds As Single
Public Cnt As Long

Sub StartTimer()
timerseconds = 1 'how often to "pop"the timer
Cnt = 0
timerid = SetTimer(0&, 0&, timerseconds * 1000&, _
AddressOf Timerproc)
End Sub

Sub endtimer()
On Error Resume Next
KillTimer 0&, timerid
End Sub

Sub Timerproc(ByVal hwnd As Long, ByVal umsg As Long, _
ByVal nidevent As Long, ByVal dwtimer As Long)
Range("A1").Value = Cnt + 1
Beep
Cnt = Cnt + 1
If Cnt < 10 Then Exit Sub

endtimer
End Sub

write your log in the timerproc

 

[Bob Phillips]

I think you want OnTime. See http://www.cpearson.com/excel/ontime.htm

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

 

[Kragelund]

Bob,

You're the man indeed - and exactly right too. I needed OnTime and it works
brilliantly so far. I recognise some of the code that Tom included in his
earlier reply as coming from the url you recommended. Somehow I feel relieved
that you guys don't tailor make all these things as the questions come along.
Even the small things I have to work with seem difficult enough...Thanks
again.

Rgds,

Kragelund

 

[Paul Willman]

Bob,

I was trying to solve a similar problem as Tom and tried to use the code on
the Pearson Consulting site and ran into a couple of problems. The first I
solved by making the "Public" function and variable declarations "Private".
The second, which I can't figure out, is I am now getting a "Compile error:
Invalid use of AddressOf operator".

Any thoughts on how to resolve this?

Thanks,

Paul

 

[Paul Willman]

Bob,

I was trying to solve a similar problem as Tom and tried to use the code on
the Pearson Consulting site and ran into a couple of problems. The first I
solved by making the "Public" function and variable declarations "Private".
The second, which I can't figure out, is I am now getting a "Compile error:
Invalid use of AddressOf operator".

Any thoughts on how to resolve this?

Thanks,

Paul

 

[Chip Pearson]

If you are using the AddressOf operator, I assume that you are using
Windows timers rather than Excel's OnTime method. The AddressOf
operator returns the memory address of a function's entry point. It
can be used ONLY in a Windows API call to specify the procedure that
Windows should execute when the API call needs to notify the calling
code that some operation is complete or to pass back information to
the caller. In the case of Windows timers, that procedure is the
procedure to call when the timer "ticks". The procedure whose address
is used with AddressOf MUST conform to the procedure declaration as
described by the API function's documentation.

In the case of the SetTimer API function, you use AddressOf to pass
the address of the procedure that Windows should call when the timer
"ticks". This procedure MUST conform to the following prototype

Sub TimerProc(ByVal HWnd As Long, ByVal uMsg As Long, _
ByVal nIDEvent As Long, ByVal dwTimer As Long)

While the name of the procedure and the names of the variables may be
changed, their order and data types must NOT change. If you use an
invalid procedure declaration, you'll likely get a crash.

AddressOf can be used ONLY with API functions like SetTimer. It is
invalid in any other context and cannot be used in "normal" VBA code.

If you're trying to use Excel's OnTime, you have no need to use
AddressOf at all, and you probably misunderstood the documentation in
the web page. OnTime takes the name (as a string) of the procedure to
call when the timer "ticks".

Post the code you are using and indicate the line of code on which you
received the compiler error.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[Paul Willman]

Bob,

Solved my own problem - I had to use the code in a Standard Module, not a
Class Module - I was also able to use it with the original "Public"
declarations.

Sorry for the unnecessary radar blip.

Paul