VBA Loop every 200 milliseconds

jc0r

Board Regular
Hi all

Im trying to loop a macro every 200 milliseconds but am having trouble, just wondered if anyone could help. Here is my code

Code:
Sub Refresh()
Worksheets("Main Data").Range("AJ5:AJ11").Value = Worksheets("Main Data").Range("AI5:AI11").Value
Worksheets("Main Data").Range("AI5:AI11").Value = Worksheets("Main Data").Range("AH5:AH11").Value
Worksheets("Main Data").Range("AH5:AH11").Value = Worksheets("Main Data").Range("AG5:AG11").Value
Worksheets("Main Data").Range("AG5:AG11").Value = Worksheets("Main Data").Range("AF5:AF11").Value
Worksheets("Main Data").Range("AF5:AF11").Value = Worksheets("Main Data").Range("AE5:AE11").Value
Worksheets("Main Data").Range("AE5:AE11").Value = Worksheets("Main Data").Range("AD5:AD11").Value
Worksheets("Main Data").Range("AD5:AD11").Value = Worksheets("Main Data").Range("AC5:AC11").Value
Worksheets("Main Data").Range("AC5:AC11").Value = Worksheets("Main Data").Range("AB5:AB11").Value
Worksheets("Main Data").Range("AB5:AB11").Value = Worksheets("Main Data").Range("AA5:AA11").Value
Worksheets("Main Data").Range("AA5:AB11").Value = Worksheets("Main Data").Range("F5:F11").Value
Worksheets("Main Data").Range("AU5:AU11").Value = Worksheets("Main Data").Range("AT5:AT11").Value
Worksheets("Main Data").Range("AT5:AT11").Value = Worksheets("Main Data").Range("AS5:AS11").Value
Worksheets("Main Data").Range("AS5:AS11").Value = Worksheets("Main Data").Range("AR5:AR11").Value
Worksheets("Main Data").Range("AR5:AR11").Value = Worksheets("Main Data").Range("AQ5:AQ11").Value
Worksheets("Main Data").Range("AQ5:AQ11").Value = Worksheets("Main Data").Range("AP5:AP11").Value
Worksheets("Main Data").Range("AP5:AP11").Value = Worksheets("Main Data").Range("AO5:AO11").Value
Worksheets("Main Data").Range("AO5:AO11").Value = Worksheets("Main Data").Range("AN5:AN11").Value
Worksheets("Main Data").Range("AN5:AN11").Value = Worksheets("Main Data").Range("AM5:AM11").Value
Worksheets("Main Data").Range("AM5:AM11").Value = Worksheets("Main Data").Range("AL5:AL11").Value
Worksheets("Main Data").Range("AL5:AL11").Value = Worksheets("Main Data").Range("H5:F11").Value
End Sub
Many thanks
 
Last edited:

Jaafar Tribak

Well-known Member
Hi,

"... but I am having trouble" is not enough info... Have you tried placing the Refresh Macro in a Do DoEvents Loop ? What hapeens exactly ?
 

jc0r

Board Regular
Sorry i have managed to get it going, however Excel is not responsive while in the loop, im assuming because of the Sleep function. This is how far i have got now

Code:
Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)Sub Refresh()
Do While True:
Worksheets("Main Data").Range("AJ5:AJ11").Value = Worksheets("Main Data").Range("AI5:AI11").Value
Worksheets("Main Data").Range("AI5:AI11").Value = Worksheets("Main Data").Range("AH5:AH11").Value
Worksheets("Main Data").Range("AH5:AH11").Value = Worksheets("Main Data").Range("AG5:AG11").Value
Worksheets("Main Data").Range("AG5:AG11").Value = Worksheets("Main Data").Range("AF5:AF11").Value
Worksheets("Main Data").Range("AF5:AF11").Value = Worksheets("Main Data").Range("AE5:AE11").Value
Worksheets("Main Data").Range("AE5:AE11").Value = Worksheets("Main Data").Range("AD5:AD11").Value
Worksheets("Main Data").Range("AD5:AD11").Value = Worksheets("Main Data").Range("AC5:AC11").Value
Worksheets("Main Data").Range("AC5:AC11").Value = Worksheets("Main Data").Range("AB5:AB11").Value
Worksheets("Main Data").Range("AB5:AB11").Value = Worksheets("Main Data").Range("AA5:AA11").Value
Worksheets("Main Data").Range("AA5:AB11").Value = Worksheets("Main Data").Range("F5:F11").Value
Worksheets("Main Data").Range("AU5:AU11").Value = Worksheets("Main Data").Range("AT5:AT11").Value
Worksheets("Main Data").Range("AT5:AT11").Value = Worksheets("Main Data").Range("AS5:AS11").Value
Worksheets("Main Data").Range("AS5:AS11").Value = Worksheets("Main Data").Range("AR5:AR11").Value
Worksheets("Main Data").Range("AR5:AR11").Value = Worksheets("Main Data").Range("AQ5:AQ11").Value
Worksheets("Main Data").Range("AQ5:AQ11").Value = Worksheets("Main Data").Range("AP5:AP11").Value
Worksheets("Main Data").Range("AP5:AP11").Value = Worksheets("Main Data").Range("AO5:AO11").Value
Worksheets("Main Data").Range("AO5:AO11").Value = Worksheets("Main Data").Range("AN5:AN11").Value
Worksheets("Main Data").Range("AN5:AN11").Value = Worksheets("Main Data").Range("AM5:AM11").Value
Worksheets("Main Data").Range("AM5:AM11").Value = Worksheets("Main Data").Range("AL5:AL11").Value
Worksheets("Main Data").Range("AL5:AL11").Value = Worksheets("Main Data").Range("H5:F11").Value
Sleep 200
Loop


End Sub
 
Last edited:

Jaafar Tribak

Well-known Member
You could use the smoother method with the SetTimer API instead of with DoEvents\Sleep.

Make the Refresh Macro Public (So it can be seen by the Timer Callback ) and put the following code In a seperate Standard Module:
Code:
Option Explicit

[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=If]#If[/URL]  VBA7 Then
    Declare PtrSafe Function KillTimer Lib "user32" (ByVal hWnd As LongPtr, ByVal nIDEvent As LongPtr) As Long
    Declare PtrSafe Function SetTimer Lib "user32" (ByVal hWnd As LongPtr, ByVal nIDEvent As LongPtr, ByVal uElapse As Long, ByVal lpTimerFunc As LongPtr) As LongPtr
[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=Else]#Else[/URL] 
    Declare Function KillTimer Lib "user32" (ByVal hWnd As Long, ByVal nIDEvent As Long) As Long
    Declare Function SetTimer Lib "user32" (ByVal hWnd As Long, ByVal nIDEvent As Long, ByVal uElapse As Long, ByVal lpTimerFunc As Long) As Long
[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=End]#End[/URL]  If

Const lTimerInterval As Long = 200  [B][COLOR=#008000]'in mseconds[/COLOR][/B]

Public Sub StartRefreshing()
    SetTimer Application.hWnd, 0, lTimerInterval, AddressOf TimerProc
End Sub

Public Sub StopRefreshing()
    KillTimer Application.hWnd, 0
End Sub

Sub TimerProc()
    On Error Resume Next
    Call Refresh
End Sub
Caution:
Do not forget to stop the API timer when done !!

Maybe you should add a call to the StopRefreshing routine in the workbook Before Close event just as a precaution:
Code:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Call StopRefreshing
End Sub
 

jc0r

Board Regular
This is fantastic. I managed to get DoEvents going but Excel becomes sluggish. This is a much better approach.
Many thanks for your help!
 

offthelip

Well-known Member
you could speed up the copy by doing all the columsn at once eg:
replace:
Code:
Worksheets("Main Data").Range("AI5:AI11").Value = Worksheets("Main Data").Range("AH5:AH11").Value
Worksheets("Main Data").Range("AH5:AH11").Value = Worksheets("Main Data").Range("AG5:AG11").Value
Worksheets("Main Data").Range("AG5:AG11").Value = Worksheets("Main Data").Range("AF5:AF11").Value
Worksheets("Main Data").Range("AF5:AF11").Value = Worksheets("Main Data").Range("AE5:AE11").Value
Worksheets("Main Data").Range("AE5:AE11").Value = Worksheets("Main Data").Range("AD5:AD11").Value
Worksheets("Main Data").Range("AD5:AD11").Value = Worksheets("Main Data").Range("AC5:AC11").Value
Worksheets("Main Data").Range("AC5:AC11").Value = Worksheets("Main Data").Range("AB5:AB11").Value
Worksheets("Main Data").Range("AB5:AB11").Value = Worksheets("Main Data").Range("AA5:AA11").Value
with




Code:
inarr = Worksheets("Main Data").Range("AA5:AH11").Value
Worksheets("Main Data").Range("AB5:AI11") = inarr
 

jc0r

Board Regular
you could speed up the copy by doing all the columsn at once eg:
replace:
Code:
Worksheets("Main Data").Range("AI5:AI11").Value = Worksheets("Main Data").Range("AH5:AH11").Value
Worksheets("Main Data").Range("AH5:AH11").Value = Worksheets("Main Data").Range("AG5:AG11").Value
Worksheets("Main Data").Range("AG5:AG11").Value = Worksheets("Main Data").Range("AF5:AF11").Value
Worksheets("Main Data").Range("AF5:AF11").Value = Worksheets("Main Data").Range("AE5:AE11").Value
Worksheets("Main Data").Range("AE5:AE11").Value = Worksheets("Main Data").Range("AD5:AD11").Value
Worksheets("Main Data").Range("AD5:AD11").Value = Worksheets("Main Data").Range("AC5:AC11").Value
Worksheets("Main Data").Range("AC5:AC11").Value = Worksheets("Main Data").Range("AB5:AB11").Value
Worksheets("Main Data").Range("AB5:AB11").Value = Worksheets("Main Data").Range("AA5:AA11").Value
with




Code:
inarr = Worksheets("Main Data").Range("AA5:AH11").Value
Worksheets("Main Data").Range("AB5:AI11") = inarr
Thankyou for the suggestion offthelip but i don't think would work as the use of this is that it works backwards. So i can see the refresh of data every 200 milliseconds for the last 2 seconds.
 

Some videos you may like

This Week's Hot Topics

Top