Application.wait... won't!

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
>
>

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
>
>

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" <PeterRooney@discussions.microsoft.com> wrote in message
news:DBC8BFE4-58AB-499B-B0B2-3D2C4F4E602F@microsoft.com...
> 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" <PeterRooney@discussions.microsoft.com> wrote in message
> news:DBC8BFE4-58AB-499B-B0B2-3D2C4F4E602F@microsoft.com...
> > 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" <PeterRooney@discussions.microsoft.com> wrote in message
news:0BB2AAA2-9299-485A-8024-4D5A7B3DD996@microsoft.com...
> 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" <PeterRooney@discussions.microsoft.com> wrote in message
> > news:DBC8BFE4-58AB-499B-B0B2-3D2C4F4E602F@microsoft.com...
> > > 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
> >
> >
>

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
> > >
> > >

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" <PeterRooney@discussions.microsoft.com> wrote in message
> news:0BB2AAA2-9299-485A-8024-4D5A7B3DD996@microsoft.com...
> > 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" <PeterRooney@discussions.microsoft.com> wrote in message
> > > news:DBC8BFE4-58AB-499B-B0B2-3D2C4F4E602F@microsoft.com...
> > > > 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"
<PeterRooney@discussions.microsoft.com>
wrote in message
news:1D201E91-52ED-407B-BF97-FC9D817E32DA@microsoft.com...

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" <PeterRooney@discussions.microsoft.com> wrote in message
> news:0BB2AAA2-9299-485A-8024-4D5A7B3DD996@microsoft.com...
> > Tom,

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


"Jim Cone" wrote:

> Hi Peter,
>
> "Peter Rooney"
> <PeterRooney@discussions.microsoft.com>
> wrote in message
> news:1D201E91-52ED-407B-BF97-FC9D817E32DA@microsoft.com...
>
> 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" <PeterRooney@discussions.microsoft.com> wrote in message
> > news:0BB2AAA2-9299-485A-8024-4D5A7B3DD996@microsoft.com...
> > > Tom,
>
>