Delay Code Execution 1

[kopy]

I'm writing some VBA and I need to delay a few lines of code from executing while a utility I'm running is executing. How do I suspend the VBA from executing for 10 seconds?

Thanks for your help,

Kopy

 

[cLFlaVA]

Here's one way...

    Dim curTime As Date
    Dim futTime As Date
    curTime = Now
    futTime = DateAdd("s", 10, curTime)
    
    While curTime < futTime
        curTime = Now
    Wend
    
    MsgBox "done with timer"

*cLFlaVA
----------------------------
When will I be able to see what other members can see about myself? It's been like, a freakin' month already!

 

[RickSpr]

cLFlaVA's solution works, but it eats up CPU time like crazy, and will actually slow down whatever it is you're waiting for. Here's a better solution: faq705-5433

(Note: I have just created this FAQ. FAQs are subject to approval by Tek-Tips management. If the link doesn't work, try it again tomorrow.)

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[citychap26]

Hmm, sorry but I think you're both wrong. I would suggest using an API as follows:

'Put this in the declarations section
Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

now here's an example of code

Function test(qName As String) As Boolean

Dim qryDef As QueryDef
Dim dbs As Database
Dim sTime As String
Dim eTime As String

Set dbs = CurrentDb()

For Each qryDef In dbs.QueryDefs
If qryDef.Name = qName Then
sTime = Now()
test = True
Sleep 5000
DoEvents
eTime = Now()
Debug.Print DateDiff("s", eTime, sTime)
Exit Function
End If
Next

End Function

Cheers

Sunil

 

[RickSpr]

I think I remember reading somewhere (MSDN?) that using the Sleep API wasn't safe in VBA. Something about the user causing an event to fire while the thread was suspended in the API.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[DanJR]

or, you could get the process id and wait for the process to finish using the WaitForSingleObject API

??

but, you'll want to also add some sort of time out procedure to it.

Cheers,
Dan

 

[rgbanse]

I've been using this for quite a while now - seems to work fine

Public Function Pause(Delay As Double, Optional Increment As String)
'Example: Pause (3) will delay program continuation for 3 seconds
'Example: Pause (3,"S") will delay program continuation for 3 seconds
'Example: Pause (3,"M") will delay program continuation for 3 minutes
'Example: Pause (3,"H") will delay program continuation for 3 hours

Dim PauseTime, Start, Finish, TotalTime
If Increment = "" Then Increment = "S"
Select Case Increment
Case Is = "S" ' Sets length of pause for seconds.
PauseTime = Delay
Case Is = "M" ' Sets length of pause for minutes.
PauseTime = Delay * 60
Case Is = "H" ' Sets length of pause for hours.
PauseTime = Delay * 3600
Case Else 'Checks for valid Increment value and displays message box in invalid
MsgBox "Pause(" & Delay & ") - Defaults to seconds." & vbCr _
& "Pause(" & Delay & ",""S"")" & vbCr _
& "Pause(" & Delay & ",""M"")" & vbCr _
& "Pause(" & Delay & ",""H"")" & vbCr _
& "Are the only valid values - please re-enter and try again."
Exit Function
End Select

'Here's where the delay starts
Start = Timer ' Starts the delay.
Do While Timer < Start + PauseTime
DoEvents ' Let other stuff run while paused.
Loop

End Function

RGB