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

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

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) 

call waitfor(.005)

Sub WaitFor(NumOfSeconds As Single)
    Dim SngSec as Single
    SngSec=Timer + NumOfSeconds

    Do while timer < sngsec
        DoEvents
   Loop
End sub

source Timing Delays in VBA

I have try this and it works for me:

Private Sub DelayMs(ms As Long)
    Debug.Print TimeValue(Now)
    Application.Wait (Now + (ms * 0.00000001))
    Debug.Print TimeValue(Now)
End Sub

Private Sub test()
    Call DelayMs (2000)  'test code with delay of 2 seconds, see debug window
End Sub

Everyone tries Application.Wait, but that's not really reliable. If you ask it to wait for less than a second, you'll get anything between 0 and 1, but closer to 10 seconds. Here's a demonstration using a wait of 0.5 seconds:

Sub TestWait()
  Dim i As Long
  For i = 1 To 5
    Dim t As Double
    t = Timer
    Application.Wait Now + TimeValue("0:00:00") / 2
    Debug.Print Timer - t
  Next
End Sub

Here's the output, an average of 0.0015625 seconds:

0
0
0
0.0078125
0

Admittedly, Timer may not be the ideal way to measure these events, but you get the idea.

The Timer approach is better:

Sub TestTimer()
  Dim i As Long
  For i = 1 To 5
    Dim t As Double
    t = Timer
    Do Until Timer - t >= 0.5
      DoEvents
    Loop
    Debug.Print Timer - t
  Next
End Sub

And the results average is very close to 0.5 seconds:

0.5
0.5
0.5
0.5
0.5