Hello all,

Any help would be greatly appreciated...

I have a perpetual data stream which I bring into Excel via a DDE link.
I store the data and run analysis on the most recent 40000 rows. I
use an array to hold a column of data for manipulation but I do not
want it to be any larger then 40000 rows.

How do I shift the data within the array so that the oldest element
stored "falls-off the end", all other elements shift up and the new
data element is placed in the array - all within one-half second.

Is there a method or combination of methods which will achieve this.
Maybe copying back-and-forth between two arrays? Any thoughts??

Thanks!

Trip

I think I got it...

For i = 1 To 40000
testarray(i - 1) = testarray(i)
Next i

or vise versa.

Any better ways??

Thanks all!

Trip

Assuming your array is 1 to 40000 (not 0 to 40000), I think you want:

For i = 2 To 40000
testarray(i - 1) = testarray(i)
Next i

testarray(40000) = NewValue

When i = 2 it assigns a value to testarray(1). The loop continues until
testarray(39999) is assigned. It could be restated (and maybe should be for
clarity) as:

For i = 1 To 39999
testarray(i) = testarray(i + 1)
Next i

testarray(40000) = NewValue

To me, that's a little clearer-at-a-glance that each array member is being
assigned the value from the member above, and just which members the loop
will effect.

HTH,
--
George Nicholson

Remove 'Junk' from return address.

"Trip" wrote in message
oups.com...
I think I got it...

For i = 1 To 40000
testarray(i - 1) = testarray(i)
Next i

or vise versa.

Any better ways??

Thanks all!

Trip

Perhaps

RedDim testarray(1 to 39999)
RedDim testarray(1 to 40000)
testarray(40000) = "newValue"

Alan Beban

George Nicholson wrote:
Assuming your array is 1 to 40000 (not 0 to 40000), I think you want:

For i = 2 To 40000
testarray(i - 1) = testarray(i)
Next i

testarray(40000) = NewValue

When i = 2 it assigns a value to testarray(1). The loop continues until
testarray(39999) is assigned. It could be restated (and maybe should be for
clarity) as:

For i = 1 To 39999
testarray(i) = testarray(i + 1)
Next i

testarray(40000) = NewValue

To me, that's a little clearer-at-a-glance that each array member is being
assigned the value from the member above, and just which members the loop
will effect.

HTH,

"Alan Beban" wrote in message
...
Perhaps

(1 to 39999)
RedDim testarray(1 to 40000)
testarray(40000) = "newValue"

Alan Beban


Hi Alan,

Would that not shift rather more than the first item out of the array?


---
Regards,
Norman


,

Rather more, indeed! Sorry, I misposted. Intended

Redim Preserve testarray(1 to 39999)
Redim Preserve testarray(1 to 40000)
testarray(40000) = "newValue"

But even that was silly; it simply replaces the last value, not the
first, which can of course simply be done with

testarray(40000) ="newValue"

To drop the first array element, retain the rest and add "newValue" as
the 40,000th element, if the functions in the freely downloadable file
at http://home.pacbell.net/beban are available to your workbook, you
might consider

testarray=MakeArray(SubArray(testarray, 2, 40000), Array("newValue"), 1)

Alan Beban

Norman Jones wrote:
"Alan Beban" wrote in message
...

Perhaps

(1 to 39999)
RedDim testarray(1 to 40000)
testarray(40000) = "newValue"

Alan Beban



Hi Alan,

Would that not shift rather more than the first item out of the array?


---
Regards,
Norman


,

Alan Beban wrote:
. . .
To drop the first array element, retain the rest and add "newValue" as
the 40,000th element, if the functions in the freely downloadable file
at http://home.pacbell.net/beban are available to your workbook, you
might consider

testarray=MakeArray(SubArray(testarray, 2, 40000), Array("newValue"), 1). . . .


Using the Array function is redundant.

testarray=MakeArray(SubArray(testarray, 2, 40000), "newValue", 1)

works fine.

Alan Beban