"Delay" function in Excel

[Luca T.]

Hello,
i would like to know if there is a way to have a (non-freezing, it has
to NOT freeze Excel) delay function in Excel (beside the loop with
DoEvents).

The problem is that i have a Macro that must "sleep" for 1 second, then
run, then sleep for 1 second, then run, and so on... but with a DoEvents
loop between each execution the CPU use is ALWAYS 100%.

Is there a not so CPU-hungry way to do it?

Thank you,
Luca

 

[Guest]

hi,
pause a macro's excecution for a while.
Application.Wait (Now() + TimeValue("0:00:01"))

 

[GJones]

Hi Luca;

This is a copy of the VBA help for the Timer method. You
should be able to combine it with DoEvents to get what you
want.

Timer Function Example
This example uses the Timer function to pause the
application. The example also uses DoEvents to yield to
other processes during the pause.

Dim PauseTime, Start, Finish, TotalTime
If (MsgBox("Press Yes to pause for 5 seconds", 4)) = vbYes
Then
PauseTime = 5 ' Set duration.
Start = Timer ' Set start time.
Do While Timer < Start + PauseTime
DoEvents ' Yield to other processes.
Loop
Finish = Timer ' Set end time.
TotalTime = Finish - Start ' Calculate total time.
MsgBox "Paused for " & TotalTime & " seconds"
Else
End
End If





Thanks,

Greg