Application.wait... won't!

[Peter Rooney]

Good afternoon,

Can anyone explain why the following code doesn't take 30 seconds to run...
or when called from within another macro, doesn't delay macro execution by 30
seconds?

Thanks in advance

Sub HoldOnABit()

Dim NewHour As Integer
Dim NewMinute As Long
Dim NewSecond As Long
Dim WaitTime As Long

NewHour = Hour(Now())
NewMinute = Minute(Now())
NewSecond = Second(Now()) + 30
WaitTime = TimeSerial(NewHour, NewMinute, NewSecond)

Application.Wait WaitTime

End Sub


Pete

 

[Charlie]

Try:

Dim NewHour As Integer
Dim NewMinute As Integer
Dim NewSecond As Integer

NewHour = Hour(Now())
NewMinute = Minute(Now())
NewSecond = Second(Now()) + 30

Application.Wait TimeSerial(NewHour, NewMinute, NewSecond)

 

[Andrew B]

Hi Peter

I now use this method:

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

then anywhere a delay is required, put in this:

Sleep 1000 '1000 milliseconds = 1 second

For 30 seconds you would have

Sleep 30000

Hop this helps

Andrew Bourke

 

[Tom Ogilvy]

I would suspect that the help example you copied is flawed. to
demonstrate:

Sub BB()
newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 10
waittime = TimeSerial(newHour, newMinute, newSecond)
waittime1 = Now + TimeValue("0:00:10")
Debug.Print waittime, waittime1
Debug.Print CSng(waittime), CSng(waittime1)
End Sub

Produces:
7:51:28 AM 09/08/2005 7:51:28 AM
0.3274074 38603.33

Use this instead:

newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 10
waittime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait Date + waittime

or just

waittime1 = Now + TimeValue("0:00:10")
Application.Wait WaitTime1

 

[Charlie]

Hmm...even better is simply:

Application.Wait TimeSerial(Hour(Now()), Minute(Now()), Second(Now()) + 30)

 

[Peter Rooney]

Tom,

This is great, thanks!
I got my original code from Excel help!!!

One thing, when I declare a the WaitTime1 variable, the code only works
correctly if I give specify type Variant or Double. I know I can't use
integer or long, as they only wotk with integers, but do you have any idea
what's wrong with "single" in this instance?

Thanks

Pete

 

[Tom Ogilvy]

Sub BB()
Dim WaitTime1 As Single

' WaitTime1 = TimeSerial(0, 0, 10)
WaitTime1 = TimeValue("0:0:10")
Debug.Print Now, Now + WaitTime1
Application.Wait Now + WaitTime1
Debug.Print Now
End Sub

worked for me. Both using TimeSerial and TimeValue

 

[Peter Rooney]

Andrew,

This is neat - thanks a lot!

Regards

Pete

 

[Peter Rooney]

Charlie,

Thanks a lot for this - it works a treat!

Regards

Pete

 

[Peter Rooney]

Tom,

Strange - only Double works for me - if I specify single, no discernible
delay takes place. Anyway, as long as one way works (but if you're anything
like me, you don't like loose ends either!)

Cheers

Pete

 

[Jim Cone]

Hi Peter,

"Peter Rooney"
<[email protected]>
wrote in message

Looks like it depends on whether "Now" is included.
So...
'86400 seconds in a day
'30/86400 = 0.0003472
'10/86400 = 0.0001157

Dim WaitTime As Double
WaitTime = Now + 0.0001157
Application.Wait WaitTime

or

Dim WaitTime as Single
WaitTime = 0.00011157
Application.Wait Now + WaitTime

Regards,
Jim Cone
San Francisco, USA
'-----------------------------


Tom,
Strange - only Double works for me - if I specify single, no discernible
delay takes place. Anyway, as long as one way works (but if you're anything
like me, you don't like loose ends either!)
Cheers
Pete

 

[Peter Rooney]

Jim,

Eeh, by heck (as we say in Manchester), I wish I were clever!
Spot on answer - I can sleep tonight (after I've fed my whippets and racing
pigeons!)

Thanks

Pete

 

[jutlaux]

Where are you putting the delare bub statement? I try it in the declarations
section of "ThisWorkbook", but when I go to run a sub that has the sleep in
it I get a error "sub or function not defined"