Wait/pause During Loop

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

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