In C++ one can expand or decrease the dimension an array. Can the same thing
be done in VBA?
--
Thanks for your help

If you have a dynamic array (one in which the bounds are not specified in
the Dim statement), you can change the number of elements in the array with
the ReDim statement.

Dim Arr() As Long
ReDim Arr(0 To 9)

You can ReDim the array as often as you want. ReDim will cause the existing
contents of the array to be lost unless you use the Preserve modifier.

ReDim Preserve Arr(0 To 11)

You can change the number of dimensions of a dynamic array if you first
Erase the array.

Dim Arr() As Long
ReDim Arr(0 To 9)
' populate the array and do something
Erase Arr
ReDim Preserve Arr(0 To 9, 0 To 3)

None of this will work if the array is statically declared by specifying the
bounds in the Dim declaration.

Dim Arr(0 To 9) As Long
ReDim Arr(0 To 11) '<<<<< COMPILER ERROR


--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)





"LesHurley" wrote in message
...
In C++ one can expand or decrease the dimension an array. Can the same
thing
be done in VBA?
--
Thanks for your help

Ok chip, but if you ReDim it again the origional contents of the array are
set to zeros. I need to increase the size of the array to make room for more
data without losing the old data. for example, in a quick sort routine there
is no way to know how much space will be needed for each section of the data.
--
Thanks for your help


"Chip Pearson" wrote:

If you have a dynamic array (one in which the bounds are not specified in
the Dim statement), you can change the number of elements in the array with
the ReDim statement.

Dim Arr() As Long
ReDim Arr(0 To 9)

You can ReDim the array as often as you want. ReDim will cause the existing
contents of the array to be lost unless you use the Preserve modifier.

ReDim Preserve Arr(0 To 11)

You can change the number of dimensions of a dynamic array if you first
Erase the array.

Dim Arr() As Long
ReDim Arr(0 To 9)
' populate the array and do something
Erase Arr
ReDim Preserve Arr(0 To 9, 0 To 3)

None of this will work if the array is statically declared by specifying the
bounds in the Dim declaration.

Dim Arr(0 To 9) As Long
ReDim Arr(0 To 11) '<<<<< COMPILER ERROR


--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)





"LesHurley" wrote in message
...
In C++ one can expand or decrease the dimension an array. Can the same
thing
be done in VBA?
--
Thanks for your help

Then you missed the part where Chip wrote about the Preserve keyword.

You can ReDim the array as often as you want. ReDim will cause the
existing
contents of the array to be lost unless you use the Preserve modifier.

ReDim Preserve Arr(0 To 11)

Rick

Rick Rothstein (MVP - VB) wrote:
Then you missed the part where Chip wrote about the Preserve keyword.

You can ReDim the array as often as you want. ReDim will cause the
existing
contents of the array to be lost unless you use the Preserve modifier.

ReDim Preserve Arr(0 To 11)


Rick

A bit more needs to be said.

If Arr is a true variant() type array [e.g.,
Dim Arr()
ReDim Arr(1 to 10, 1 to 2)]
then with the Preserve keyword you can change only the upper bound of
the last dimension
[e.g.,
Dim Arr()
Redim Arr(1 to 10, 1 to 2)
ReDim Preserve Arr(1 to 10, 1 to 4)]
In this case, if you try to change either bound of the 1st dimension, or
the lower bound of the 2nd dimension, you will get a Subscript out of
range error.

If, however, Arr is a an array contained within a Variant type variable
[e.g.,
Dim Arr
Redim Arr(1 to 10, 1 to 2)]
then with the Preserve keyword you can change either or both bounds of
the last dimension [e.g.,
Dim Arr
Redim Arr(1 to 10, 1 to 2)
ReDim Preserve Arr(1 to 10, 0 to 3)]

If the functions in the freely downloadable file at
http:/home.pacbell.net/beban are available to your workbook, then you
can use the ResizeArray function to preserve the values of the array
that is passed to it and change the lower and/or upper bounds of any or
all of the dimensions of a one-, two- three- or four-dimensional
array, or increase (up to 4) or decrease the number of the array's
dimensions (whether or not the array is contained within a Variant type
variable).

Alan Beban

Thanks everyone
--



"Alan Beban" wrote:

Rick Rothstein (MVP - VB) wrote:
Then you missed the part where Chip wrote about the Preserve keyword.

You can ReDim the array as often as you want. ReDim will cause the
existing
contents of the array to be lost unless you use the Preserve modifier.

ReDim Preserve Arr(0 To 11)


Rick

A bit more needs to be said.

If Arr is a true variant() type array [e.g.,
Dim Arr()
ReDim Arr(1 to 10, 1 to 2)]
then with the Preserve keyword you can change only the upper bound of
the last dimension
[e.g.,
Dim Arr()
Redim Arr(1 to 10, 1 to 2)
ReDim Preserve Arr(1 to 10, 1 to 4)]
In this case, if you try to change either bound of the 1st dimension, or
the lower bound of the 2nd dimension, you will get a Subscript out of
range error.

If, however, Arr is a an array contained within a Variant type variable
[e.g.,
Dim Arr
Redim Arr(1 to 10, 1 to 2)]
then with the Preserve keyword you can change either or both bounds of
the last dimension [e.g.,
Dim Arr
Redim Arr(1 to 10, 1 to 2)
ReDim Preserve Arr(1 to 10, 0 to 3)]

If the functions in the freely downloadable file at
http:/home.pacbell.net/beban are available to your workbook, then you
can use the ResizeArray function to preserve the values of the array
that is passed to it and change the lower and/or upper bounds of any or
all of the dimensions of a one-, two- three- or four-dimensional
array, or increase (up to 4) or decrease the number of the array's
dimensions (whether or not the array is contained within a Variant type
variable).

Alan Beban