Shifting Array Elements

  • Thread starter Thread starter Trip
  • Start date Start date
T

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
 
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,
 
Perhaps

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

Alan Beban
 
Alan Beban said:
Perhaps

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

Alan Beban
Click to expand...


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
 
Alan said:
. . .
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). . . .
Click to expand...

Using the Array function is redundant.

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

works fine.

Alan Beban
 
Back
Top