Shifting Array Elements

[Trip]

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

 

[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

 

[George Nicholson]

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]

Perhaps

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

Alan Beban

 

[Norman Jones]

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]

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

 

[Alan Beban]

. . .
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