[SOLVED] VBA with real time data

Hi,

I am an amateur VBA programmer and need some help with real time data .
I am using the reuters tool to get the real time data. I intend to
capture this real time data in Excel every ten seconds for all the
stocks that i want to monitor and store it in an array(probably in a
different sheet) of size ten. This would mean that after 100 seconds
when all the cells of all the arrays are filled up the first cell in
each array should be cleared and assigned the current stock value.
This way each cell in the array should get a new value every 100
seconds.

Problem:- I have tried using Application.Ontime and Application .Wait
in a loop to capture these values every ten seconds. But when used in a
loop , the worksheet freezes meaning it stops recieving real time data.
And each cell of any particular array is filled with the same value as
the first cell. I need to somehow figure out how to introduce a delay
in the Sub without freezing the real time data feed . Here is a simple
code i'm trying to use. Please help me with this. I'll greatly
appreciate it.
---------------------------------------------------------------------------------------------------------------------------------------
Sub calling()
Dim i

For i = 1 To 10
Call dataCopy(i)
Call delay
Next

End Sub


--------------------------------------------------------------------------------------------------------------------------------------
Sub dataCopy(rec)
Dim i, j

j = rec

For i = 1 To 6
Worksheets("Sheet2").Cells(i, j).Value = Worksheets("Sheet1").Cells(i,
4)
Next

Exit Sub

End Sub
---------------------------------------------------------------------------------------------------------------------------------------
Sub delay()

Dim newHour, newMinute, newSecond, waitTime
newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 10
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime

End Sub
---------------------------------------------------------------------------------------------------------------------------------------

Regards,
Paresh

Hi , its me again. I ma new to this group but I can somehow tell that
Tom is best one to help me with this. So, if you can please do reply
hopefully with a solution. If possible email it.Thank you.

Paresh

jain.pb@gmail.com wrote:
> Hi,
>
> I am an amateur VBA programmer and need some help with real time data .
> I am using the reuters tool to get the real time data. I intend to
> capture this real time data in Excel every ten seconds for all the
> stocks that i want to monitor and store it in an array(probably in a
> different sheet) of size ten. This would mean that after 100 seconds
> when all the cells of all the arrays are filled up the first cell in
> each array should be cleared and assigned the current stock value.
> This way each cell in the array should get a new value every 100
> seconds.
>
> Problem:- I have tried using Application.Ontime and Application .Wait
> in a loop to capture these values every ten seconds. But when used in a
> loop , the worksheet freezes meaning it stops recieving real time data.
> And each cell of any particular array is filled with the same value as
> the first cell. I need to somehow figure out how to introduce a delay
> in the Sub without freezing the real time data feed . Here is a simple
> code i'm trying to use. Please help me with this. I'll greatly
> appreciate it.
> ---------------------------------------------------------------------------------------------------------------------------------------
> Sub calling()
> Dim i
>
> For i = 1 To 10
> Call dataCopy(i)
> Call delay
> Next
>
> End Sub
>
>
> --------------------------------------------------------------------------------------------------------------------------------------
> Sub dataCopy(rec)
> Dim i, j
>
> j = rec
>
> For i = 1 To 6
> Worksheets("Sheet2").Cells(i, j).Value = Worksheets("Sheet1").Cells(i,
> 4)
> Next
>
> Exit Sub
>
> End Sub
> ---------------------------------------------------------------------------------------------------------------------------------------
> Sub delay()
>
> Dim newHour, newMinute, newSecond, waitTime
> newHour = Hour(Now())
> newMinute = Minute(Now())
> newSecond = Second(Now()) + 10
> waitTime = TimeSerial(newHour, newMinute, newSecond)
> Application.Wait waitTime
>
> End Sub
> ---------------------------------------------------------------------------------------------------------------------------------------
>
> Regards,
> Paresh