Solved: Need a tutorial on Array Redim Preserve

Good Day,

I've been playing with arrays (for hours).

Can someone explain why the first code works, and the second one does not?

I noticed that if put the variable/expanding portion as the second array, it works.

[VBA]Sub DoesWork()
Dim x() As Variant
Dim i
Dim j
For i = 1 To 10
If Cells(i, 1) Mod 2 = 0 Then
j = j + 1
ReDim Preserve x(1 To 1, 1 To j)
x(1, j) = Cells(i, 1).Value
End If
Next i
Range("B1").Resize(UBound(x, 2)) = Application.Transpose(x)
End Sub

Sub DoesNotWork()
Dim x() As Variant
Dim i
Dim j
For i = 1 To 10
If Cells(i, 1) Mod 2 = 0 Then
j = j + 1
ReDim Preserve x(1 To j, 1 To 1)
x(1, j) = Cells(i, 1).Value
End If
Next i
Range("B1").Resize(UBound(x, 2)) = Application.Transpose(x)
End Sub[/VBA]

[VBA]ReDim Preserve x(1 To j, 1 To 1)[/VBA]
From vba help:

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

In your first code, you were adjusting the last dimension of the array, which will work. The second try falls over, as you are trying to adjust the first dimension.

Does that make sense?

Mark

If there's only 1 dimension containing values you could use:

[vba]Sub DoesWork_snb()
Dim x()

For i = 1 To 10
If i Mod 2 = 0 Then
ReDim Preserve x(j)
x(j) = j + 1
j = j + 1
End If
Next

Range("C1").Resize(UBound(x) + 1) = Application.Transpose(x)
End Sub[/vba]

Of course you could skip Preserve and Transpose.
[VBA]Sub DoesWork2()
Dim x() As Variant
Dim i As Long
Dim j As Long
Dim r As Range
Dim c As Range

Set r = Range("A1:A10")
'j = [SUMPRODUCT((MOD(A1:A10,2)=0)+0)]
j = Evaluate("SUMPRODUCT((MOD(" & r.Address & ",2)=0)+0)")
ReDim x(1 To j, 1 To 1)
If j = 0 Then Exit Sub
j = 0
'For i = 1 To 10
For Each c In r
If c Mod 2 = 0 Then
j = j + 1
x(j, 1) = c.Value
End If
Next c

Range("B1").Resize(UBound(x)) = x
End Sub[/VBA]

@KH

are you sure about:
[vba]j= [SUMPRODUCT((MOD(A1:A10,2)=0)+0)][/vba]
shouldn't it be
[vba]j= [SUMPRODUCT((MOD(row(A1:A10),2)=0)+0)][/vba]

But I'd suggest:

[vba]
Sub snb()
[G1:G5] = Application.Transpose(Filter([transpose(if(not(mod(row(1:10),2)),A1:A10))], "False", False))
End Sub
[/vba]

GTO:

I didn't know the one could only make adjustments to the last array. I've been searching the internet for an explanation. Does Microsoft has this documented?

snb:

I've learnt a new twist with your single array code.

Kenneth Hobs:

I like your work-around technique. I'll try it.

Thanks,
Xrull
:bow:

Documentation: VBEditor, highlight Redim, click F1:

Excel 2010 Developer Reference > Visual Basic for Applications Language Reference > Visual Basic Language Reference > Statements
ReDim Statement
Used at procedure level to reallocate storage space for dynamic array variables.
Syntax
ReDim [Preserve] varname(subscripts) [As type] [, varname(subscripts) [As type]] . . .
The ReDim statement syntax has these parts:
PartDescriptionPreserveOptional. Keyword used to preserve the data in an existing array when you change the size of the last dimension.varnameRequired. Name of the variable; follows standard variable naming conventions.subscriptsRequired. Dimensions of an array variable; up to 60 multiple dimensions may be declared. The subscripts argument uses the following syntax: [lower To] upper [,[lower To] upper] . . .
When not explicitly stated in lower, the lower bound of an array is controlled by the Option Base statement. The lower bound is zero if no Option Base statement is present.
typeOptional. Data type of the variable; may be Byte, Boolean, Integer, Long, Currency, Single, Double, Decimal (not currently supported), Date, String (for variable-length strings), String * length (for fixed-length strings), Object, Variant, a user-defined type, or an object type. Use a separate As type clause for each variable being defined. For a Variant containing an array, type describes the type of each element of the array, but doesn't change the Variant to some other type.
Remarks
The ReDim statement is used to size or resize a dynamic array that has already been formally declared using a Private, Public, or Dim statement with empty parentheses (without dimension subscripts).
You can use the ReDim statement repeatedly to change the number of elements and dimensions in an array. However, you can't declare an array of one data type and later use ReDim to change the array to another data type, unless the array is contained in a Variant. If the array is contained in a Variant, the type of the elements can be changed using an As type clause, unless you're using the Preserve keyword, in which case, no changes of data type are permitted.
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.
When variables are initialized, a numeric variable is initialized to 0, a variable-length string is initialized to a zero-length string (""), and a fixed-length string is filled with zeros. Variant variables are initialized to Empty. Each element of a user-defined type variable is initialized as if it were a separate variable. A variable that refers to an object must be assigned an existing object using the Set statement before it can be used. Until it is assigned an object, the declared object variable has the special value Nothing, which indicates that it doesn't refer to any particular instance of an object.

http://vbaexpress.com/forum/ms-help:...vs_Caution.gif CautionThe ReDim statement acts as a declarative statement if the variable it declares doesn't exist at module level or procedure level. If another variable with the same name is created later, even in a wider scope, ReDim will refer to the later variable and won't necessarily cause a compilation error, even if Option Explicit is in effect. To avoid such conflicts, ReDim should not be used as a declarative statement, but simply for redimensioning arrays.


http://vbaexpress.com/forum/ms-help:...nt/vs_note.gif NoteTo resize an array contained in a Variant, you must explicitly declare the Variant variable before attempting to resize its array.

snb,

I'm going to read the documentation links you sent me.

Thanks,
Xrull

P.S.
I'm trying to mark the thread as solved, but I'm using Chrome. I'll have to use another browser.