Time Loop

[bongobus]

I would like a looped code that waits 2 seconds then refreshes the links e.g calculate then waits 2 seconds then refreshes - basically a loop

 

[joeu2004]

still not working and in the vba editor these lines are red font
nextTime = Now + TimeSerial(0,0,2), "repeatit"

FYI, finally got around to testing. Works as __I__ intend it with the correction of the obvious copy-and-paste error above. Whether it works as you intend it might be a different story.

Here's the test code. Red lines are part of the test; you would not include them. It's a little tricky to execute "stopit" within 2 seconds of clicking OK for the Msgbox. I do it in two steps.

Private nextTime As Date
Private lastTime As Date

Sub startit()
MsgBox Now & "    startit"
lastTime = Now
nextTime = Now + TimeSerial(0, 0, 2)
Application.OnTime nextTime, "repeatit"
End Sub

Sub repeatit()
MsgBox lastTime & vbNewLine & Now & "    repeatit"
' do whatever you need here to "refresh the links"; perhaps:
' Application.Calculate
' then...
lastTime = Now
nextTime = Now + TimeSerial(0, 0, 2)
Application.OnTime nextTime, "repeatit"
End Sub

Sub stoptit()
On Error Resume Next
MsgBox lastTime & vbNewLine & Now & "    stopit"
Application.OnTime nextTime, "repeatit", , False
End Sub

 

[Fennek]

Hi,

is the problem to build a loop?

Private Declare PtrSafe Sub Sleep Lib "kernel32.dll" ( _
    ByVal dwMilliseconds As Long)

Public Sub mySleep()
do   
'your code
            Call Sleep(2000) '2000 Millisekunden Pause
loop
End Sub

The application.wait should work as well.

regards

 

[bongobus]

Hi,

is the problem to build a loop?

Private Declare PtrSafe Sub Sleep Lib "kernel32.dll" ( _
    ByVal dwMilliseconds As Long)

Public Sub mySleep()
do   
'your code
            Call Sleep(2000) '2000 Millisekunden Pause
loop
End Sub

The application.wait should work as well.

regards

Hi fennek - thanks this seems to work as I see the calculate work - I start the code by a shortcut ctrl+z

the way I can exit the code is pressing escape then end !

 

[bongobus]

Hi fennek - thanks this seems to work as I see the calculate work - I start the code by a shortcut ctrl+z

the way I can exit the code is pressing escape then end !

hi is their an alternative that does the above however only when not using - so pauses for you to edit some cells then if not using excel executes the code again until next edit?