Application.Wait (Now + (TimeValue("0:00:01") / 5))
Not really. If you had tested, you would find that it does not wait at all.
The following code is a reliable way to wait for fractional seconds.
Rich (BB code):
Sub test()
Dim et As Single
Dim st As Single, d As Double
d = Date: st = Timer
If d <> Date Then d = Date: st = Timer ' at midnight
Application.Wait d + (st + 0.2) / 86400
et = Timer
MsgBox Format(CDbl(et - st), "0.000000")
End Sub
As noted, the statement "If d <> Date..." is needed only if you might run the code
at midnight.
In many circumstances, "d" is not needed at all. But it is prudent to have in case: (1) the delay might
span midnight; and (2) system interrupts might cause a longer delay, especially when the delay is 0.031 sec or less.
-----
The following demonstrates that Now+TimeValue("0:0:1")/5 does not work.
Rich (BB code):
Sub test()
Dim st As Single, et As Single, x As Double
x = Time: Do: Loop Until Time <> x ' wait for 1-sec epoch
st = Timer
Application.Wait Now + TimeValue("0:0:01") / 5
et = Timer
MsgBox Format(CDbl(et - st), "0.000000")
End Sub
Sometimes, the elapsed time is about 0.016 sec. But that is because a system clock "tick" occurred (every 0.015625 sec), not because of Application.Wait.
Now+TimeValue("0:0:1")/5 does not work for two reasons.
First, Now is Date+Time, and Time is rounded down to the second.
So, for example, if the current time is 1:02:03
.5, Now+TimeValue("0:0:1")/5 is 1:02:03
.2. But since that is before the current time, Application.Wait returns immediately (when Date is included in the wait time.)
Second, even if the current time is exactly 1:02:03
.0, for example, as the test above ensures, Application.Wait returns immediately even though Now+TimeValue("0:0:1")/5 is 1:02:03
.2.
The reason is more difficult to demonstrate. It is an anomaly of type Date, which is why I use type Double instead.
When we use a type Date expression,
sometimes VBA evaluates it as date and time, where time is again rounded down to the second. But the operative word is "sometimes". For example:
Rich (BB code):
Sub test()
Dim x As Date
x = Now + TimeValue("0:0:1") / 5
MsgBox x & vbNewLine & Format((x - Int(x)) * 86400, "0.000")
End Sub
MsgBox displays:
6/13/2015 11:54:56 AM
42896.200
The first line shows that VBA substituted date and time rounded down to the second. I believe it does that in the Application.Wait statement, as well.
So the wait time is always the current time or earlier, which causes Application.Wait to return immediately.
But the second line shows that VBA actually does add 0.2 to the (rounded down) time of day, represented as seconds since midnight.
So in some expressions like (x-Int(x))*86400, VBA substitutes the actual date and time to the fractional second.
A work-around to the second problem is to write CDbl(Now+TimeValue("0:0:1")/5) in the Application.Wait statement.
But recall the first problem: Application.Wait still does not wait the required time (0.2 sec), except when current time is exactly "on the second"; for example, 1:02:03
.0.
For a delay of 0.2, that is
not the case more than 81% of the time.
-----
FYI, I would not use TimeValue("0:0:1") in this context. TimeSerial(0,0,1) is faster. The constant #0:0:1# is even faster.
(VBA displays #0:0:1# as #12:00:01 AM#.)
But 0.2/86400 is better than #0:0:1#/5, IMHO. The latter is difficult to generalize (e.g. 0.3 sec, not 1/3 sec) and to parameterize (e.g. a variable delay).