CODEWITHSUNDEEP
excelwaitvba
nikhil patil
nikhil patil

Reputation: 142

Application.wait in VBA

I want to add wait time of 1 second in my Application.

What I found online? 

newHour = Hour(Now())
   newMinute = Minute(Now())
   newSecond = Second(Now()) + 1
   waitTime = TimeSerial(newHour, newMinute, newSecond)
   Application.Wait waitTime

Here is my code 

Sub workwithdelay()
Dim i As Integer
Dim done As Integer
Dim newHour As Integer
Dim newMinute As Integer
Dim newSecond As Integer
Dim waitTime As String
Dim ws As Worksheet


Set ws = ActiveSheet

frame = Cells(1, "A").Value


For i = 1 To done

newHour = Hour(Now)
newMinute = Minute(Now)
newSecond = second(Now) + 1
waitTime = newHour: newMinute: newSecond

(ALL of work Code)

Application.Wait waitTime

Next i

End Sub

The code takes 0.30 seconds to 0.45 seconds to complete one cycle in for loop(not including wait time) depending on the processing of data. I want to add a definite 1 second to complete one for loop cycle no matter what the processing time of the loop is.

Upvotes: 0

Views: 5916

Answers (2)

Michael S Priz
Michael S Priz

Reputation: 1126

You have the right idea, but I think what you want is the TimeValue function (documented here).

You can use it like this:

Dim StartTime as Date
Dim EndTime as Date

StartTime =  Now
EndTime = StartTime + TimeValue("00:00:01")
' Do Stuff
Application.Wait EndTime

This will cause the application to wait until one second after the start of the Do Stuff section, which I think is what you are asking for.

Hope this helps :)

Upvotes: 1

Kazimierz Jawor
Kazimierz Jawor

Reputation: 19067

You need to use different technique with do...loop + timer.

Sub WaitOneSecond()

    Dim Start as single
        start = timer
    do while start + 1 > timer
       doevents
    loop
end sub

and in your original sub call WaitOnSecond where necessary:

Sub workwithdelay()
(...)

For i = 1 To done
(...)
call WaitOnSecond

Next i

End Sub

Some information to above:

  1. timer counts seconds and milliseconds from midnights of today
  2. be careful with my code run just before midnight- it is not correct when new day starts
  3. application.wait doesn't recognise milliseconds therefore you are not able to get exact 1 second waiting time

Upvotes: 1

Related Questions