locked
Application.OnTime not working RRS feed

  • Question

  • I have recently upgraded to Windows 7, Office 2010, VBA 7 (from XP and Office 1997).  I have several macros that use the Application.OnTime command.  One, for example, gives me a "beep" every few minutes.  When I run this macro it works several times, but then stops for no apparent reason.  It worked completely fine in with my old operating system/Office suite. 

    Is this a bug?  Is there a work-around?

    Thursday, August 25, 2011 1:41 PM

Answers

  • The problem might be due to your declarations, or lack of them (no variables in your 2nd macro are declared).

    When I first tried in one call I ended up with the next ontime being before Now, which obviously doesn't make sense. I then fully declared all variables, eg As Long, As String (and most important of all) As Date as appropriate. I've had the OnTime running now for a couple of hours every minute.

    Suggest you do likewise, eg
    Dim nextTime As Date
    nextTime = CDate(CStr(Hour(Now)) & ":" & Minute(Now) + BellTime & ":00")
    Debug.Print Now, nextTime
    If BellTime > 0.1 Then If BellTime > 0.1 Then Application.OnTime nextTime, "RingBell"

    Probably better to add the Date to NextTime

    In passing suggest you only declare variables at module level that will be used in different procedures, the only one I can see are these
    Private iRings As Long, BellStopper As Long
    although I commented some stuff in your 2nd macro that's not relevant for testing.

    Head your module Option Explicit

    Peter Thornton

    <rtwoodward> wrote in message news:05321a28-2481-4019-ad18-39f7681b8373@communitybridge.codeplex.com...

    Sorry about that,

    Here are the declarations required for these subs:



    Private Declare Function Beep Lib "kernel32" (ByVal dwFreq As Long, ByVal dwDuration As Long) As Long



    Dim BellStopper, iRings, ans, BellTime, mmm, sec, nextbell





    • Proposed as answer by danishani Sunday, March 4, 2012 11:08 PM
    • Marked as answer by danishani Thursday, March 8, 2012 6:25 AM
    Thursday, August 25, 2011 10:15 PM

All replies

  • I have recently upgraded to Windows 7, Office 2010, VBA 7 (from XP and Office 1997).  I have several macros that use the Application.OnTime command.  One, for example, gives me a "beep" every few minutes.  When I run this macro it works several times, but then stops for no apparent reason.  It worked completely fine in with my old operating system/Office suite. 

    Is this a bug?  Is there a work-around?

    • Moved by Mike Feng Monday, August 29, 2011 11:01 AM VBA (From:Visual Basic General)
    • Merged by danishani Sunday, March 4, 2012 11:10 PM duplicate thread
    Thursday, August 25, 2011 1:07 PM
  • I would recommend posting your question to the VBA forum, or perhaps a moderator can move it there. This forum is primarily for Visual Basic .NET.

    http://social.msdn.microsoft.com/Forums/en-US/isvvba/threads

     


    Paul ~~~~ Microsoft MVP (Visual Basic)
    Thursday, August 25, 2011 1:09 PM
  • Thanks.  I've posted it there.
    Thursday, August 25, 2011 1:43 PM
  • OnTime should work exactly the same in your new setup as it did in your old. Post your code.

    Peter Thornton

    Thursday, August 25, 2011 4:39 PM
  • Ooops here's the code that I actually use, before I pasted old code that I no longer use.

    This is 2 linked subs, one that gets things started and one that rings the bell over and over again.

    Variables that are not initialized inside the macro are defined as global variables at the top of the module:

     

    Sub StartBell()
    If iRings > 0 Then
    BellStopper = 1
    iRings = 0
    MsgBox ("The bell is now off")
    Exit Sub
    End If

    BellStopper = 0
    ans = MsgBox("Do you want to run on a particular minute (yes) or a simple bell every x minutes?", vbYesNoCancel + vbQuestion + vbDefaultButton1, "Tell me")
    If ans = vbYes Then ' Set up to ring on the special minute
    BellTime = InputBox("On what minute do you want to ring?", "Minute to ring?", 5)
    If BellTime = "" Then Exit Sub
    mmm = Minute(Now)
    sec = Second(Now)
    mmm = (mmm / BellTime - Int(mmm / BellTime)) * BellTime
    nextbell = BellTime - mmm - sec / 60
    If nextbell < 1 / 60 Then nextbell = BellTime
    ElseIf ans = vbCancel Then
    Exit Sub
    Else

    Rerun:
    ans = InputBox("How many minutes between beeps?", "Minutes between beeps", 5)

    If ans = "" Then Exit Sub
    If ans = 0 Then GoTo Rerun
    BellTime = ans
    nextbell = BellTime
    End If
    Beep 1000, 100
    iRings = 1
    ' Application.OnTime Now + TimeValue("0:05:00"), "RingBell"
    Application.OnTime Now + nextbell * 0.000694, "RingBell"
    End Sub

    Sub RingBell()
    If BellVol = 0 Then BellVol = 100
    iRings = iRings + 1
    ' Selection.TypeText Text:=iRings
    ' Selection.TypeParagraph

    Dim ansBell As VbVarType
    If BellStopper = 0 Then

    ' Beep 10, 10
    Beep 1000, BellVol
    ' Beep 10, 10
    ' Beep 500, BellVol
    ' Beep 10, 10

    If iRings > 99 Then
    ansBell = MsgBox("The bell has rung " & iRings & " times. Do you want to continue?", _
    vbYesNo)
    If ansBell = vbYes Then
    iRings = 0
    Else
    BellStopper = 1
    Exit Sub
    End If

    End If

    If LastBackupCheck - Now > iBackuphour * 60 * 0.000694445 Then Call BackupRun

    'ans = MsgBox("Rings =" & iRings & " BellTime =" & BellTime)
    TimeToRing = Hour(Now) & ":" & Minute(Now) + BellTime & ":00"
    If BellTime > 0.1 Then If BellTime > 0.1 Then Application.OnTime TimeValue(TimeToRing), "RingBell"
    ' If BellTime > 0.1 Then Application.OnTime Now + BellTime * 0.000694445, "RingBell"
    End If
    End Sub

    Thursday, August 25, 2011 5:02 PM
  • Variables that are not initialized inside the macro are defined as global variables at the top of the module:

    There's only one variable declared in a macro. It would save a lot of time recreating your code if you also post the declarations

    Peter Thornton

    Thursday, August 25, 2011 5:51 PM
  • Sorry about that,

    Here are the declarations required for these subs:

    Private Declare Function Beep Lib "kernel32" (ByVal dwFreq As Long, ByVal dwDuration As Long) As Long

      Dim BellStopper, iRings, ans, BellTime, mmm, sec, nextbell

     



    Thursday, August 25, 2011 6:02 PM
  • The problem might be due to your declarations, or lack of them (no variables in your 2nd macro are declared).

    When I first tried in one call I ended up with the next ontime being before Now, which obviously doesn't make sense. I then fully declared all variables, eg As Long, As String (and most important of all) As Date as appropriate. I've had the OnTime running now for a couple of hours every minute.

    Suggest you do likewise, eg
    Dim nextTime As Date
    nextTime = CDate(CStr(Hour(Now)) & ":" & Minute(Now) + BellTime & ":00")
    Debug.Print Now, nextTime
    If BellTime > 0.1 Then If BellTime > 0.1 Then Application.OnTime nextTime, "RingBell"

    Probably better to add the Date to NextTime

    In passing suggest you only declare variables at module level that will be used in different procedures, the only one I can see are these
    Private iRings As Long, BellStopper As Long
    although I commented some stuff in your 2nd macro that's not relevant for testing.

    Head your module Option Explicit

    Peter Thornton

    <rtwoodward> wrote in message news:05321a28-2481-4019-ad18-39f7681b8373@communitybridge.codeplex.com...

    Sorry about that,

    Here are the declarations required for these subs:



    Private Declare Function Beep Lib "kernel32" (ByVal dwFreq As Long, ByVal dwDuration As Long) As Long



    Dim BellStopper, iRings, ans, BellTime, mmm, sec, nextbell





    • Proposed as answer by danishani Sunday, March 4, 2012 11:08 PM
    • Marked as answer by danishani Thursday, March 8, 2012 6:25 AM
    Thursday, August 25, 2011 10:15 PM