how to update a remote data link?

[greenbambootea]

I am working on a small project. some realtime data is coming into one of the Excel worksheet and I am using a VBA code to process it. Since the realtime data keeps coming, I keep my code running all the time. However, the problem is that if I do so, the automatic data updating for the realtime data stops. right after I stop my macro. They begin to update again. It seems to me that EXCEL is 100% doing my code and do not want to do anything else even I use DoEvents in my code after every loop. please help!!

 

[SkipVought]

Hi,

Show us the code.

Skip,
[sub]
[red][/red]
[/sub]

 

[greenbambootea]

Sorry. My bad. Here are some more details. The workbook mainly has 2 worksheets. One sheet I call datafeed. when I running a software bought from a realtime information provider, I can input certain symbols like "CLS7M8" into any cell in EXCEL, and it will should be the data and keeps changes when the market is on. My project is to do some analysis and show a final table based on the coming realtime data. we use to do it in EXCEL directly, but it is too slow. now, I wrote some VBA code to do all the calculation by using Array, loops, etc and show the result on another worksheet I call workingsheet. it is must faster. however, whenever I run my code, EXCEL stops picking up the realtime data. I looks to me that EXCEL has spent all its resource for the code. I try to use OnTime, sleep etc and none works. Following is part of the code.

Sub read_settltments()
Dim col As Integer
Dim real_row As Integer
Dim row As Integer
Dim temp As Integer

col = 4
For row = 1 To row_cme_ice_future Step 1
real_row = row + future_starting_row_datafeed
If IsError(data_sheet.Cells(real_row, col)) Then
MsgBox "the CME future settlement data is wrong, please try again"
Application.Quit
Else
temp = Len(data_sheet.Cells(real_row, col))
If temp = 8 Or temp = 0 Then
MsgBox "the CME future settlement data is wrong, please try again"
Application.Quit
Else
cme_future(row, 3) = data_sheet.Cells(real_row, col)
End If
End If
Next
End sub

Above is only a price of the data-reading code, and there are a few more and a lot of calculation which does not depends on any worksheet. Following part is the main sub which I need to keep running all the time to process the realtime data promptly:

Sub main()
Dim runs As Integer
runs = 1
Set working_sheet = Workbooks(1).Worksheets(1)
Set data_sheet = Workbooks(1).Worksheets(5)
data_sheet.Cells(76, 1).Value = 0

Dim BenchMark As Double
Call read_settltments
Call read_spread_width

Do While data_sheet.Cells(76, 1).Value <> 1
working_sheet.Activate
BenchMark = Timer
Call read_future
Call read_spread
...
Call show_result
data_sheet.Cells(100 + runs, 1) = Timer - BenchMark
runs = runs + 1
DoEvents
Loop
End Sub

I use data_sheet.Cells(76, 1) to control if I want to quit the loop. After using DoEvents. I can select cells, scorll, etc while the macro is runing. however, Excel just does not want to pickup the realtime data. Whenever I stop my code, the data updates. Please help!

 

[greenbambootea]

By the way, I did some range operation on the workingsheet. Therefore, the datasheet is not the active sheet. However, I tried to make it actively during the loop before the range operation and after, Excel still does not pick up the data. I do not if there is some code or simple command can force Excel to update the data link. Thanks