Is it possible to create a second dimension to an array that has already had
its first dimension filled with data:

BigArray = Split(StringList, " ")

I want to redimension BigArray so that it now has a second dimension. Or do
I dimension with 2D upfront and then add the outcome to the split to the
first dimension. Can you do that? I have been trying to avoid looping.

Thanks

EM

ExcelMonkey wrote:
> Is it possible to create a second dimension to an array that has already had
> its first dimension filled with data:
>
> BigArray = Split(StringList, " ")
>
> I want to redimension BigArray so that it now has a second dimension. Or do
> I dimension with 2D upfront and then add the outcome to the split to the
> first dimension. Can you do that? I have been trying to avoid looping.
>
> Thanks
>
> EM
Why are you trying to avoid looping?

Alan Beban

ExcelMonkey wrote:
> Is it possible to create a second dimension to an array that has already had
> its first dimension filled with data:
>
> BigArray = Split(StringList, " ")
>
> I want to redimension BigArray so that it now has a second dimension. Or do
> I dimension with 2D upfront and then add the outcome to the split to the
> first dimension. Can you do that? I have been trying to avoid looping.
>
> Thanks
>
> EM
You're using some terms strangely here. What is your concept of "filling
the first dimension" of a 2-D array? You don't fill dimensions, you fill
"rows" and "columns". Every single element of a 2-D array has two
dimensions--its "row" dimension and its "column" dimension. That's why
you have to access it with 2 index numbers.

It is certainly possible to convert a 1-D array to 2-D, and although the
2-D array may have only one row of data, it does not have a "first
dimension filled with data".

So how about a clearer illustration of what you are trying to do. E.g.,
are you simply trying to convert a 1-D array to 2-D?

Alan Beban

Sorry Allan. I have 1D array with rows populated. I have done so doing the
following:

BigArray = Split(StringList, " ")

I was trying to avoid looping (for no real reason). I wasn't sure if could
still populate all the rows in the first dimension (like i did above) of the
Array if had in fact dimensioned it as 2D array. so I was wondering can you
add a dimension to the array after I have populated the rows in the first
dimension or can dim as 2D and still use the syntax above to populate 1D?

"Alan Beban" wrote:

> ExcelMonkey wrote:
> > Is it possible to create a second dimension to an array that has already had
> > its first dimension filled with data:
> >
> > BigArray = Split(StringList, " ")
> >
> > I want to redimension BigArray so that it now has a second dimension. Or do
> > I dimension with 2D upfront and then add the outcome to the split to the
> > first dimension. Can you do that? I have been trying to avoid looping.
> >
> > Thanks
> >
> > EM
> You're using some terms strangely here. What is your concept of "filling
> the first dimension" of a 2-D array? You don't fill dimensions, you fill
> "rows" and "columns". Every single element of a 2-D array has two
> dimensions--its "row" dimension and its "column" dimension. That's why
> you have to access it with 2 index numbers.
>
> It is certainly possible to convert a 1-D array to 2-D, and although the
> 2-D array may have only one row of data, it does not have a "first
> dimension filled with data".
>
> So how about a clearer illustration of what you are trying to do. E.g.,
> are you simply trying to convert a 1-D array to 2-D?
>
> Alan Beban
>
>

ExcelMonkey wrote:
> Sorry Allan. I have 1D array with rows populated. I have done so doing the
> following:
>
> BigArray = Split(StringList, " ")
>
> I was trying to avoid looping (for no real reason). I wasn't sure if could
> still populate all the rows in the first dimension (like i did above) of the
> Array if had in fact dimensioned it as 2D array. so I was wondering can you
> add a dimension to the array after I have populated the rows in the first
> dimension or can dim as 2D and still use the syntax above to populate 1D?
>
The following will produce a BigArray of one dimension, bounds 0 to 2:

Dim BigArray
ReDim BigArray(0 To 0, 0 To 2)
StringList = "foo1 foo2 foo3"
BigArray = Split(StringList, " ")

But if the functions in the freely downloadable file at
http://home.pacbell.net/beban are available to your workbook, the
following will produce a 2-D array with the first row loaded, bounds 0
to 0, 0 to 2:

Dim BigArray
StringList = "foo1 foo2 foo3"
BigArray = Split(StringList, " ")
BigArray = TwoD(BigArray)

Alan Beban

ExcelMonkey <ExcelMon...@discussions.microsoft.com> wrote...
>Is it possible to create a second dimension to an array that has
>already had its first dimension filled with data:
>
>BigArray = Split(StringList, " ")
>
>I want to redimension BigArray so that it now has a second
>dimension. Or do I dimension with 2D upfront and then add the
>outcome to the split to the first dimension. Can you do that? I have
>been trying to avoid looping.

If you absolutely have to do this without looping, you could use
arrays of arrays, e.g.,

Dim v As Variant
ReDim v(0 To 1)
v(0) = Split("a b c d e f g h i j k l m n o p")
v(1) = Array(1, 2, 2, 2, 1, 2, 2, 2, 1, 2, 2, 2, 2, 2, 1, 2)
Debug.Print v(1)(5); v(0)(9)

Or, if your array isn't too big (unlikely since you're loading it from
a string using Split),

v = Split("a b c d e f g h i j k l m n o p")
v = Application.WorksheetFunction.Transpose(v)
ReDim Preserve v(LBound(v, 1) To UBound(v, 1), 1 To 6)
v = Application.WorksheetFunction.Transpose(v)

While this doesn't require EXPLICIT looping, the implementations of
Transpose and ReDim Preserve almost certainly perform looping. Your
code may look nicer to you, but you won't gain much if any execution
speed.

Alan Beban wrote:
> . . .
> But if the functions in the freely downloadable file at
> http://home.pacbell.net/beban are available to your workbook, the
> following will produce a 2-D array with the first row loaded, bounds 0
> to 0, 0 to 2:
>
> Dim BigArray
> StringList = "foo1 foo2 foo3"
> BigArray = Split(StringList, " ")
> BigArray = TwoD(BigArray)
>
> Alan Beban

Of course, having the first dimension bounds 0 to 0 doesn't leave room
for adding elements. To end up with first dimension bounds of, e.g., 0
to 5, add a line to the above:

ResizeArray BigArray,0,5

Or just use

Dim BigArray
StringList = "foo1 foo2 foo3"
BigArray = Split(StringList, " ")
ResizeArray BigArray, 0, 5, 0, 2

To get Harlan Grove's suggested code

v = Split("a b c d e f g h i j k l m n o p")
v = Application.WorksheetFunction.Transpose(v)
ReDim Preserve v(LBound(v, 1) To UBound(v, 1), 1 To 6)
v = Application.WorksheetFunction.Transpose(v)

to work I had to add ReDim v at the beginning; otherwise I got an
Invalid ReDim error message (in xl2002). And then the result (within the
variant variable) was a 1-based Variant() array--because that's what the
built-in Transpose function does--rather than a 0-based String() array.

If it were important to you for BigArray to be a true String() array
rather than a String() array within a variant variable (I doubt it is),
you could use

Dim BigArray() As String
ReDim BigArray(0 To 0)
StringList = "foo1 foo2 foo3"
Assign Split(StringList, " "), BigArray
ResizeArray BigArray, 0, 5, 0, 2

Alan Beban

Alan Beban wrote:
> . . .
> To get Harlan Grove's suggested code
>
> v = Split("a b c d e f g h i j k l m n o p")
> v = Application.WorksheetFunction.Transpose(v)
> ReDim Preserve v(LBound(v, 1) To UBound(v, 1), 1 To 6)
> v = Application.WorksheetFunction.Transpose(v)
>
> to work I had to add ReDim v at the beginning; otherwise I got an
> Invalid ReDim error message (in xl2002).

Sorry. Harlan Grove obviously intended the above snippet to follow the
Dim Statement he had provided, i.e.,

Dim v As Variant
ReDim v(0 To 1)
v(0) = Split("a b c d e f g h i j k l m n o p")
v(1) = Array(1, 2, 2, 2, 1, 2, 2, 2, 1, 2, 2, 2, 2, 2, 1, 2)
Debug.Print v(1)(5); v(0)(9)

Alan Beban