Email:      Pass:    Pass?
Close Window   
Free Ebook
Get Your Free Excel
ebook!
Our Top 15 Excel Tutorials
Instant Access!
E-mail:
Subscribe for Free Excel tips & more!
E-mail:
Advertisements



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

View Answers     

Similar Excel Tutorials

Stop a Macro from Running in Excel
How to stop a macro from running after it has started. This method uses the keyboard and has nothing to do with th ...
Pause a Macro or Make it Slow Down in Excel
How to make a macro stop or pause for a specified amount of time. This allows you to slow down the execution of a m ...
Break out of or Exit Different Types of Loops in VBA Macros in Excel
How to Exit, End, or Break out of loops in Excel. This tutorial includes breaking out of While, Do, and For loops. ...
Loop through All Worksheets in Excel using VBA and Macros
Ill show you how to loop through all of the worksheets in a workbook in Excel using VBA and Macros. This only take ...

Helpful Excel Macros

Excel Macro that Searches Entire Workbook and Returns All Matches
- This is the ultimate Lookup Macro for Excel. It will search every worksheet in the workbook and return all of the mat
Vlookup Macro to Return All Matching Results from a Sheet in Excel
- This Excel Macro works like a better Vlookup function because it returns ALL of the matching results. Run the
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

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.


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.


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 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.


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


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 am not sure if anyone can help me on this because it involves data in a program outside of Excel; however, what i am looking for is to make the macro I have loop and repeat itself based on the values in Excel.

If my data begining in A1 includes a number that I am looking up in my program and the macro as i have it written works perfect. The problem is that i need the macro to look at cell A2 and rerun the macro then go to A3 and so on until there is no data in Column A to be searched.


Thanks in advance for your support.

Sub WPGM()
Range("A1").Select
Selection.Copy
Call ConnectWRQ
Session.TransmitANSI "WPGM"
Session.TransmitTerminalKey rcIBMEnterKey
Pause

Session.TransmitANSI "3"
Paste
Pause
Session.TransmitTerminalKey rcIBMEnterKey
Pause
CurPos 19, 22
Pause
Session.TransmitANSI "R2011/5 "
Pause
Session.TransmitTerminalKey rcIBMEnterKey
Pause
Session.TransmitTerminalKey rcIBMPf2Key
Pause
Session.TransmitTerminalKey rcIBMPf3Key
Next

End Sub


Hi,

I've been trying to do this for days now with no success. I have a spreadsheet where I need to load several BDHs (historical data points from a Bloomberg terminal). These are based off of 2 cells, one is the user supplied date (which is then manipulated in the plain spreadsheet to supply the other dates) and the other is the Security ticker.

What I am trying to do is have a macro that will take these cells and write in the BDH formula when these things are labeled "Ready" in a third cell. The values of those BDH calls then need to be pasted into another sheet. This is not the problem.

The problem is the loading data. I've been reading this forum for a couple of days and have yet to find an answer that works but, I need the macro to wait for the data to load before it continues on to paste things. If I use a Application.Wait or a Do While Loop with an arbitrary counter, the data does not finish loading from Bloomberg.

I've read some solutions that break this operation in half so that you have 2 macros so you can run the second once the data has loaded, however this is all part of a much bigger loop that runs through 1000+ securities.

The loop needs to wait for the data from one security to load, copy it and then pull the next security and re-loop. My question is how do I get Excel to pause in such a way that allows this?

I am using the 2010 Edition of Excel.


Is it possible to pause the VBA code to give Excel time to calculate before proceeding? What's the VBA statement?

I read about a wait time function but that pauses the entire application and would not allow for calculations.

Thanks,

D


Hi,

Is it possible to insert a pause in a module for users to input data?

My program will be something like:

1 - Activate input sheet
2 - Message box : "please input evidence" (once the user clicks ok the module normally continues running, whereas I would like it to wait)
3 - program waits as user inputs evidence either in the activated sheet or through an application (Netica, Bayesian network) created by the program
4 - once inputted the program reads the input and does it's stuff

Is this possible? is it easy to do?

since there could be a large amount of evidence to be entered I was thinking of either a timed pause (10 secs) and then a yes/no prompt as to whether all the evidence has been entered, if not the wait an extra 10 secs before prompting again and continuing like this until the user clicks yes or perhaps using just a simple ok button once all the evidence is entered, without the timed prompts...

Thanks in advance for all your help,

Mbrolass.


I have a complex model that takes varying times to calculate depending on the inputs. I am trying to run a macro to loop through my process until a specific paramater is met. Unfortunately, the macro proceeds to the subsequent steps before the spreadsheet is done calculating from the previous steps. I have tried using the "Application.Wait Now + TimeSerial(0, 1, 0)" for varying times but this is inefficient and unreliable. Is there a way to make the macro pause until the spreadsheet is done calculating before it moves onto the next step in the macro?

Thanks for your help.


My first time here. I'm having a hard time searching the web on my problem. I have created a macro that does the following steps. But I'm stuck on trying to pause the macro. Please let me know what code I need to use. I was thinking I would use a do while loop command, but not sure. The steps are.

1. export a column to txt
2. run a bat that opens our Terminal and runs a vb script.
3. vb script then opens Results.CSV
4. continue on macro to copy data from Results.CSV to my excel workbook.

I need to somehow pause my VBA macro after I run my bat file and than resume after it recognizes that results.csv is now open and ready to use.

Please let me know if you guys have any information on how to do this.

Thanks,


How do I write a code that will pause excel until a condition is met, say, the value in cell A1 = 100, for example. The formula in A1 is a sum function. My spreadsheet is linked to data outside of excel and sometimes it takes time before the data populates. I can't use a time delay because the time it takes to populate varies, and sometimes does not populate. An input box won't help. And, I don't want the macro to run if the condition is met, and run a different function if the condition is not met. I just want it to pause and wait until the condition is met before proceeding. Is this possible? Any help you can give is great.