ReDim Preserve array to erase unwanted values, subscript error

cg85cg · Nov 19, 2006

In a program I am writing I cycle through some arrays in order to get rid of a few unwanted values. the loop goes through the array and finds the value it doesn't like and then shifts each element after that up one. When the loop completes, I am left with a bunch of elements (not empty) that I do not want at the back of the array, I want to resize the array so that it is just big enough to hold the data I want. In short I am using the ReDim Preserve statement to cut off the unwanted back end of an array. the error message I get is 'runtime error 9 subscript out of range'. I don't understand why this is so, I've tried a couple of different ways to write the statement but nothing is working.

The code and a variable index follows:

ReDim Preserve day1(1 To UBound(day1) - i, 1 To 1)

UBound(day1) = the current length of the array = 2091

i = the number of times array elements have been shifted up, thus the number of extra array elements I have at the end.

If more code is required I can provide it.

VulcanJedi · Nov 19, 2006

Check out 'Redim" in the VBA Help?:
You have a multitude of issues apparently?
If you are using option base 1 then you might be ok, but if not you are using option base 0 (default) therfore you are cropping the lower AND upper bounds or your array and apparently.
1.As stated the syntax of Redim does NOT allow you to use Redim Preserve with the intent to rediming multiple dimensions, which you appear to be doing.
2.you are apparently trying to augment both the lower AND upper bounds of a particular dimension. Again a nono.

If you use the Preserve keyword, you can resize only the last array dimension and you can't change the number of dimensions at all. For example, if your array has only one dimension, you can resize that dimension because it is the last and only dimension. However, if your array has two or more dimensions, you can change the size of only the last dimension and still preserve the contents of the array. The following example shows how you can increase the size of the last dimension of a dynamic array without erasing any existing data contained in the array.

ReDim X(10, 10, 10)
. . .
ReDim Preserve X(10, 10, 15)

Similarly, when you use Preserve, you can change the size of the array only by changing the upper bound; changing the lower bound causes an error.

If you make an array smaller than it was, data in the eliminated elements will be lost. If you pass an array to a procedure by reference, you can't redimension the array within the procedure

[yinyang]

Tranpkp [pc2]

N1GHTEYES · Nov 20, 2006

If you pass an array to a procedure by reference, you can't redimension the array within the procedure

Maybe I'm misunderstanding you here, but I think you can. Try this code:

Code:

Public Function ArrayRedimTest(arrsize1 As Long, arrsize2 As Long) As String
Dim opstr As String
ReDim testarray(arrsize1) As Long
opstr = "Initial array size was " & Str(arrsize1) & ".  "
redimmer testarray(), arrsize2
opstr = opstr & "After resizing, array size is " & Str(UBound(testarray)) & "."
ArrayRedimTest = opstr
End Function

Public Sub redimmer(ByRef dummyarray() As Long, newsize As Long)
ReDim dummyarray(newsize) As Long
End Sub

If you put that into a module then put the userfunction "ArrayRedimTest" into a cell, with the arguments 5 & 10, you get "Initial array size was 5. After resizing, array size is 10.". Note that the 10 value comes from actually checking the ubound of the array after the redim process inside another sub.

Tony

IknowMe · Nov 20, 2006

Dim TestArray()

TestArray() = Array("Zero", "One", "Two", "Three", "Four")
ReDim Preserve TestArray(3)

For Elements = 0 To UBound(TestArray)
MsgBox TestArray(Elements)
Next Elements

[thumbsup2]

Wow, I'm having amnesia and deja vu at the same time.
I think I've forgotten this before.

N1GHTEYES · Nov 20, 2006

MrMilson - was this in response to my post, tranpkp's or the OP?

Tony

VulcanJedi · Nov 20, 2006

nighteyes / MrWilson...

All of your statement involve a SINGLE dimensional ARRAY.
Look up HELP in VBA....it explicitly says you can NOT change both the Lower AND Upper bounds.
Also that can NOT change any but the LAST dimension.

[yinyang]

Tranpkp [pc2]

IknowMe · Nov 20, 2006

Just showing an examplle of using the Preserve statement. Not trying to step on any toes. Should've stated my intent along witht the sample code. I wasn't attempting to disagree with you tranpkp, but rather supporting what you'd already stated. My bad I should've been more clear.

[thumbsup2]

Wow, I'm having amnesia and deja vu at the same time.
I think I've forgotten this before.

VulcanJedi · Nov 20, 2006

no worries!; i was just confused as I thought you guys were saying it can be done, when I didn't see it was applicable to the OPs post and contradicted what the help info explicitly stated.
We're here to help; i've just made my way back to tektips after being here heavily years ago....

[yinyang]

Tranpkp [pc2]

IknowMe · Nov 20, 2006

Here's a work around.

Code:

Sub Main()
  
    Dim YourArray() As Variant
    Dim TempArray As Variant
    Dim Dimension1 As Long
    Dim Dimension2 As Long
  
    ReDim YourArray(0 To 3, 0 To 3)
  
    'Populate Array Elements
    YourArray(0, 0) = "0,0"
    YourArray(0, 1) = "0,1"
    YourArray(1, 0) = "1,0"
    YourArray(1, 1) = "1,1"
    YourArray(2, 0) = "2,0"
    YourArray(2, 1) = "2,1"
    YourArray(3, 0) = "3,0"
    YourArray(3, 1) = "3,1"
    
    'Copy array to temp.
    TempArray = YourArray()
    'Resize and Clear Your Array
    ReDim YourArray(0 To 2, 0 To 2)
    'Repopulate YourArray
  
    For Dimension1 = 0 To 2
        For Dimension2 = 0 To 2
            YourArray(Dimension1, Dimension2) = TempArray(Dimension1, Dimension2)
        Next
    Next
  
  Debug.Print YourArray(0, 0)
  Debug.Print YourArray(0, 1)
  Debug.Print YourArray(1, 0)
  Debug.Print YourArray(1, 1)
  Debug.Print YourArray(2, 0)
  Debug.Print YourArray(2, 1)
    
End Sub

[\code]

[thumbsup2]  [COLOR=blue][I]Wow, I'm having amnesia and deja vu at the same time.
                         I think I've forgotten this before.[/I][/color]

IknowMe · Nov 20, 2006

\ / oops.... at least the code works.....erm I hope

[thumbsup2]

Wow, I'm having amnesia and deja vu at the same time.
I think I've forgotten this before.

N1GHTEYES · Nov 20, 2006

tranpkp - Sorry, I didn't mean to disagree w/ your advice to the OP re redim preserve. As far as I can see, that was spot on. I just thought that the comment I quoted wasn't strictly true. You seemed to be saying that an array passed to a procedure by ref cannot be redimmed inside that proc. That is what I was questioning.

An array passed to a proc by ref CAN be redimmed inside that proc - see the example I posted.

Maybe I misunderstood what you originally meant.

Tony

VulcanJedi · Nov 20, 2006

Np; just saying that what the original poster was trying to do should be impossible on the two counts as stated explictly from the help. I was confused that the following posts seemed to say that should be possible which was what I was questioning, but apparently u guys just meant to reinforce how the Redim preserve could/should work...

[yinyang]

Tranpkp [pc2]

IknowMe · Nov 20, 2006

cg85cg,

What are you storing in the array? If either dimension is a static length you could flip your indexes and use the preserve statment the way your intending.

example

Say I know I have Two names for each person but not sure how many persons

I guess two people:
Redim SomeArray(2, 2)
Column1 Column2
Row1 John Smith
Row2 Jane Doe

but I find out I actually need to add three three people
We know ReDim Preserve SomeArray(3, 2)
won't work so I flip indexes like so

flipped
Assume we started with ReDim SomeArray(2, 2)
And I need to add 1 more person.
ReDim Preserve SomeArray(3, 3)
Row1 Row2 Row3
Column1 John Jane Buzz
Column2 Smith Doe LightYear

Will this work for you?

[thumbsup2]

Wow, I'm having amnesia and deja vu at the same time.
I think I've forgotten this before.

IknowMe · Nov 20, 2006

Typo

flipped
Assume we started with ReDim SomeArray(2, 2)
And I need to add 1 more person.
ReDim Preserve SomeArray(2, 3)
Row1 Row2 Row3
Column1 John Jane Buzz
Column2 Smith Doe LightYear

[thumbsup2]

Wow, I'm having amnesia and deja vu at the same time.
I think I've forgotten this before.

cg85cg · Nov 20, 2006

jeez, you guys really went to town on this thread. To start, option base 1 is true

I am actually manipulating three arrays in the same way, I have them all intitially declared as variants because when i fill the arrays with data from a range, some cells in the range have "-", this signifies that no data was present and these are the elements I am deleting. the arrays were initially dimensioned as 2D, but really only have one dimension. for example:

Dim array(n, 1)

This is because i ran into problems when moving data between ranges and arrays. Apparantly the range was 2d even if it only had one column so the array also had to be created as a 2d array with only one column. At the point when i am changing the size to trim off the back end, the array will not need to accept any new data (only transpose it), so can i Redim the array as:

Redim Preserve array(1 to n)

Instead of

Redim Preserve array(1 to n, 1 to 1)?

Also, as a corallary, in one of the arrays, the range that is to be moved into the array has a string, "TRUE". when VBA determines what data type each element has it thinks "TRUE" is boolean and treats it as such. whawt is the best was to make sure that the string "TRUE" is not labeled as boolean? Should i write a seperate loop to go through the array and change the data type to string? I can't just declare the array as string because i run into problems when moving a range to it.

MrMilson, When you redim the array to fip it do the values flip with it or do you have to re-enter each value in its new index?

IknowMe · Nov 21, 2006

MrMilson, When you redim the array to fip it do the values flip with it or do you have to re-enter each value in its new index?

You would set it up and use it that way from the start.

This works

Code:

Sub Main()

Dim Info() As Variant
Dim Column As Long
Dim Row As Long
Dim Customer As Variant

'I know I want four peices of info for each customer
'but I'm unsure how many customers I have at this point
ReDim Info(1 To 4, 1 To 1)

Info(1, 1) = "John" 'First name
Info(2, 1) = "Doe"  'Last name
Info(3, 1) = "111-111-1111" 'Phone
Info(4, 1) = "100.00$"  'Amount owed

'I now need to add an additional user

[COLOR=red]ReDim Preserve Info(1 To 4, 1 To 2)[/color]

Info(1, 2) = "Jane" 'First name
Info(2, 2) = "Doe"  'Last name
Info(3, 2) = "111-111-1111" 'Phone
Info(4, 2) = "200.00$"  'Amount owed

For Row = 1 To UBound(Info, 2)
    For Column = 1 To UBound(Info, 1)
        Customer = Customer & " " & Info(Column, Row)
    Next Column
    MsgBox Customer
    Customer = ""
Next Row
End Sub

This Doesn't

Code:

Sub Main()

Dim Info() As Variant
Dim Column As Long
Dim Row As Long
Dim Customer As Variant

'I know I want four peices of info for each customer
'but I'm unsure how many customers I have at this point
ReDim Info(1 To 1, 1 To 4)

Info(1, 1) = "John" 'First name
Info(1, 2) = "Doe"  'Last name
Info(1, 3) = "111-111-1111" 'Phone
Info(1, 4) = "100.00$"  'Amount owed

'I now need to add an additional user

[COLOR=red]ReDim Preserve Info(1 To 2, 1 To 4)[/color]

Info(2, 1) = "Jane" 'First name
Info(2, 2) = "Doe"  'Last name
Info(2, 3) = "111-111-1111" 'Phone
Info(2, 4) = "200.00$"  'Amount owed

For Row = 1 To UBound(Info, 1)
    For Column = 1 To UBound(Info, 2)
        Customer = Customer & " " & Info(Column, Row)
    Next Column
    MsgBox Customer
    Customer = ""
Next Row

End Sub

Although both are attempting to do the same thing.

[thumbsup2]

Wow, I'm having amnesia and deja vu at the same time.
I think I've forgotten this before.

IknowMe · Nov 21, 2006

The concept is this:

If your using a two dimension array and one side is of fixed lentgh as in your example

ReDim Preserve day1(1 To UBound(day1) - i, 1 To 1)

Set it up with the fixed length coming first set up your array so that the fixed side comes first if you want to use redim preserve.

I'm not sure if any of this is clear but I don't know how to explain it better than this. If anyone else does feel free to jump in.

[thumbsup2]

Wow, I'm having amnesia and deja vu at the same time.
I think I've forgotten this before.

cg85cg · Nov 21, 2006

oh i got it. i just thought maybe you were saying there is a special trick for switching around the array. I will give that a shot for sure.

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

ReDim Preserve array to erase unwanted values, subscript error

Technical User

Technical User

Technical User

Programmer

Technical User

Technical User

Programmer

Technical User

Programmer

Programmer

Technical User

Technical User

Programmer

Programmer

Technical User

Programmer

Programmer

Technical User

Similar threads

Log in

Part and Inventory Search

Sponsor