Email:      Pass:    Pass?
Close Window   
Free Ebook
Get Your Free Excel
Our Top 15 Excel Tutorials
Instant Access!

Subscribe for Free Excel tips & more!

Free Excel Forum

Wait/pause During Loop

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


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


wait 1000

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

Any suggestions are welcome.


View Answers     

Similar Excel Tutorials

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 ...
Loop Through an Array in Excel VBA Macros
I'll show you how to loop through an array in VBA and macros in Excel.  This is a fairly simple concept but it can ...
How to Make Macros Run A LOT Faster
Here is a very simple and easy-to-use tip to make all of your Excel macros run A LOT faster.  It is very simple and ...
Print Only Specific Parts of a Worksheet in Excel
In Excel you can select parts of a worksheet to print while ignoring all of the other data on the worksheet. This a ...

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.

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.

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?

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:

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

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.


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)

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


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:

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"
        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:


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



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")
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?

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?)



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:


  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!

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?

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.

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




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


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()
Call ConnectWRQ
Session.TransmitANSI "WPGM"
Session.TransmitTerminalKey rcIBMEnterKey

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

End Sub


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.




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,