Application.Wait Not Working ???

[Alan]

I have the following code for pausing before trying an Internet
connection (one of a number of them). However, it just hangs there.
I am using Windows XP and Excel 2003.

Sub WaitSeconds(seconds As Integer)
'
' This subroutine waits for a specified number of seconds.
'
Dim CurrentHour As Integer, CurrentMinute As Integer,
CurrentSecond As Integer
Dim newHour As Integer, newMinute As Integer, newSecond As Integer
Dim waitTime As Integer, CurrentTime As Integer
CurrentHour = Hour(Now())
CurrentMinute = Minute(Now())
CurrentSecond = Second(Now())
newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + seconds
waitTime = TimeSerial(newHour, newMinute, newSecond)
Debug.Print "Current time = " & CurrentHour & ":" & CurrentMinute
& _
":" & CurrentSecond
Debug.Print "Waiting until " & newHour & ":" & newMinute & _
":" & newSecond
Application.Wait waitTime
End Sub

Any ideas why it would do this??

I call the subroutine above like this when it hangs:

WaitSeconds (2)

Thanks, Alan

 

[Mike H]

Alan,

Try this

Sub WaitSeconds(seconds As Integer)
' This subroutine waits for a specified number of seconds.
'
Dim CurrentHour As Integer, CurrentMinute As Integer, CurrentSecond As
Integer
Dim newHour As Integer, newMinute As Integer, newSecond As Integer
Dim waitTime As Variant, CurrentTime As Long
CurrentHour = Hour(Now())
CurrentMinute = Minute(Now())
CurrentSecond = Second(Now())
newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + seconds
waitTime = TimeSerial(newHour, newMinute, newSecond)

Debug.Print "Current time = " & CurrentHour & ":" & CurrentMinute & _
":" & CurrentSecond
Debug.Print "Waiting until " & newHour & ":" & newMinute & _
":" & newSecond
Application.Wait waitTime
Debug.Print Now

End Sub

Mike

 

[Bernie Deitrick]

Use actual date time values

Sub WaitSeconds(Seconds As Integer)
' This subroutine waits for a specified number of seconds.
'
Dim CurrentTime As Date
Dim FinishTime As Date
CurrentTime = Now
FinishTime = Now + Seconds / 24 / 60 / 60

Debug.Print "Current time = " & Format(CurrentTime, "mmm dd, yyyy hh:mm:ss")
Debug.Print "Waiting until " & Format(FinishTime, "mmm dd, yyyy hh:mm:ss")
Application.Wait FinishTime
Debug.Print "I have finished waiting at " & Format(Now, "mmm dd, yyyy hh:mm:ss")

End Sub


HTH,
Bernie
MS Excel MVP