Avatar of ThomasFoege
ThomasFoege
 asked on

Reversing and trimming an array?

Hello EE!

I've run into some problems with arrays. I find arrays to be pretty hard to understand, but I've managed to make an array like so:

array(1) = "a"
array(2) = "b"
array(3) = "c"
array(4) = "d"

Is there any way, to easily flip the array and trim it to 3 max (I'm not sure if the array goes above 3 though)?

So the array becomes:
array(1) = "d"
array(2) = "c"
array(3) = "b"


Hope you can help!
Thanks in advance!
Microsoft Excel

Avatar of undefined
Last Comment
ThomasFoege

8/22/2022 - Mon
ukerandi

array(0) = "a"
array(1) = "b"
array(2) = "c"
array(3) = "d"

b’cos array start from 0(zero)
Jorge Paulino

The array indexes starts on zero and not one. If you want to remove an index you need do create a new array and copy to there the values you want.

Why don-t you use collections? It's easier.

Dim coll As New Collection
coll.Add "a"
coll.Add "b"
coll.Add "c"

' removes the 3rd position
coll.Remove 3
ukerandi

dim i count as integer
dim a count as integer
array(0) = "a"
array(1) = "b"
array(2) = "c"
array(3) = "d"

i=4
a =0
while(i<=0)

 array(a)=array(i)

i=i-1
a=a+1
wend
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
ukerandi

dim i count as integer
dim a count as integer
array(0) = "a"
array(1) = "b"
array(2) = "c"
array(3) = "d"

i=4
a =0
while(i<=0)


 
array(a)=array(i)
if(i=3)then
exit loop
end if
i=i-1
a=a+1
wend
NeillZ

It's fine to start your array at 1, 2 or any other number for that matter. (In fact you can explicitly define the lower boundary as starting at 1… but I digress).

Generally arrays start at 0. E.g. For an array defined per "Dim vnArray(1)"
You have a one-dimensional array with 2 field (0 and 1)... and (in this example) it is a variant array. (Which means you can add anything to any field (including setting ‘fields’ to objects).

You can the expand only the last dimension of arrays of the fly but you can’t reduce it. As jpaulino has suggested you can use a collection instead of an array to achieve what you want… collections are like one-dimensional arrays… but you can add and remove from them.

In terms of finding out how big an array is, you use Ubound.
In the example array [created by Dim vnArray(1)]: Ubound(vnArray,1) would return 1 (i.e. the upper boundary of the first dimension).

Re your question though: If the right tool for the job is an array and you want to reduce it, you use a second (temporary array). I have provided two code examples of how you could do this.

Foot note: In both code examples my arrays have a lower boundary of zero, but I am ignoring it. Many people prefer to use arrays from the ‘field’ 1 up. Once you get used to using arrays though, field 0 it can be quite useful… but that is another story.

''' To remove the first field
Private Sub RemoveItem1(vnArray)

    Dim vnTempArray(), in1 As Integer

    ''' Dimension vnTempArray to one less than vnArray
        ReDim vnTempArray(UBound(vnArray) - 1)
    
    ''' Get all fields in vnArray, starting at field 2 and up to the upper bound of the array
        For in1 = 2 To UBound(vnArray, 1): vnTempArray(in1 - 1) = vnArray(in1)
        Next in1
    
    ''' Replace the original array (i.e. write the result back to your original array)
    ''' NB: As I am writing directly inro the variable of the calling procedure.
        vnArray = vnTempArray

End Sub

''' To remove all filed that equal 'a'
Sub RemoveAllAs(vnArray)

    Dim vnTempArray(), in1 As Integer

    ''' Make one dimensional Temp Array
        ReDim vnTempArray(0)
        
    ''' Transfer all fields in vnArray, except those with 'a' to the Temp Array
        For in1 = 1 To UBound(vnArray)
            If vnArray(in1) <> "a" Then
        ''' Expand the temp array (Redim Preserve allow syou to expand the last dimensionn of an array)
                ReDim Preserve vnTempArray(UBound(vnTempArray) + 1)
        ''' Transfer the qualifying field into the [new] upper boundary of the temp array
                vnTempArray(UBound(vnTempArray)) = vnArray(in1)
            End If
        Next in1
    
    ''' Replace the original array (i.e. write the result back to your original array)
    ''' NB: As I am writing directly inro the variable of the calling procedure.
         vnArray = vnTempArray

End Sub

Open in new window

Rory Archibald

The lower bound of the array depends on how it was declared and whether there is an Option Base statement in effect - they do not always start at 0.

To trim and flip, you'll need to loop backwards and populate, then redim but the exact code may depend on what you mean by "I'm not sure if the array goes above 3 though".
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ThomasFoege

ASKER
The array may only be 1 or 2 long, just so it doesnt bug up


The problem is i fill an array based on a pivot table, that lists by date. But I'm only interested in the latest 3, but I want them to show in reverse order. If the pivot is filtered to only show 1 date, the array will only contain 1 date
Rory Archibald

Why don't you fill from the bottom (or right) of the pivot table then, rather than trying to rejig it all afterwards?
ThomasFoege

ASKER
Well, i get the length of the pivot by

intLength = (Worksheets("Data Availability").PivotTables("ava_1").TableRange2.Rows.Count)  - 5 

Open in new window


and then
For Each r In Worksheets("Data Availability").Range("$A$6:$C$" & (intLength + 5)).Rows
    varReliAxis(i) = r.Cells(1, 1).Value
Next r

Open in new window


If i filter it the other way, I do have it reversed, but I would then need to delete the first ones in the array?

All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Rory Archibald

Sorry, I don't follow you. You need to start with the bottom cell, then do the one above, then the one above that, then stop.
NeillZ

I agree with rorya: Probably easier to just get the data you want in the first place.
But, in the interest of staying with your question as posed… below is code to get “up to” the last three items from your array.

''' To just get up to the last three
Sub GetLastThree(vnArray)

    Dim vnTempArray(), in1 As Integer

    ''' Make one dimensional Temp Array
        ReDim vnTempArray(0)
        
    ''' Transfer top-most items from array (up to 3)
        Do
            ReDim Preserve vnTempArray(in1 + 1)
            vnTempArray(UBound(vnTempArray)) = vnArray(UBound(vnArray) - in1)
            in1 = in1 + 1
        Loop Until in1 = 3 Or UBound(vnArray) - in1 < 1
    
    ''' Replace the original array (i.e. write the result back to your original array)
    ''' NB: As I am writing directly inro the variable of the calling procedure.
         vnArray = vnTempArray

End Sub

Open in new window

ThomasFoege

ASKER
So I can use that function like this?

myArray = GetLastThree(myArray)

And then myArray would now only contain the top 3?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
NeillZ

Mostly right...
You can just run the sub from your main procedure as:
      GetLastThree myArray
The sub will write directly back to the callers array as passed (i.e. no need for MyArray =)
vnArray in the sub is ByRef, so vnArray is MyArray

Alternately, copy the code as provided into your main procedure and change vnArray to MyArray…
Both approaches will work
ThomasFoege

ASKER
That works super!

Only problem is that I get a blank in the new array?

From A, B, C to
" ", C, B

Did i miss something?
NeillZ

Oh... (and forgive me if you are well aware of this):
If you copy the code into your main procedure and use an integer already defined (but not otherwise in use) ensure you set it to zero before the process.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
ThomasFoege

ASKER
I dont use any of those :/

Is there anyway to insert an if loop or similar that makes it skip the first line in the array to get around the blank?
NeillZ

yeah... didn't test it (sorry)...
Change:  Loop Until in1 = 3 Or UBound(vnArray) - in1 < 1
To:          Loop Until in1 = 2 Or UBound(vnArray) - in1 < 1
ThomasFoege

ASKER
Thanks but that dindt fix it :/ The array still comes up with a blank as the first data point?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
NeillZ

If you are staying up to date with my lats update... hold on a mo...
ThomasFoege

ASKER
i changed the

ReDim Preserve vnTempArray(in1 + 1)

Open in new window

to

ReDim Preserve vnTempArray(in1)

Open in new window


And it works, I think, does that might cause problems later?
Rory Archibald

Just as an FYI, since you are doing this in Excel, you could just use application.min(2, ubound(vnarray)) to get the applicable size and declare your intermediate array as that size, rather than doing a Redim preserve in the loop.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
NeillZ

The original code was correct... will exit the do loop when either the third element is retrieved or the next element in less than 1 in the array (i.e. in case your array has less than three elements)… but this assumes the array has an upper boundary equal to the number of elements (i.e. per your original “starting at 1”)

In the test you are running… does the array have more or less than 3 elements…?
And… are we still using an array where the upper boundary is the number of elements (or did you change to using ‘field’ 0…?
ASKER CERTIFIED SOLUTION
NeillZ

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ThomasFoege

ASKER
super! thanks a ton!