Following on from my earlier query about storing an array which itself
contains further arrays I have figured out how to do it (and reinvented
a wheel no doubt!) I can simply assign into a holderArray the
individual subArrays bfore filing in a Name.

My query relates to how VBA treats the first subArray differently from
subsequent ones.

I assign the subArray(with four elements) into index one of
holderArray(which is redimmed to have just one element
holderArray(1)=subArray_1 e.g. {Tom, is, new, std}

Once this has been stored in a Name and recovered by assignment to a
variant the Ubound(variant) is 4. These correspond to the 4 elements of
subArray_1. i.e.{Tom, is, new, std}

subArrays added subsequently are treated differently; when I add
another subArray(with four elements {Fred, was, old, std}) to the
holderArray and store in a Name and recover it to a variant, the
Ubound(variant) is 2! These correspond to subArray_1 and subArray_2.
i.e.
{subArray_1,subArray_2}
Tom Fred
is was
new old
std std

In other words, the "orientation" of subArray_1 when it is the sole
subArray in holderArray is "rotated" relative that that it (and all
other subArrays) has when more than one subArray is stored in
holderArray.

My question then is how can I force the orientation of the first
subArray to be consistent with that of subsequent ones? I guess I'm
looking for something like the worksheet TRANSPOSE?

Apologies for the duff example; the real thing is too convoluted to
impose on the group.

Thanks in advance for yr suggestions (and to Peter T for his response
to previous posting)

Matthew

I am not aware of the original post, but could you not just have a
multi-dimension array, such that

ary(1,1) = "Tom"
ary(1,2) = "is"
ary(1,3) = "new"
ary(1,4) = "std"
ary(2,1) = "Fred"
ary(2,2) = "is"
ary(2,3) = "old"
ary(2,4) = "std"

etc.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Matthew Dodds" wrote in message
oups.com...
Following on from my earlier query about storing an array which itself
contains further arrays I have figured out how to do it (and reinvented
a wheel no doubt!) I can simply assign into a holderArray the
individual subArrays bfore filing in a Name.

My query relates to how VBA treats the first subArray differently from
subsequent ones.

I assign the subArray(with four elements) into index one of
holderArray(which is redimmed to have just one element
holderArray(1)=subArray_1 e.g. {Tom, is, new, std}

Once this has been stored in a Name and recovered by assignment to a
variant the Ubound(variant) is 4. These correspond to the 4 elements of
subArray_1. i.e.{Tom, is, new, std}

subArrays added subsequently are treated differently; when I add
another subArray(with four elements {Fred, was, old, std}) to the
holderArray and store in a Name and recover it to a variant, the
Ubound(variant) is 2! These correspond to subArray_1 and subArray_2.
i.e.
{subArray_1,subArray_2}
Tom Fred
is was
new old
std std

In other words, the "orientation" of subArray_1 when it is the sole
subArray in holderArray is "rotated" relative that that it (and all
other subArrays) has when more than one subArray is stored in
holderArray.

My question then is how can I force the orientation of the first
subArray to be consistent with that of subsequent ones? I guess I'm
looking for something like the worksheet TRANSPOSE?

Apologies for the duff example; the real thing is too convoluted to
impose on the group.

Thanks in advance for yr suggestions (and to Peter T for his response
to previous posting)

Matthew

Hello Matthew,

The most part I like in excel is working easier with arrays.
An entire worksheet with all it's cells is available to you store your
arrays, temporarily or permanently, work with them the way you wish to
play, transpose them, cut them short, redim etc.

I could not get exactly how you are working with your arrays, but what I
understand is you want to transpose your array.

So you can add a worksheet in the workbook, work with it as long as you
want and when you are done, delete the worksheet.

Before creating an sub-array add a worksheet in the workbook, name it
programatically as say 'array-sheet' and work with it.

creat an subarray, find the last unsued row(or column) and add the array
to the sheet at last unused row (or column).

When you want to transpose, copy the UsedRage in that sheet, delete the
UsedRange, and pastespecial in the same sheet transposing it.
Then count the rows and columns in the UsedRange in this sheet and
accordingly redeem your transposed array, assign the values, when done
delete the sheet.

Sharad

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Thanks! It works! I'm stunned (and feel ever so slightly silly for not
having tried it, er, somewhat earlier)

Cheers

Matthew

Matthew,

Don't feel bad, that is why they pay us then big bucks :-).

Bob


"Matthew Dodds" wrote in message
oups.com...
Thanks! It works! I'm stunned (and feel ever so slightly silly for not
having tried it, er, somewhat earlier)

Cheers

Matthew