SetTimer (Excel 2000 SP-3)

Topic

New Reply

Has anybody had any experience with multiple timers using SetTimer API? I can get two timers going but when I kick in the second one the timing speed is doubled. I think the problem is in the TimerProc sub.

Sub TimerProc(ByVal hwnd As Long, ByVal uMsg As Long, ByVal nIDEvent As Long, ByVal dwTimer As Long)

If bTimer = True Then
iCounter = iCounter + 1
UserForm1.TextBox1.Text = CStr(iCounter)
End If

Code example of one of the CommandButton subs:

Private Sub cbStartStop2_Click()

If bTimer2 = False Then
lngTimerID2 = SetTimer(0, 0, 1000, AddressOf TimerProc)
If lngTimerID2 = 0 Then
MsgBox “Timer not created. Ending Program”
Exit Sub
End If
bTimer2 = True
cbStartStop2.Caption = “Stop Timer”
Else
lngTimerID2 = KillTimer(0, lngTimerID2)
If lngTimerID2 = 0 Then
MsgBox “Couldn’t kill the timer”
End If
bTimer2 = False
cbStartStop2.Caption = “Start Timer”
End If

End Sub

If bTimer2 = True Then
iCounter2 = iCounter2 + 1
UserForm1.TextBox2.Text = CStr(iCounter2)
End If

End Sub

Reply | Quote

Replies

Could you give some more information?

What are these timers to do?

I didn’t know one could set a timer to a sub that needs arguments. What arguments is the sub getting (and how)?

Reply | Quote

Jan,

My brain went plaid again. Just moments after posting I realized that I was using the same Timer Process for both timers. After I made a new Timer Process sub I was able to run both timers independently. Probably a perfect situation to develop a class module. I will play with that today.

I also apologize that I didn’t notice that I didn’t include the sub header when posting the original. The timers will time event updates to the spreadsheet and raise a flag (interior.colorindex) if a process has not been updated for a period of time. The code I am posting is just experimental use with forms for the timers. As you probably know SetTimer API can crash Excel in a heartbeat if not coded carefully.

Sub TimerProc(ByVal hwnd As Long, ByVal uMsg As Long, ByVal nIDEvent As Long, ByVal dwTimer As Long)

If bTimer = True Then
iCounter = iCounter + 1
UserForm1.TextBox1.Text = CStr(iCounter)
End If

End Sub

This will be changed to something like the following instead of the user form.

If iCounter < 15 Then
Else
Sheet2.Range("C2").Interior.ColorIndex = 36
EndTimer
End If

Reply | Quote

[indent]

---

As you probably know SetTimer API can crash Excel in a heartbeat if not coded carefully

---

[/indent]

Yes. Especially if the called sub does things to XL normally prohibited in the state XL is in when the sub is executed. ONe of them is printpreview. Another is in Cell edit mode. And there are possibly more…

Reply | Quote