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!