VBA Loop every 200 milliseconds

J

jc0r

Board Regular
Joined
Mar 16, 2009
Messages
124
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:

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
Sort by date Sort by votes
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 ?
 
Upvote 0
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:
Upvote 0
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
 
Upvote 0
This is fantastic. I managed to get DoEvents going but Excel becomes sluggish. This is a much better approach.
Many thanks for your help!
 
Upvote 0
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
 
Upvote 0
offthelip said:
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
Click to expand...

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.
 
Upvote 0
Try I think you will find it does exactly the same as your code
 
Upvote 0
You must log in or register to reply here.

Similar threads

W
  • Question Question
Trying to learn how to loop code
Replies
1
Views
238
mumps
mumps
V
  • Solved
Average Function
Replies
8
Views
395
Peter_SSs
Peter_SSs
JenniferMurphy
  • Question Question
Is there a Null function?
Replies
6
Views
531
JenniferMurphy
JenniferMurphy
B
  • Question Question
Color Rows with Value in it, in all Columns, based on Color of Value in Dropdown List (dynamic)
Replies
5
Views
463
Fluff
Fluff
J
  • Question Question
Alternative method fo simple function?
Replies
5
Views
980
jc0r
J

Forum statistics

Threads
1,224,820
Messages
6,181,159
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top