CODEWITHSUNDEEP
functionvbatime
Code_Kid
Code_Kid

Reputation: 35

How to get 30 seconds delay

I want to generate a 30 seconds delay in vba. I can't use 'Timer' as my application will run overnight till next morning. (Timer will generate the seconds(in fraction) elapsed till midnight). Is there any other way to achieve this other than Timer and Application.WaitNow().
I tried using the following code but it throws Code execution has been interrupted error.

Function Wait()

Dim StartSecond
Dim EndSecond

StartSecond = Second(Now)
'Sheets("SampleOutputEx").Range("M2") = StartSecond

EndSecond = (StartSecond + 30) Mod 60                             
While Second(Now) <> EndSecond
  DoEvents
Wend

End Function

Upvotes: 0

Views: 1386

Answers (2)

Code_Kid
Code_Kid

Reputation: 35

Here we go. Never thought the answer could be very simple.

Function Wait()
Dim StartSecond As Single
Dim EndSecond
StartSecond = Now

EndSecond = DateAdd("s", 30, Now)

While Now < EndSecond
DoEvents
Wend

End Function

This works fine for me. Any comments regarding this are much appreciated.

Upvotes: 0

Murtuza Kabul
Murtuza Kabul

Reputation: 6514

Try this

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

Call this function whenever you want a sleep

Function Wait(Seconds as integer)

  Sleep Seconds*1000

End Function

You can also use 

expression.OnTime(When, Name, Tolerance)

and set the time to the next execution by adding 30 seconds to current time.

This article has a better explanation of it

Upvotes: 1

Related Questions