Results 1 to 9 of 9
  1. #1
    New Lounger
    Join Date
    Jun 2003
    Location
    Swindon, Wiltshire, England
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Pausing a Macro (Excel 2002)

    I would like to be able to pause a macro for x number of seconds or milliseconds. Not for user input or anything else, purely to slow it down.
    I do not want the user to have to restart the macro.

  2. #2
    New Lounger
    Join Date
    Jun 2003
    Location
    Swindon, Wiltshire, England
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pausing a Macro (Excel 2002)

    Post deleted by galaxy

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Pausing a Macro (Excel 2002)

    This is from VB HELP under TIMER and should get you started, modify to suit your needs
    Steve


    <pre>Dim PauseTime, Start, Finish, TotalTime
    If (MsgBox("Press Yes to pause for 5 seconds", 4)) = vbYes Then
    PauseTime = 5 ' Set duration.
    Start = Timer ' Set start time.
    Do While Timer < Start + PauseTime
    DoEvents ' Yield to other processes.
    Loop
    Finish = Timer ' Set end time.
    TotalTime = Finish - Start ' Calculate total time.
    MsgBox "Paused for " & TotalTime & " seconds"
    Else
    End
    End If</pre>


  4. #4
    New Lounger
    Join Date
    Jun 2003
    Location
    Swindon, Wiltshire, England
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pausing a Macro (Excel 2002)

    Thats great thanks.

    I am a bit of a vba virgin but i managed to get it down to

    Dim PauseTime, Start, Finish, TotalTime
    PauseTime = 5
    Start = Timer
    Do While Timer < Start + PauseTime
    DoEvents
    Loop
    Finish = Timer ' Set end time.
    TotalTime = Finish - Start ' Calculate total time.

  5. #5
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,819
    Thanks
    133
    Thanked 480 Times in 457 Posts

    Re: Pausing a Macro (Excel 2002)

    Why not use
    Application.Wait(Now + TimeValue("0:00:05"))

    ..putting in required values (e.g. 5 seconds in example above)

    zeddy

  6. #6
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Long Beach, California, USA
    Posts
    233
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pausing a Macro (Excel 2002)

    From Excel Help:

    The Wait method suspends all Microsoft Excel activity and may prevent you from performing other operations on your computer while Wait is in effect. However, background processes such as printing and recalculation continue.

    DoEvents passes control to the operating system. Control is returned after the operating system has finished processing the events in its queue. Caution: Any time you temporarily yield the processor within an event procedure, make sure the procedure is not executed again from a different part of your code before the first call returns; this could cause unpredictable results. In addition, do not use DoEvents if other applications could possibly interact with your procedure in unforeseen ways during the time you have yielded control.


    edited to remove redundant information

  7. #7
    New Lounger
    Join Date
    Jun 2003
    Location
    Swindon, Wiltshire, England
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pausing a Macro (Excel 2002)

    Application.Wait(Now + TimeValue("0:00:05"))

    This command does work fine but i dont know how to use parts of seconds. eg 1.5 or 0.75 seconds.

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Pausing a Macro (Excel 2002)

    Application.Wait(Now +0.75/24/60/60)
    OR
    Application.Wait(Now +0.75/86400)

    Excel time is in DAYS/ The numbers are the seconds in a day.

    Steve

  9. #9
    New Lounger
    Join Date
    Jun 2003
    Location
    Swindon, Wiltshire, England
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pausing a Macro (Excel 2002)

    Thanks

    I tried Application.Wait(Now + TimeValue("0:00:00.75")) but didnt think it would work!

Posting Permissions

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