How to give a time delay of less than one second in excel vba?

Question

i want to repeat an event after a certain duration that is less than 1 second. I tried using the following code

Application.wait Now + TimeValue ("00:00:01") 

But here the minimum delay time is one second. How to give a delay of say half a seond?

Answer 1

You can use an API call and Sleep:

Put this at the top of your module:

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

Then you can call it in a procedure like this:

Sub test() Dim i As Long  For i = 1 To 10     Debug.Print Now()     Sleep 500    'wait 0.5 seconds Next i End Sub 

Answer 2

I found this on another site not sure if it works or not.

Application.Wait Now + 1/(24*60*60.0*2) 

the numerical value 1 = 1 day

1/24 is one hour

1/(24*60) is one minute

so 1/(24*60*60*2) is 1/2 second

You need to use a decimal point somewhere to force a floating point number

Source

Not sure if this will work worth a shot for milliseconds

Application.Wait (Now + 0.000001)