Application.wat for less than 1 second

spurs

Active Member
Joined
Oct 18, 2006
Messages
479
Office Version
  1. 2016
  2. 2013
  3. 2010
  4. 2007
  5. 2003 or older
Platform
  1. Windows
The format for having an application wait (delay) for 1 second is

Application.Wait (Now + TimeValue("0:00:01"))


how would you handle a situation of 0.2 seconds? or do you need a completely different method for under 1 second?
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Hi, you could try:

Code:
Application.Wait (Now + (TimeValue("0:00:01") / 5))
 
Upvote 0
Application.Wait (Now + (TimeValue("0:00:01") / 5))
Great idea

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).
 
Last edited:
Upvote 0
This seems to work:

Code:
Sub test()
  Dim f             As Single
  Dim i             As Long

  For i = 1 To 10
    f = Timer
    WaitASec 0.2
    Debug.Print Format(Timer - f, "0.000")
    DoEvents
  Next i
End Sub

Sub WaitASec(sec As Double)
  Application.Wait CDbl(Now) + ((256# * Timer Mod 256) / 256# + sec) / 86400#
End Sub
 
Upvote 0
Sub WaitASec(sec As Double)
Application.Wait CDbl(Now) + ((256# * Timer Mod 256) / 256# + sec) / 86400#
End Sub[/CODE]

If we ignore the midnight problem [1], as you do, the simplest implementation of my suggestion is:

Application.Wait CDbl(Date) + (Timer + sec)/86400

I thought that was "obvious". Sorry if it is wasn't.


-----
[1] The "midnight problem" is: the midnight system clock "tick" occurs between the calls to Now (or Date) and Timer. I see I never explained it; but I alluded to it.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top