.wait for 1/2 a second

G

Guest

Thanks for taking the time to read my question.

Is is possible to wait for less than a full second?

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

Thanks,

Brad
 
V

Vasant Nanavati

Hi Tom:

I didn't think it would work using Wait either, but when I experimented with
it I was surprised to find that it seemed to work reasonably well.

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

Regards,

Vasant
 
V

Vasant Nanavati

Hi Tom:

Yes, I did test it. As I said before, I was surprised.

I think the discussions you are pointing to relate to OnTime and not Wait.

Regards,

Vasant
 
T

Tom Ogilvy

Sub AABC()
Dim sngStart As Single
Dim i As Long
sngStart = Timer
Debug.Print sngStart
For i = 1 To 30
Debug.Print Timer
Application.Wait Now + (TimeValue("00:00:01") / 2)
Next
Debug.Print Timer
Debug.Print Format(Timer - sngStart, "0.0")
End Sub

Without the / 2, it printed off times about a second appart and an elapsed
time of 29.2 seconds.

with the / 2 it went through instantly. All times were the same.

So for me, I didn't have the same happy results you had. Maybe you can
critique my test. I must have done something wrong.
 
B

Bernie Deitrick

Vasant,

Since the Wait method takes an Excel Date Time, I don't think it can compare
to decimal seconds. It depends on the rounded values of the current time and
expected elapsed time, and could execute anywhere between 0 and 1 second
later. It's hard to predict, but if you have a high resolution timer, then
you could test it better:

Dim oTimer As New CHiResTimer
Sub test()
oTimer.StartTimer
Application.Wait Now + (TimeValue("00:00:01") / 2)
oTimer.StopTimer
MsgBox "That took " & Format(oTimer.Elapsed, "#.000000") & " seconds."

oTimer.StartTimer
Application.Wait Now + (TimeValue("00:00:01"))
oTimer.StopTimer
MsgBox "That took " & Format(oTimer.Elapsed, "#.000000") & " seconds."

oTimer.StartTimer
Application.Wait Now + (TimeValue("00:00:02"))
oTimer.StopTimer
MsgBox "That took " & Format(oTimer.Elapsed, "#.000000") & " seconds."

End Sub

Put the code below into a class module named CHiResTimer, and the macro
above will show the time differences.

HTH,
Bernie
MS Excel MVP


Option Explicit

'How many times per second is the counter updated?
Private Declare Function QueryFrequency Lib "kernel32" _
Alias "QueryPerformanceFrequency"
( _
lpFrequency As Currency) As Long

'What is the counter's value
Private Declare Function QueryCounter Lib "kernel32" _
Alias "QueryPerformanceCounter" ( _
lpPerformanceCount As Currency) As
Long

'Variables to store the counter information
Dim cFrequency As Currency
Dim cOverhead As Currency
Dim cStarted As Currency
Dim cStopped As Currency


Private Sub Class_Initialize()
Dim cCount1 As Currency, cCount2 As Currency

'Get the counter frequency
QueryFrequency cFrequency

'Call the hi-res counter twice, to check how long it takes
QueryCounter cCount1
QueryCounter cCount2

'Store the call overhead
cOverhead = cCount2 - cCount1

End Sub

Public Sub StartTimer()
'Get the time that we started
QueryCounter cStarted
End Sub

Public Sub StopTimer()
'Get the time that we stopped
QueryCounter cStopped
End Sub

Public Property Get Elapsed() As Double

Dim cTimer As Currency

'Have we stopped or not?
If cStopped = 0 Then
QueryCounter cTimer
Else
cTimer = cStopped
End If

'If we have a frequency, return the duration, in seconds
If cFrequency > 0 Then
Elapsed = (cTimer - cStarted - cOverhead) / cFrequency
End If

End Property
 
B

Bernie Deitrick

Tom's macro shows the limitation much better - no big surprise there. Also,
I should have been clearer: even a 1 second "wait" can be anything between 0
and 1 second.

HTH,
Bernie
MS Excel MVP
 
T

Tom Ogilvy

See the other leg of the discussion, but I would be interested to see what
you used. I always reserve the right to be wrong.

Also, I didn't accuse you of not testing, I asked what your test showed. I
don't think I used the term democrate. <g>
 
V

Vasant Nanavati

Hi Tom:

I would never presume to critique anything you say <g>.

In the limited case of the OP's question, I tried:

Sub Test()
UserForm1.Show
End Sub

Sub UserForm_Activate()
Application.Wait Now + TimeValue("00:00:00") / 2
Unload Me
End Sub

Since there was an apparent appreciable reduction in the time that the form
stayed visible when dividing the Wait period by 2, I unwisely assumed that
the strategy was working. Of course, you are entirely correct and the wait
period seems to be either 1 second or 0 seconds with nothing in between.

Regards,

Vasant
 
V

Vasant Nanavati

Hi Bernie:

Yes, you and Tom are correct and I am wrong ... no big surprise there either
<vbg>. I think my months of being missing in action have made me a bit
rusty.

Regards,

Vasant
 
T

Tom Ogilvy

Vasant, Bernie,

I found the results using Bernie's (using exact posted code) to be erratic
even at the 1 second wait ( although the 1/2 never even approached 1/2 of a
second - more like .0003 or less). So I added loops on each and averaged
the results. (see results below)

Dim oTimer As New CHiResTimer
Sub test()
oTimer.StartTimer
For i = 1 To 100
Application.Wait Now + (TimeValue("00:00:01") / 2)
Next i
oTimer.StopTimer
Debug.Print "That took (1/2 sec): " & Format(oTimer.Elapsed / 100,
"#.000000") & " seconds."

oTimer.StartTimer
For i = 1 To 100
Application.Wait Now + (TimeValue("00:00:01"))
Next
oTimer.StopTimer
Debug.Print "That took (1 sec): " & Format(oTimer.Elapsed / 100, "#.000000")
& " seconds."

oTimer.StartTimer
For i = 1 To 100
Application.Wait Now + (TimeValue("00:00:02"))
Next
oTimer.StopTimer
Debug.Print "That took (2 sec): " & Format(oTimer.Elapsed / 100, "#.000000")
& " seconds."

End Sub


RESULTS:
That took (1/2 sec): .000038 seconds.
That took (1 sec): .999923 seconds.
That took (2 sec): 2.001930 seconds.

Essentially, with the the divide by 2 to achieve 1/2 second, there was no
wait. So I would conclude it doesn't work. Same results as with my less
precise timer routine.

Windows XP, xl2003
 
B

Bernie Deitrick

Tom,

Given your testing technique, the average wait for integer seconds will be
close to the desired, because you start the next wait immediately. With the
first wait, however, it can be much shorter, since Excel is simply looking
for the seconds value to roll over. For example, if a 1 second wait starts
at X.95 seconds, then Excel will get to X + 1 within .05 seconds, and the
"wait" will be much shorter than expected.

HTH,
Bernie
MS Excel MVP
 
T

Tim Zych

Here's another way, with much greater time control because the parameter
represents milliseconds.

' Must be declared at the top of a module.
Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

Sub TryIt()
Sleep 500
End Sub
 
M

Michel Pierron

Hi Brad,
Why not simply:

Dim T As Single
T = Timer + 1 / 2
While T > Timer: Wend

MP
 
G

Guest

Michael,

When I saw this post, looked up the Timer function since I'd never seen it
before. It appears that you are correct, but only for Windows based
machines. The help file for Timer states "In Microsoft Windows the Timer
function returns fractional portions of a second. On the Macintosh, timer
resolution is one second."

FYI for those Mac users out there.

I think Sleep is the choice to use when you want to pause for less than a
second.

Mike
 
T

Tushar Mehta

FYI for those Mac users out there.

I think Sleep is the choice to use when you want to pause for less than a
second.
Click to expand...
I realize I am jumping into a discussion I haven't read from the
beginning, but...

Isn't Sleep a *Windows* API?

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions
 
G

Guest

Tushar,

Well, I guess you do have a point there. <g> See what happens when you
type before you think? I guess you Mac folks just have to live with seconds.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Running SPSS-Syntax using Excel VBA (Excel 2013) 0
Loop +2, +4, etc. help 5
Wait less than a second 3
automation error 0
Application.Wait 2
Macro problems: Waiting for site to load and commanding Save todownload prompt 5
Excel VBA to go to specific sheet & cell in the current workbook not working. 2
Please help with Application.Wait ( ) 2

Top