Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    Mar 2001
    Location
    New York, USA
    Posts
    52
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SetTimer (Excel 2000 SP-3)

    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

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SetTimer (Excel 2000 SP-3)

    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)?
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    Star Lounger
    Join Date
    Mar 2001
    Location
    New York, USA
    Posts
    52
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SetTimer (Excel 2000 SP-3)

    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

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SetTimer (Excel 2000 SP-3)

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

    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...
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •