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

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

Peter Rooney wrote:
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

Andrew,

This is neat - thanks a lot!

Regards

Pete



"Andrew B" wrote:

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

Peter Rooney wrote:
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

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"

"Andrew B" wrote:

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

Peter Rooney wrote:
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

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)


"Peter Rooney" wrote:

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

Hmm...even better is simply:

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


"Charlie" wrote:

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)


"Peter Rooney" wrote:

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,

Thanks a lot for this - it works a treat!

Regards

Pete



"Charlie" wrote:

Hmm...even better is simply:

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


"Charlie" wrote:

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)


"Peter Rooney" wrote:

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

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

--
Regards,
Tom Ogilvy


"Peter Rooney" wrote in message
...
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

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" wrote:

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

--
Regards,
Tom Ogilvy


"Peter Rooney" wrote in message
...
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

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

--
Regards,
Tom Ogilvy

"Peter Rooney" wrote in message
...
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" wrote:

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

--
Regards,
Tom Ogilvy


"Peter Rooney" wrote in message
...
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

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



"Tom Ogilvy" wrote:

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

--
Regards,
Tom Ogilvy

"Peter Rooney" wrote in message
...
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" wrote:

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

--
Regards,
Tom Ogilvy


"Peter Rooney" wrote in message
...
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

Hi Peter,

"Peter Rooney"

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

"Tom Ogilvy" wrote:
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
--
Regards,
Tom Ogilvy

"Peter Rooney" wrote in message
...
Tom,