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

 

[Fennek]

Hi,

try the "sleep"-function:

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

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

 

[bongobus]

Hi,

try the "sleep"-function:

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

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

only works once ?

 

[bongobus]

only works once ?

all I want is a refresh every 2 seconds as this spreadsheet is a display only

another spreadsheet is maintained

 

[bongobus]

all I want is a refresh every 2 seconds as this spreadsheet is a display only

another spreadsheet is maintained

Bump

 

[joeu2004]

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

A Sleep loop would never leave VBA. Perhaps you want the following in a normal module (not a worksheet module):

Private nextTime As Date

Sub startit()
nextTime = Now + TimeSerial(0,0,2), "repeatit"
Application.OnTime nextTime, "repeatit"
end Sub

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

Sub stoptit
On Error Resume Next
Application.OnTime nextTime, "repeatit",, False
End Sub

To avoid having to remember to execute "stopit" manually, you also might want to put the following into the ThisWorkbook module:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
stopit
End Sub

 

[bongobus]

A Sleep loop would never leave VBA. Perhaps you want the following in a normal module (not a worksheet module):

Private nextTime As Date

Sub startit()
nextTime = Now + TimeSerial(0,0,2), "repeatit"
Application.OnTime nextTime, "repeatit"
end Sub

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

Sub stoptit
On Error Resume Next
Application.OnTime nextTime, "repeatit",, False
End Sub

To avoid having to remember to execute "stopit" manually, you also might want to put the following into the ThisWorkbook module:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
stopit
End Sub

hi still not working and in the vba editor these lines are red font

nextTime = Now + TimeSerial(0,0,2), "repeatit"

 

[bongobus]

I guess what I'm trying to achieve is this

Calculate
Application.Wait (Now + TimeValue("0:00:05"))
Calculate
Application.Wait (Now + TimeValue("0:00:05"))
Calculate
Application.Wait (Now + TimeValue("0:00:05"))
Calculate
Application.Wait (Now + TimeValue("0:00:05"))
Calculate
Application.Wait (Now + TimeValue("0:00:05"))
Calculate


but not needing so many lines hahahaha

also the ability to pause the code by clicking a shape then after so many seconds it reverts to the refresh again

 

[joeu2004]

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

An obvious last-minute copy-and-paste error. The lines should read:

nextTime = Now + TimeSerial(0,0,2)

 

[joeu2004]

Calculate
Application.Wait (Now + TimeValue("0:00:05"))
Calculate
Application.Wait (Now + TimeValue("0:00:05"))
[....]
but not needing so many lines hahahaha

Again, like Sleep, that stays in VBA all of the time. It does not allow you to use Excel while waiting; and Excel is not executing in the background.

Use my Application.Ontime paradigm, simply changing TimeSerial(0,0,2) to TimeSerial(0,0,5) or whatever you finally settle on.

BTW, there is reason to use TimeValue("0:00:05") instead of TimeSerial(0,0,5). The latter is more efficient. It is also localization-independent; some regions do not use ":" as the time separator. But feel free to use TimeValue, if your enamored with it.

Also, if are content with Calculate then the 5-sec delay (not exactly what you wrong initially), you do not need my "startit" subroutine. Just use my "repeatit" subroutine.

also the ability to pause the code by clicking a shape then after so many seconds it reverts to the refresh again

Just another Application.Ontime subroutine; or use the "stopit" and "startit" subroutines that I provided.

As for assigning a "shape" to click on, click Developer > Insert > Form Controls > Button.

If you do not see the Developer tab in your Ribbon, right-click any tab in your Ribbon (e.g. File) and click Customize Ribbon.