Refresh data in spreadsheet

[Lun]

I want to use VBA to refresh data in the spreadsheet periodically. I've tried following codes, it refresh the cell A1 every 2 second. However, within these 2 seconds, I can't use the spreadsheet. Even I have used "DoEvents", it can only catch the action I did in the 2 sec, but it still doesn't give me free control on the spreadsheet within the 2 sec. Is there any multi-threading in VBA ? or is there any alternative ? Thanks !

Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Sub main()
Dim x As Integer

For x = 0 To 10
Sleep 2000
DoEvents
Cells(1, 1) = x
Next

End Sub

 

[Andy Nguyen]

Where do you get the data from ? Is it a web query ? I know when you import web query, you can set the refresh rate by minute. If this is some internal data from another sheet, we would need more info before making some suggestion. If your data is dynamic, what are the variables ?

 

[rholowczak]

The "sleep" function you are calling does not allow other VBA events to execute. So this is why your spreadsheet is blocking for 2 seconds at a time.
You might instead consider using a Timer. "Timer" itself gies the number of seconds elapsed since midnight.

So you could do something like:

For x = 0 To 10
StartTime = Timer
TimeNow = Timer
While ((TimeNow - StartTime) < 2)
TimeNow = Timer
DoEvents ' Or do other things....
Wend
Cells(1, 1) = x
Next

I suppose one possible issue is that if what you are doing in the middle
of the While takes more than 2 seconds, it will not get interrupted.
So in that case you may not get exactly 2 seconds between loops but you
for sure will get at least 2 seconds between loops.


Cheers,

Prof. H.

 

[Lun]

Where do you get the data from ? Is it a web query ? I know when you import web query, you can set the refresh rate by minute. If this is some internal data from another sheet, we would need more info before making some suggestion. If your data is dynamic, what are the variables ?

yes, I get the data from web, but I don't know how to set the refresh rate, is there any sample ?
thanks !

 

[Lun]

The "sleep" function you are calling does not allow other VBA events to execute. So this is why your spreadsheet is blocking for 2 seconds at a time.
You might instead consider using a Timer. "Timer" itself gies the number of seconds elapsed since midnight.

So you could do something like:

For x = 0 To 10
StartTime = Timer
TimeNow = Timer
While ((TimeNow - StartTime) < 2)
TimeNow = Timer
DoEvents ' Or do other things....
Wend
Cells(1, 1) = x
Next

I suppose one possible issue is that if what you are doing in the middle
of the While takes more than 2 seconds, it will not get interrupted.
So in that case you may not get exactly 2 seconds between loops but you
for sure will get at least 2 seconds between loops.


Cheers,

Prof. H.

actually, there are two problems with doevents
1. it will use up nearly all CPU time, most cases > 90 %, other applications in the same computer will be affected
2. whenever I enter data into the spreadsheet, say enter a value into cell C3, then runtime error '1004', below is a simple code sample. In this sample, once you enter data into the spreadsheet, the count at cell A1 & B1 will stop immediately, and the value of A1 is larger than B1 by one, so we can see the error comes from doevents

sub main
For x = 0 To 10000
Cells(1, 1) = x
DoEvents
Cells(1, 2) = x
Next
end sub

 

[Andy Nguyen]

yes, I get the data from web, but I don't know how to set the refresh rate, is there any sample ?
thanks !

http://www.mrexcel.com/tip103.shtml

 

[Lun]

http://www.mrexcel.com/tip103.shtml

I have just tried it out, it works fine, thanks ! \\:D/