How do I pause/wait 1 second in Excel VBA?

I am iterating through a for/next loop where each loops retrieves data from a database and outputs data to a .txt file.  There are about 3000 iterations (for x = 1 to 3000).  

What ends up happening, I think, is that Excel cannot read & write data fast enough to keep up with the for/next loop iterations...and ends up skipping some files and not outputting a .txt file at all.  Thus, I am missing some important data files.  

How can I "slow down" the for/next loop such that it waits 1 full second before moving onto the next iteration?  However, I don't want to "pause" the program because the data will not get read/written/outputted to a .text file if the program is paused.  Basically I want to give the macro time to output data before it moves onto the next iteration.  How do I do this?
LVL 1
shaolinfunkAsked:
Who is Participating?
 
zorvek (Kevin Jones)ConsultantCommented:
The best way to pause code execution is to provide a mechanism that gives the parent application such as Excel or Word opportunities to handle events as well as other operating system tasks. The routine below provides both and allows a pause of as little as a hundredth of a second.

Note that the declaration of the Sleep API function has to be placed above all other routines in the module.

[Begin Code Segment]

Public Declare Sub Sleep Lib "kernel32" (ByVal Milliseconds As Long)

Public Sub Pause( _
      ByVal Seconds As Single, _
      Optional ByVal PreventVBEvents As Boolean _
   )

' Pauses for the number of seconds specified. Seconds can be specified down to
' 1/100 of a second. The Windows Sleep routine is called during each cycle to
' give other applications time because, while DoEvents does the same, it does
' not wait and hence the VB loop code consumes more CPU cycles.

   Const MaxSystemSleepInterval = 25 ' milliseconds
   Const MinSystemSleepInterval = 1 ' milliseconds
   
   Dim ResumeTime As Double
   Dim Factor As Long
   Dim SleepDuration As Double
   
   Factor = CLng(24) * 60 * 60
   
   ResumeTime = Int(Now) + (Timer + Seconds) / Factor
   
   Do
      SleepDuration = (ResumeTime - (Int(Now) + Timer / Factor)) * Factor * 1000
      If SleepDuration > MaxSystemSleepInterval Then SleepDuration = MaxSystemSleepInterval
      If SleepDuration < MinSystemSleepInterval Then SleepDuration = MinSystemSleepInterval
      Sleep SleepDuration
      If Not PreventVBEvents Then DoEvents
   Loop Until Int(Now) + Timer / Factor >= ResumeTime
   
End Sub

[End Code Segment]

Note that the expression

   Int(Now) + Timer / Factor

is used to create a time that both handles midnight crossovers and is accurate to within 1/100 of a second. Just the Timer function alone is accurate to within 1/100 of a second but does not handle midnight crossovers. The Now function is only accurate to within about 1/4 of a second.

The DoEvents call is used to give the managed environment such as Excel or Word opportunities to handle events and do other work. But DoEvents only works within the managed environment and can still consume a considerable amount of resources without some other throttling mechanism. By also using the Windows Sleep API call the Windows operating system is given an opportunity to let other processes run. And, since the code is doing nothing but waiting, it is the appropriate thing to do.

Often the task involves waiting for an asynchronous task to complete such as a web query. To use the above routine while waiting for such a task to compete, two time durations are needed: the total amount of time to wait until it can be assumed that a failure has occurred in the other task, and the amount of time to wait between checks that the other task has completed. Determining how long to wait until giving up requires consideration of the longest possible time that the task could reasonably take and how long the user is willing to wait for that task to complete - wait too long and the user gets frustrated, don't wait long enough and the risk increases of falsely assuming an error occurred when it didn't. This duration is the more difficult to determine of the two. The second time, the duration between checks for completion, is easier to determine. This duration should be long enough to not consume unnecessary CPU cycles doing the check, but short enough to respond quickly when the status of the asynchronous task changes. A duration of between a quarter of a second and one second is usually reasonable. The sample code below illustrates how to wait for an asynchronous task to complete that usually finishes in less than 10 seconds.

   Dim TimeoutTime As Date
   TimeoutTime = Now() + TimeSerial(0, 0, 30) ' Allow 30 seconds for the asynchronous task to complete before assuming it failed
   Do While Now() < TimeoutTime And Not IsTaskComplete
      Pause 0.5 ' Pause half a second to allow the ashyncronous task (and the rest of the environment) to do work
   Loop

The above example uses a function named IsTaskComplete to determine if the asynchronous task completed. The function can do anything such as checking if a cell changed, checking if a control's property is set, or checking if a file exists.


Other techniques for pausing code execution and the problems with each are listed below. These should all be avoided in any well-designed application.

Wait Method (VBA only):

   Application.Wait Now() + TimeSerial(0, 0, 10)

The Wait method suspends all application activity and may prevent other operations from getting processing time while Wait is in effect. However, background processes such as printing and recalculation continue. The net effect of pausing using the Wait method is to shut down the application (e.g. Excel) event handling and slow or stop other applications. This method does not allow any fractional seconds to be used.

Windows Sleep:

   Public Declare Sub Sleep Lib "kernel32" (ByVal Milliseconds As Long)
   Sleep 10000

Using the Sleep Windows API call is system friendly by allowing all other processes to get processing time but it effectively shuts down the parent application. This is generally not a good idea as an application should always be responsive to user requests, even if the application is waiting for an asynchronous task to complete. Extended sleep periods can also cause problems when Windows is sending out system events such as system shut down notifications.

DoEvents Loop

   TimeoutTime = Now() + TimeSerial(0, 0, 10)
   Do
      DoEvents
   Loop Until Now > TimeoutTime

Performing a DoEvents loop to pause gives the parent application a chance to handle events but, because there is no pause between DoEvents calls, virtually all available processing time is dedicated to the loop and nothing else which means this is not a good way to pause code execution. This method does not allow any fractional seconds to be used.

Basic Loop without DoEvents

   TimeoutTime = Now() + TimeSerial(0, 0, 10)
   Do
   Loop Until Now > TimeoutTime

A tight loop without a DoEvents call effectively brings the workstation to a halt until the loop exits. This is the worst technique to pause code execution. This method does not allow any fractional seconds to be used.

There are other, more sophisticated, techniques that monitor event queues and other system resources but the net result is the same as a simple loop with both a DoEvents and a Sleep. As long as some other throttling mechanism is used such as the Windows Sleep function, DoEvents will consume very little resources as all it does is look for any pending events and then either processes those events or returns immediately to the caller.

Kevin
0
 
DaveCommented:
You can use Wait, ie

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

Open in new window

0
 
MadonnaCCommented:
debug.print the data - then take a look at what excel is writing. If it is a timing issue, then you will see incorect data. If it is a file issue (disk full, no permissions, not closing file(s) ) then you would see correct data in the debug window
0
 
zorvek (Kevin Jones)ConsultantCommented:
How are you retrieving the data? If you are using an asynchronous technique you are going to have timing problems and inserting a pause will only start to solve the problem.

A better solution (and bulletproof) is to us a synchronous technique such as using the ADODB interface. Your code will not continue until the DB command is complete.

Here is a tutorial on using ADODB to interface with a database.

The following text and sample code illustrates how to:

   -> open a database connection
   -> use a stored procedure to perform a query
   -> execute any SQL command against a database
   -> open a recordset using a custom query
   -> open a recordset using a table name
   -> check for an empty recordset
   -> read all records in a recordset
   -> add a record to a recordset
   -> delete a record from a recordset
   -> copy a recordset with headers to a worksheet
   -> close a recordset and database

This sample code, except for Open database method, can be used with any database such as Access, SQL Server, or Oracle. When using a database, most interaction happens via a recordset. Data is manipulated almost entirely using Recordset objects. Any number of Recordset objects can be created and used at the same time - each representing a different query or the same query. Different Recordset objects can access the same tables, queries, and fields without conflicting.

After opening a Recordset the Recordset can contain zero or more records. One record in the Recordset is always the current record except when the Recordset BOF or EOF property is true in which case no record is the current record. The current record is the record that is affected by any record-specific methods. To move amongst the records in a Recordset use the MoveNext, MovePrevious, MoveLast, and MoveFirst Recordset methods. A specific record can be made the current record by setting the AbsolutePosition property to the index number of the desired record. Fields in the current record are access as illustrated below.

   Value = MyRecordset!Field1
   MyRecordset!Field2 = Value + 1

When the current record is changed use the Update method to apply the changes to the database. Use the Add method to add a new record and the Delete method to delete the current record. The Add method can be used even if the query returns an empty recordset.

Caution about using the RecordCount method: The RecordCount for a serverside recordset may return a -1. This occurs with ActiveX Data Objects (ADO) version 2.0 or later when the CursorType is adOpenForwardonly or adOpenDynamic and when with ADO 1.5 only when the cursortype is adOpenForwardonly. To get around this problem use either adOpenKeyset or adOpenStatic as the CursorType for server side cursors or use a client side cursor. Client side cursors use only adOpenStatic for CursorTypes regardless of which CursorType is selected.

Before writing any ADODB code the data objects library "Microsoft ActiveX Data Objects x.x Library" must be referenced in the VBA project (Tools->References).

For additional information on the ADODB interface see the MSDN pages at:

   http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdmscadoapireference.asp

Sample code:

   Dim MyDatabase As ADODB.Connection
   Dim MyCommand As ADODB.Command
   Dim MyRecordset As ADODB.RecordSet
   Dim Column As Long

   ' Open database connection
   Set MyDatabase = New ADODB.Connection
   MyDatabase.CursorLocation = adUseClient
   MyDatabase.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source='C:\full\path\to\database.mdb'; User Id=admin; Password=;"
   ' For more information about Open syntax:
   '   http://msdn.microsoft.com/en-us/library/ms808201.aspx
   ' Additional help constructing connection strings can be found at http://www.connectionstrings.com/

   ' Query database using stored procedure (requires command object)
   Set MyCommand = New ADODB.Command
   Set MyCommand.ActiveConnection = MyDatabase
   MyCommand.CommandText = "qrySomeQuery" ' <- name of procedure
   MyCommand.CommandType = adCmdStoredProc
   With MyCommand
      .Parameters.Refresh
      .Parameters.Append .CreateParameter("QueryTextParam", adVarChar, adParamInput, 10, StringValue)
      .Parameters.Append .CreateParameter("QueryTextParam", adDouble, adParamInput, , DoubleValue)
      .Parameters.Append .CreateParameter("QueryLongParam", adBigInt, adParamInput, , LongValue)
      .Parameters.Append .CreateParameter("QueryDateParam", adDate, adParamInput, , DateValue)
      .Parameters.Append .CreateParameter("QueryDateTimeStampParam", adDBTimeStamp, adParamInput, , DateTimeValue)
      .Parameters.Append .CreateParameter("BooleanParam", adBoolean, adParamInput, , BooleanValue)
      ' For more information about CreateParameter syntax:
      '   http://msdn.microsoft.com/en-us/library/ms808298.aspx
   End With
   
   ' Open recordset using command object
   Set MyRecordset = New ADODB.Recordset
   MyRecordset.Open MyCommand, , adOpenDynamic, adLockPessimistic
   ' For more information about Open method syntax:
   '   http://msdn.microsoft.com/en-us/library/ms808656.aspx
   
   ' Build a custom query using command object
   Set MyCommand = New ADODB.Command
   With MyCommand
      Set .ActiveConnection = MyDatabase
      .CommandType = adCmdText
      .CommandText = "SELECT * From tblMyTable WHERE (tblMyTable.MyID = 1)"
   End With
   MyRecordSet.Open MyCommand, , adOpenDynamic, adLockReadOnly

   ' Execute any SQL statement
   MyDatabase.Execute "INSERT INTO TableName (Field1, Field2) VALUES ('" & Range("A1").Value & "','" & Range("A2").Value & "')"

   ' Open a recordset by specifying specific table (no query)
   MyRecordset.Open "TableName", MyDatabase, adOpenDynamic, adLockPessimistic
   
   ' Open a recordset by specifying query without using command object
   MyRecordset.Open "SELECT * FROM MyTable", MyDatabase, adOpenDynamic, adLockPessimistic

   ' Test for no records
   If MyRecordset.BOF And MyRecordset.EOF Then
      MsgBox "No records in table"
   End If

   ' Determine total records (see notes above about inconsistencies with the RecordCount method)
   MsgBox "Total records: " & MyRecordset.RecordCount
   
   ' Look at all records in record set
   While Not MyRecordset.EOF
      MsgBox "Record number: " & MyRecordset.AbsolutePosition
      MyRecordset.MoveNext
   Wend

   ' Find a specific record given a field value
   MyRecordset.MoveFirst
   MyRecordset.Find "ID='ABC123'"
   If Not MyRecordset.BOF And Not MyRecordset.EOF Then
      MyRecordset!Field1 = "Match Found"
      MyRecordset.Update
   End If

   ' Copy the entire recordset to a worksheet (this technique does not copy field names)
   Sheets("Sheet1").[A2].CopyFromRecordset MyRecordset
   
   ' Create headers and copy data
   With Sheets("Sheet1")
      For Column = 0 To MyRecordset.Fields.Count - 1
         .Cells(1, Column + 1).Value = MyRecordset.Fields(Column).Name
      Next
      .Range(.Cells(1, 1), .Cells(1, MyRecordset.Fields.Count)).Font.Bold = True
     .Cells(2, 1).CopyFromRecordset MyRecordset
   End With

   ' Update current record
   MyRecordset!Field1 = "Some data"
   MyRecordset!Field2 = "Some more data"
   MyRecordset.Update

   ' Move specific fields from current record to worksheet
   With Sheets("Sheet1")
      Cells(Row, "A") = MyRecordset!Field1
      Cells(Row, "B") = MyRecordset!Field2
   End With
   
   ' Add new record and set field values
   MyRecordset.AddNew
   MyRecordset!Field1 = "Some data"
   MyRecordset.Update

   ' Update an existing record or add it if it does not exist
   MyRecordset.MoveFirst
   MyRecordset.Find "Field1='"& SourceSheet.Cells(Row, "A") & "'"
   If MyRecordset.BOF Or MyRecordset.EOF Then
      MyRecordset.AddNew
   End If
   MyRecordset!Field1 = SourceSheet.Cells(Row, "A")
   MyRecordset!Field2 = SourceSheet.Cells(Row, "B")
   MyRecordset.Update
   
   ' Delete current record
   MyRecordset.Delete

   ' Close recordset
   MyRecordset.Close

   ' Close database
   MyDatabase.Close

Kevin
0
 
shaolinfunkAuthor Commented:
most detailed response i've ever gotten on EE.  thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.