"Wait" function in VBA

ashes0000 · February 3rd, 2007, 02:28 PM

Hi Everyone,
Can anyone help me to pause a vb program for about 2 minutes.
I have used the Sleep method but want to see if there is a better one.

Thank You
Ash

arshad Mehmood

irashkin · April 3rd, 2007, 02:56 PM

a not very graceful, but easy alternative:

Dim nw As Date
Dim i as integer
nw = DateAdd("s", 3, Now())
While DateDiff("s", Now(), nw) > 1
i= i 'i.e. wait three seconds
Wend

heroadu · June 14th, 2007, 12:52 AM

hi, mate,

Why not just try "For loop structure", something like
For j = 1 To 10000000

Next j

it can be last for a while.
hope it can be helpful

FHankFreeman · February 13th, 2010, 11:42 AM

Quote:

Originally Posted by MrInterface

I use the following function:

Public Function fnWait(intNrOfSeconds As Integer)
Dim varStart As Variant
  varStart = Timer
  Do While Timer < varStart + intNrOfSeconds
  Loop
End Function

This works perfectly, when you are accessing a database via VBA on Excel and then you want to perform some calcutions in VBA right after the table is returned.

So to the author of the script, thank you very much.
FHanFreeman
13-Feb-2010.

SLeepdepD · December 18th, 2010, 12:43 PM

It's worth mentioning that looping with DoEvents in the middle is going to tie up your CPU, using the API method will not.

Code:

Private Declare Sub APISleep Lib "kernel32" Alias "Sleep" (ByVal lngMilliseconds As Long)

Public Function Sleep(lngSeconds As Long) As Boolean
  If lngSeconds > 0 Then
    Call APISleep(lngSeconds * 1000)

    Sleep = True
  End If

End Function

HiTechCoach · December 18th, 2010, 05:16 PM

Quote:

Originally Posted by heroadu

hi, mate,

Why not just try "For loop structure", something like
For j = 1 To 10000000

Next j

it can be last for a while.
hope it can be helpful

The problem with this approach is that it will not give you constant results for the amount of delay.

Back in the days of the first PC's ( old XT's) your method was a common practice. Then the 286 machines cam out. The processor were so much faster that the loops like you suggest we being performed very fast. Games were executing so fast the human could not keep up. You have to run a special program to script the processor to slow it down enough that a humans could keep up. Games had to be rewritten to pause based in the passing of time. Once this method was used games were still be playable on the newer and faster CPUs.