Email:      Pass:    Pass?
Close Window   
TE
Subscribe for Email Updates!
Excel tips, help, and more!
E-mail:


Advertisements


Free Excel Forum

Wait/pause During Loop

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

Hi

I've looked about but I must be searching using the wrong words as I can't seem to find what I'm after.

I have a do while .... loop, all I want to do is slow it down by just by a second or two to see what's going on. A pause until a key is pressed isn't required.

In original basic I used years ago it would have gone something like:

for a=1 to 1000:next a

or

wait 1000

a simple one second pause but it doesn't like it.

Any suggestions are welcome.

Cheers


Similar Excel Video Tutorials

Helpful Excel Macros

Delete a VBA Module From Excel
- Delete a VBA macro module from Excel with this macro. This macro allows you to fully remove a macro module from Excel.
Count The Number of Words in a Cell or Range of Cells in Excel - UDF
- Count words in cells with this user defined function (UDF). This UDF allows you to count the number of words that are w
Count The Number of Words in a Cell or Range of Cells in Excel - With User-Specified Delimiter / Separator - UDF
- UDF to count the number of words in a cell or range with a user-specified delimiter. This means that you can tell the f
Sort Data that Doesn't Have Headers in Ascending Order in Excel
- Sort data that doesn't have headers in ascending order in Excel with this macro. This is a simple sort macro that assum
Basic Web Query in Excel - Import Data from the Web into Excel
- Import data from the web into Excel with this macro. This is a basic web query macro that will import data from any web

Similar Topics









I am currently using this pause function in one of my macros:

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

However, my computer is pretty old and whenever it gets to that pause function it eats up a lot of CPU resources during the time while it's paused. My guess is that it counts to a high number or something in order to determine when to start again, or something to that effect, causing it to use a lot of CPU power (at least for my computer).
Is there an alternate pause function that doesn't eat up any resource? The more basic the better I suppose, I just need it to wait X amount of time before restarting a cycle.

Is it possible to add a pause or wait a few seconds before the code continues to run? What I am thinking is showing a userform once a button is pressed with a message like "please wait..", then after the form is shown on the screem, it is closed then the code continues to run.

Many thanks.


have a simple loop that I want ot pause until some key is pressed.
try as I might I can't find anything to adress this but I would believe this s a common application

for x = 1 to 100

do something

Wait for any (or a specific ) key

next x

Any thoughts?


Is it possible to have an Excel macro pause at a certain step before continuing on with the remainder of the script?

This is what I'm hoping to do:
- call an external program from my macro and pause execution
- wait for that program to finish it's work
- resume processing of the macro

I can't say pause XX seconds/minutes because the processing time of the program will vary considerably. I'd like for the macro to wait until the program terminates before continuing. I'm wondering if there is a way to have it check task mgr or something similar to see if the application is still running.

If not, is there a way to create some sort of loop where the macro does not continue until a condition is met? I was thinkning I can have the other application generate a file when it's done processing. Then have Excel loop continuously until the file exists before it continues processing.

Hopefully that makes sense.


ok...this may seem like a noob question, but it is giving me some grief. Basicaly I want to establish a loop in VB and within the loop I would like to pause execution of the loop and wait for input from a cell then resume execution of the loop once input has been entered. Is there an easy way of doing this? Thanks in advance for any help.


I need to pause for 0.5 seconds (half a second)

How can I do that.

Applicatiom.Wait can only be used for whole seconds and a FOR ... NEXT loop is CPU dependant. Any good ideas?

Gr,
Ricardo


Hi there,
I am writing an Excel Macros and I want it to pause in the middle until some data from other macroses (Equity data from Reuters) can get loaded in the sheet, looked-up etc. So I tried using the Applicaiton.Wait command. However, this command pauses not only my macro but also all other Excel applications. Is it possible that I pause my macro only and wait until the other application are done?
Thanks


I'm using VBA to interact with one of the systems we use in work (Attachmate).
In order to ensure syncronisation between the VBA code and the system I rely on
methods that wait for a string to appear on the screen and when they(the strings) appear the code
continues. The problem now is that i have a to interact with a system/screen that doesn't have any new
strings(that i can predict) appearing and therefore i can not use my WaitForString method. It would
be ideal if there existed a WaitForStringToChange method, but there doesn't.

Does anyone have any idea if there is a way to pause the code and resume with a predefined pause time?
Also does there exist a Timeout method for loop execution? I mean that even if the condition isn't met the loop
will exit because it has been looping too long. I guess i could use an integer to count but it think the time that this would
provide would vary depending on the availability of system resources

i was thinking i could solve my problem with something like the following:
Code:


String1 = screen.area(2,2,3,5)
String2 = String1

Do until String1 < > String2
	String2 = Screen.area(2,2,3,5)
	
	'Here I wanted to put a system/code pause

	'And here a condition that if the loop has been going 
	'for X amount of time then exit the loop with an error message
Loop


The main thing I want is to solve if the Pause issue. I think i could solve the timeout issue by
usign the NOW function and define a variable for the time before entering the loop and define another inside the loop
But if you have any suggestions for this pause issue it would be great.

Thanks


I am trying to 'animate' a procedure and a one second pause - using the application.wait function - is much too long (no pause is much too fast). Is there any method of pausing the 'animation' for say a quarter to a tenth of a second?

The application.wait function does not accept decimals of a second.


I have a spreadsheet that runs 24/7 on a TV. IT works and updates the cells perfect when the macro is off. Here is module 1:
Code:

Option Explicit
Private Declare Function GetAsyncKeyState Lib "user32" (ByVal vKey As Long) As Integer
Private Sub pause()
Dim pause As Worksheet
Do
For Each pause In ThisWorkbook.Worksheets
pause.Activate
Application.Wait Now + TimeValue("00:00:15")
End Sub
 
Public Sub Switch()
 
  Dim ws As Worksheet
 
  Do
    For Each ws In ThisWorkbook.Worksheets
      ws.Activate
      ws.Calculate
      Application.Wait Now() + TimeValue("00:00:15")
      If GetAsyncKeyState(vbKeyShift) Then Exit Sub
      DoEvents
    Next ws
  Loop
 
End Sub


Thisworkbook:
Code:

Private Sub Workbook_Open()
Application.OnTime Now + TimeValue("00:00:15"), "Switch"
End Sub


For some reason sheet 1 (master) cells do not update. I was told maybe it is the pause option. What other options do I have? I am no programmer and had help with this code.


Is there a way to pause the macro while excel runs a query? i am using a macro to run a query on a large file. once the data is returned to excel i am copy/pasting it to different sheet. the problem i am currently running into at this time is that the query takes over few secounds to return data and macro moves to the next line of code. due to which i am getting an error. i have tried to add the code Code:

        Do While IsEmpty(Cells(1, 2))
            .Wait Now + TimeSerial(0, 0, 2)
        Loop


and this is causing excel to get stuck in a loop with no results. please help.


I am developing a userform that loops through some imported data and changes the userform display text boxes based on the data in the row. This is to develop a realtime playback tool of some diagnostic data recorded. I use a for loop from the first row to the last imported row and then update the userform controls based on the current row content. I have a pause function prior to moving to the next row to slow the code down and see the playback on the userform - this gives a realtime playback feel. The function is:

'public function for timer to playback realtime
Public Sub pause(ByVal pSng_Secs As Single)
Dim lsng_start As Single
Dim lsng_end As Single

On Error GoTo Err_Pause

lsng_start = Timer
lsng_end = Timer + pSng_Secs
Do While Timer < lsng_end
Loop
Err_Pause:
Exit Sub
End Sub

In the main code I have:

Pause 2
next r
This gives a pause of two seconds before moving to the next row.

What I want is to do is set pause to a variable to give different delays, I want my main userform to have a set of command buttons that chnage the variable that pause equals. This has to happen while the for loop is running through all the data. I want this so the user can speed up the playback and slowdown the playback as required. Each time a button is pressed, the for loop slows or speeds up depending on user requirements.

HELP??


Hi everyone I am having a little trouble with a sheet i'm currently working on. Having exhausted what i believe to be every avenue trying to do this without VBA, i've finally had to sucomb to it as i can't see an alternative and i'm no VB expert by any means! This is what i have so far:
Code:

Private Sub Worksheet_Calculate()
    If Sheet1.Cells(10, 10) = "1" _
    Or Sheet1.Cells(10, 10) = "2" Then
    'Pause needs to go here!
    Sheet1.Cells(10, 13) = "3"
    Else
        Sheet1.Cells(10, 13) = ""
    End If
End Sub


Basically what i am trying to do is insert a pause, sleep, wait etc. command into my IF statement.
If cell J10 (10, 10) = 1 or 2, pause for 300 milliseconds, then fill cell M10 (10, 13) with the value 3.
I am aware the wait and sleep commands cause excel not to funtion while the command is in use. Ideally i would like everything to perform as usual just that specific cell is paused. I use a program that writes all the data into Excel and it updates every 250ms hence my above effort is under Worksheet_Calculate().


I have a spreadsheet that runs 24/7 on a TV. IT works and updates the cells perfect when the macro is off. Here is module 1:

Code:

Option Explicit
Private Declare Function GetAsyncKeyState Lib "user32" (ByVal vKey As Long) As Integer
Private Sub pause()
Dim pause As Worksheet
Do
For Each pause In ThisWorkbook.Worksheets
pause.Activate
Application.Wait Now + TimeValue("00:00:15")
End Sub
 
Public Sub Switch()
 
  Dim ws As Worksheet
 
  Do
    For Each ws In ThisWorkbook.Worksheets
      ws.Activate
      ws.Calculate
      Application.Wait Now() + TimeValue("00:00:15")
      If GetAsyncKeyState(vbKeyShift) Then Exit Sub
      DoEvents
    Next ws
  Loop
 
End Sub


Thisworkbook:

Code:

Private Sub Workbook_Open()
Application.OnTime Now + TimeValue("00:00:15"), "Switch"
End Sub


For some reason sheet 1 (master) cells do not update. I was told maybe it is the pause option. What other options do I have? I am no programmer and had help with this code.


I am looking to do a macro that cycles through a chart updating it with a slight delay. The example is I have 5 customers and I want a chart to cycle through each customer displaying their sales on a chart for 3-5 seconds each. I want the chart to pause between the update but still allow the spreadsheet to be edited/scrollable. Below is the VB code that I can find but it actually pauses Excel completely. I am advanced in the front end of excel but relatively new to the macro/VB side. I usually can take what someone has as an example and apply it to what I need but all the recommendations for the "Application.Wait" examples does what I want with the chart but does not allow movement in the spreadsheet.

Sub chartloop()
Dim i As Integer
i = 0
Do Until (i = 5)
i = i + 1
Range("B10") = i
Application.wait Now() + TimeValue("00:00:03")
Loop
End Sub

While I am at it, you'll notice that the code only cycles through 5 times, I haven't looked yet but is there a way to reset the "i" to 0 after x number of cycles?


Thanks,
Jim L.


I have some code that is sending out emails to a mailing list. My ISP only lets me send out 500 emails an hour so I've added a very simple counter that pauses the routine for 45 minutes when it gets to 450 using "Application.Wait Time + TimeSerial(0, 45, 0)"

This is working exactly as I want it to, except that when the code is in the pause period it is using 100% of the CPU (well, 100% of one core so 50% total) - which is causing my laptop to heat up considerably (thanks dell for incrediblly poor fan/air flow design!)

After some searching, I've come across a number of other people having this problem, but haven't found a solution.

Does anyone know if there is a way to pause VBA code for a set period of time without it using 100% of the CPU? Or can anyone confirm that there is no way, and I can give up looking! (if this is the case, does anyone know if it's a good idea to leave the laptop in the fridge overnight whilst the code is running?)

Cheers

Charlie


Hi everyone I am having a little trouble with a sheet i'm currently working on. Having exhausted what i believe to be every avenue trying to do this without VBA, i've finally had to sucomb to it as i can't see an alternative and i'm no VB expert by any means! This is what i have so far:

Code:

Private Sub Worksheet_Calculate()
    If Sheet1.Cells(10, 10) = "1" _
    Or Sheet1.Cells(10, 10) = "2" Then

    'Pause needs to go here!

    Sheet1.Cells(10, 13) = "3"
    Else
        Sheet1.Cells(10, 13) = ""
    End If
End Sub


Basically what i am trying to do is insert a pause, sleep, wait etc. command into my IF statement.

If cell J10 (10, 10) = 1 or 2, pause for 300 milliseconds, then fill cell M10 (10, 13) with the value 3.

I am aware the wait and sleep commands cause excel not to funtion while the command is in use. Ideally i would like everything to perform as usual just that specific cell is paused. I use a program that writes all the data into Excel and it updates every 250ms hence my above effort is under Worksheet_Calculate().


I want Ecell 2007 Macro to move to predetermined cells pause/wait until the data in that cell is edited and or new data key entered. Once enter has been hit the macro moves to the next predetermined cell.


Hello i'm using the Wait method to pause a macro's excecution.

Is it possible with this function, or another one, to get a wait time of less than a second?


Thanks in advance.


Good morning all,

I have a macro that includes a loop. The loop changes some data but allows the user to watch the changes. What I would like to do is allow the user to pause the loop if there is something that they notice and would like to explore further but also allow the user to continue once they are finished looking it it. Not really sure how to accomplish this. I was thinking something like:

Code:

  Sub Test1()

For x = 1 to 150
'user presses a key or something to pause the macro
'if user presses another key or something, macro continues

code to change data

next x


Any help would be greatly appreciated!
thanks
tc


I want to record a macro and pause it for input. I do not have a pause button in Excel but it shows up in Word. I went to all commands in both apps and it does not exist in Excel but does in Word. I've done searches on how to pause in Excel and my results show there should be a pause button in this app. My IT dept says they cannot help me. Does anyone know if this button exists in Excel 2007 and where I might find it?


Hi,
Can anybody tell me if it is possible to pause a macro until the user clicks the mouse? At the moment I have it on a timer to pause for five seconds but think it would be better if the user can control how long the pause is for.
Thanks,
Sian


Is there any code that will only pause a specific sub and not the entire workbook?

Im currently using application.wait waitTime but would like other pieces of code to still run during this wait period

Cheers,

Tim


Hi!

I am using Sendkeys for Hyperion Planning Excel Add-In User Interface in order to automate retrieving data from the database. Sometimes the UI is too slow and I'd need a little pause between Sendkeys commands. The most helpful solution was provided by TsTom year 2003 in this Forum, see http://www.mrexcel.com/archive2/42000/48659.htm. However, it doesn't work in the UI window of Hyperion Add-In, only after closing the UI window and before opening another UI window. I need a pause inside the UI window. I've used three days for solving this problem and this forum is my last hope

TsTom's almost working 5-years old solution in brief:
Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Dim lngCounter As Long
Do While lngCounter < 6000 lngCounter = lngCounter + 1
S leep 100 'wait for 1/10th second
DoEvents 'yield processes
Loop

-MrWeb


I looked up the timer and pause functions, but they didn't allow me to modify the text that my macro inserted. how can I pause the macro for 30 seconds and still be able to change the font of the WordArt text before the macro finishes up?

Thanks,

Gary