Windows Timer crashes Excel

[Robert]

I tried using Chip's Win API code at...

(Using Windows Timers)
http://www.cpearson.com/excel/ontime.htm

It ran fine, except when I was in the Edit mode. Without fail, it
shuts down Excel with no dialog box or other warning. On restart, it
shows the file as been recovered from a crash.

?? What am I doing wrong? Is there a library to reference? I couldn't
find one.

Windows 2000 5.002195 SP3
Excel 2002 SP1

TIA, and here's the code:

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

Sub StartTimer()
TimerSeconds = 5 ' how often to "pop" the timer.
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)
'
' The procedure is called by Windows. Put your
' timer-related code here.
'
Range("a1").Value = Now()
End Sub

 

[Chip Pearson]

Excel will immediately shut down if you attempt to modify the
worksheet from within the TimerProc. I don't think there is any
way around this.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Robin Hammond]

Robert,

There is a VBA ActiveX timer on my site in the Excel stuff section that
might work.

I have just tested on Excel XP, and the timer event will not fire when VB is
not in control, i.e. when you are in edit mode, which avoids the
complications you get with an unhandled API callback. It's based around the
VB timer with a wrapper around it. As an activex it is designed to go on a
form, so see the comments below.

Anyway, this is how it might be done assuming you are using Excel 2000 or
higher.

1. save your work!
2. Create a form called UserForm1
3. Add the Timer control and nothing else to the form - see my site for
instructions
4. In a module

Sub LaunchTimer()
UserForm1.Show vbmodeless
UserForm1.Hide
End Sub

In the Form code
private sub Timer1_Timer()
Beep
End Sub

Private Sub Userform_Initialize()
Timer1.Interval = 5000
Timer1.Enabled = True
End Sub

Good luck, and let us know if it fixed the problem. I haven't had a chance
to push the control to the limits yet.

Robin Hammond
www.enhanceddatasystems.com

 

[Robert]

Thanks, Chip. I misunderstood. I now see on your sight where you warn
that will happen. Thanks for the code, though, because I can still use
it elsewhere.

-Rob

 

[Robert]

Thanks a bunch, Robin. I'll check it out this weekend.

-Robert

 

[Thomas Risi]

Hi Robert,

you can use 'my solution' ... a TimerControl for Office (freeware).

http://rtsoftwaredevelopment.de/Freeware_TimerControl.htm