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 ...
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 ...
Loop through a Range of Cells in Excel VBA/Macros
How to use VBA/Macros to iterate through each cell in a range, either a row, a column, or a combination of both. T ...

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







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


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.


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.


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


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


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.


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.


Good morning,

I was wondering does anybody know of a way to get code to pause in place until a condition is met? Specifically, I want my code to bring up a modeless userform, but I want the code to pause at that spot in the procedure until the userform is unloaded (which is what seems to happen when I bring up a modal userform).

I've tried looking at using a boolean variable in order to control whether the code after the modeless userform is displayed gets executed. But, the code keeps running and just seems to skip over the parts where it does not have permission to execute. Since I have the code within a for-next loop, I don't want the code looping a bunch of times either

The general structure of my code is as follows:

For i = 1 to 52

If a < b then
userform.show vbModeless

--> Here is where I want to pause until the userform is unloaded

Hi all,

I have a simple stopwatch program which i've created in excel, with a start, stop and save button. does anybody know how I would go about making a pause button work. So when the pause button is pressed the timer can be resumed by pressing the start button.

This is my code:
Code:

Dim CmdStop As Boolean
Dim PauseTime, Start, Finish, TotalTime

Sub Btn1_Click()
CmdStop = 0
If (MsgBox("Press Yes to pause for 5 seconds", 4)) = vbYes Then
    PauseTime = 5    ' Set duration.
    Start = Timer    ' Set start time.
    Do While CmdStop = 0
        TimerValue = Timer - Start
        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
End Sub

Private Sub btnPause_Click()
???
End Sub

Private Sub BtnReset_Click()
TimerValue = 0
End Sub

Sub BtnStop_Click()
CmdStop = 1
End Sub





wow, I figure the answer must be simple but I can't seem to figure it out.
I need to pause the macro so that the user can visually verify a column of data, then press a defined key (Enter for example) to continue the macro on its way.
I can see how to pause the macro for a specified time, but not indefinately until a user physically presses a key.
Thanks so much...


if i have this 2-D figure walking a straight line or
a ball rolling a continous motion and speed,
i created a form which have a pause and resume on it
what code should i put in for these two button for them to work?
i.e. i need to code under

Code:

Private Sub Pause()
and 
Private Sub Resume()


and the resume button should do nothing when the figure is moving,
and pause shouldnt anything when the figure is paused.

can someone please help me with the code?
Thanks you a million!


I have a spreadsheet that pulls values from Bloomberg, and then a deletes rows based on the Bloomberg values. Currently I require multiple buttons because the delete happens faster than the data can be pulled. Is there some way to put a pause in until the spreadsheet calculation is complete? I'd prefer the pause not be a fixed amt of time since it varies so greatly.


Thanks,

Bill


Hi gang!

I'm an excel newby and I'm sure there must be an easier way of sorting the formula below out?

=SUM(+$B$5*B6/1000)+(+$C$5*C6/1000)+(+$D$5*D6/1000)+(+$E$5*E6/1000)+(+$F$5*F6/1000)+(+$G$5*G6/1000)+(+$H$5*H6/1000)+(+$I$5*I6/1000)+(+$J$5*J6/1000)+(+$K$5*K6/1000)+(+$L$5*L6/1000)+(+$M$5*M6/1000)+(+$N$5*N6/1000)+(+$O$5*O6/1000)+(+$P$5*P6/1000)+(+$Q$5*Q6/1000)+(+$R$5*R6/1000)+(+$S$5*S6/1000)+(+$T$5*T6/1000)+(+$U$5*U6/1000)+(+$V$5*V6/1000)+(+$W$5*W6/1000)+(+$X$5*X6/1000)+(+$Y$5*Y6/1000)+(+$Z$5*Z6/1000)+(+$AA$5*AA6/1000)+(+$AB$5*AB6/1000)+(+$AC$5*AC6/1000)+(+$AD$5*AD6/1000)+(+$AE$5*AE6/1000)+(+$AF$5*AF6/1000)+(+$AG$5*AG6/1000)+(+$AH$5*AH6/1000)+(+$AI$5*AI6/1000)+(+$AJ$5*AJ6/1000)+(+$AK$5*AK6/1000)+(+$AL$5*AL6/1000)+(+$AM$5*AM6/1000)+(+$AN$5*AN6/1000)+(+$AO$5*AO6/1000)+(+$AP$5*AP6/1000)+(+$AQ$5*AQ6/1000)+(+$AR$5*AR6/1000)+(+$AS$5*AS6/1000)+(+$AT$5*AT6/1000)+(+$AU$5*AU6/1000)+(+$AV$5*AV6/1000)+(+$AW$5*AW6/1000)+(+$AX$5*AX6/1000)+(+$AY$5*AY6/1000)+(+$AZ$5*AZ6/1000)+(+$BA$5*BA6/1000)+(+$BB$5*BB6/1000)+(+$BC$5*BC6/1000)+(+$BD$5*BD6/1000)

Problem is that if I try and add a column - e.g. +(+$BE$% ... then I get an error message telling me the formula is too long.

Thanks for any help.


Can I add a Pause/Resume toggle button (to a userform that takes quite while to execute) that will temporarily pause the macro at a particular step and after pressing it again it will resume the execution of macro from that step..

Is it even possible?

I have posted the same question at the following thread but doesnt seem to get an answer from quite a long time..

http://www.excelforum.com/excel-prog...me-button.html

Regards,
Vaibhav



I want to pause my application in milliseconds. How can I convert the following code and make it work in my 64 bit environment using PtrSafe?

Code:

      Declare Sub Sleep Lib "kernel32" Alias "Sleep" _
             (ByVal dwMilliseconds As Long)


Once if fix this, then the following call should work>

Code:

     Sleep (1000)





Hi,

I have a line of code in a macro that allows the macro to pause for 20 seconds in order for calculations to finish in the spreadsheet before going on to the next step. My code is:

Code:

Application.Wait Now + TimeValue("00:00:20")


Is there any way of replacing this line with something that pauses for the exact amount of time it takes for the spreadsheet calcs to finish. I don't really like using the fixed amount to time. 20 seconds may be too little or too much depending on the sheet size.

Thanks in Advance.


Hi,

I guess this is quite simple but I can't get it.
How do I Loop a macro to row 1000?

I have: Loop Until ActiveCell = vbNullString
But as I have empty cells every now and then the macro stops.
I want the loop to go all the way to row 1000
like Loop Until Row=1000
But how do I write so excel understand what I want?


I got a macro that is trying to populate a table with information from an outside source (Bloomberg), but the macro runs so fast it doesn't have time to populate.

I'm trying to find a way for the macro to pause so that the data can finish loading before it saves and exits.

I've tried application.wait and it paused, but it pauses the whole procedure of the data loading also.

Can anybody help or have any suggestions? Thanks .


How do I pause a macro untill the previous macro is done? Is this automatically the case?

There are buttons for cancel and pause, but neither work. I realized after I initiated the goal seek that i should have closed some larger files, but now that it is running and each iteration is taking 10 sec. it is taking forever. Short of closing excel with the task manager, is there a solution?


Just wondering how I would get a macro to pause to allow me to change a filename and then when I hit enter, it continues.

I use it to export excel worksheets to our webserver and it works now, but overwrites the file name everytime. I want to change the filename before saving.

Any help appreciated.


I'm using Excel 2003. My macro takes a 40,000 line (approx) CSV file, and among other things finds rows with 10 keywords in the row and deletes the entire row.

The problem I want to solve is that the find loop is very very slow. It takes about 1-2 seconds per row to search through all my keywords. 40,000/60/60 = 11 hours.

The find operation is slower with the more words I add, but even with just one or two words, it was kind of slow. I have found that the find/replace command is much quicker, but I can't delete entire rows with that method and that is what I must do.

My approach uses a for each loop to iterate through each row. From there, I use a loop to find each of my key words in that row. Keywords are stored in an array.

Help to speed up this process would be appreciated!

Gabe


Hi Everyone,

I have found code to pause a macro and select a range of cells using an inputbox. This is NOT what I want to do. What I wish to do is pause the macro, allow the user to select an object (it will be a picture as an embedded object), then restart the macro (ie: the macro will tell it where to copy and paste the picture).

If it is possible to restart the macro automatically upon selecting the object that is great, but even better would be an OK button on the message box.

Is there a method whereby an vbokonly messagebox appears (the text will prompt the user to select a picture), then once the ok button is clicked it will restart the macro?


Ladies, Gents,

When the macro is running, it effectively stops the user from selecting any cells, or working within the spreadsheet in any way.

I need the spreadsheet user to be able to pause the running macro via a set of key strokes eg CTRL p for Pause (or other method?).
When the macro is paused, the user can jump into the spreadsheet and "do their thing" eg select a cell for a title, do a graph and print it etc etc. Then when he or she is ready, resume the macro from where it paused with another set of keystrokes (or other method - button?).

They should be able to do this as many times as they like while the macro is running.

The application is Data acquisition from a measuring device, where there can be an hour or more between 1 reading and the next.

Any help here would be appreciated.

Regards

Chris