Transpose Function not Working with Long Array Elements

Hi,
Attached is a simple Macro I wrote to basically paste an array of strings
into a range of cells. If the array elements ("String" data type) are less
than or equal to 255 characters long, the macro works. If the array elements
are more than 255 characters in length, I'll get the following error:
"Run-time error '-2147417848 (800101088): Method 'Transpose' of object
WorksheetFunction failed"

It does not matter how many elements the array has; it's all about the
element length, which is so bizzare. Does anyone know why?

I've searched everywhere for an answer to this weird problem, but no luck so
far.

Thanks for your help in advance! In the code below, the array elements are
exactly 255 characters long, and the macro should work fine. If you just add
1 more character to those elements, the macro will crash.

===========================================
Sub test()
Dim testArray(0 To 1) As String
Dim xlap As Excel.Application
Dim wks As Worksheet

Set xlap = Application
Set wks = xlap.ActiveSheet

testArray(0) =
"123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345"
testArray(1) =
"123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345"

wks.Cells(1, "A").Resize(2).Value =
xlap.WorksheetFunction.Transpose(testArray)

Set wks = Nothing
Set xlap = Nothing
End Sub

Why. I suspect it has to do with the origins of excel. In versions of
excel prior to xl97, text was limited to 255 characters. In xl97, they
added a storage mechanism to store larger strings - up to 32K. However, the
"record" or data structure used to maintain information about the cell still
stores the first 255 characters only. Also, 255 is the max value stored in
8 bits/1 word, so I suspect many structures related to strings only allow a
length of 255 - until the core excel code is rewritten, I suspect
limitations like this will continue to exist.

--
Regards,
Tom Ogilvy


"Ngan" <[email protected]> wrote in message
news:[email protected]...
> Hi,
> Attached is a simple Macro I wrote to basically paste an array of strings
> into a range of cells. If the array elements ("String" data type) are
less
> than or equal to 255 characters long, the macro works. If the array
elements
> are more than 255 characters in length, I'll get the following error:
> "Run-time error '-2147417848 (800101088): Method 'Transpose' of object
> WorksheetFunction failed"
>
> It does not matter how many elements the array has; it's all about the
> element length, which is so bizzare. Does anyone know why?
>
> I've searched everywhere for an answer to this weird problem, but no luck
so
> far.
>
> Thanks for your help in advance! In the code below, the array elements
are
> exactly 255 characters long, and the macro should work fine. If you just
add
> 1 more character to those elements, the macro will crash.
>
> ===========================================
> Sub test()
> Dim testArray(0 To 1) As String
> Dim xlap As Excel.Application
> Dim wks As Worksheet
>
> Set xlap = Application
> Set wks = xlap.ActiveSheet
>
> testArray(0) =
>
"123456789012345678901234567890123456789012345678901234567890123456789012345
6789012345678901234567890123456789012345678901234567890123456789012345678901
2345678901234567890123456789012345678901234567890123456789012345678901234567
8901234567890123456789012345"
> testArray(1) =
>
"123456789012345678901234567890123456789012345678901234567890123456789012345
6789012345678901234567890123456789012345678901234567890123456789012345678901
2345678901234567890123456789012345678901234567890123456789012345678901234567
8901234567890123456789012345"
>
> wks.Cells(1, "A").Resize(2).Value =
> xlap.WorksheetFunction.Transpose(testArray)
>
> Set wks = Nothing
> Set xlap = Nothing
> End Sub

Thanks, Tom! It's so amazing that MS doesn't even have this issue addressed
anywhere. In my actual code, the array elements are constructed dynamically
and their contents are perfectly fine. Only when using the Transpose method
does it crash. I ended up using a FOR loop to paste each array element into
each cell instead of using the Transpose method.

"Tom Ogilvy" wrote:

> Why. I suspect it has to do with the origins of excel. In versions of
> excel prior to xl97, text was limited to 255 characters. In xl97, they
> added a storage mechanism to store larger strings - up to 32K. However, the
> "record" or data structure used to maintain information about the cell still
> stores the first 255 characters only. Also, 255 is the max value stored in
> 8 bits/1 word, so I suspect many structures related to strings only allow a
> length of 255 - until the core excel code is rewritten, I suspect
> limitations like this will continue to exist.
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Ngan" <[email protected]> wrote in message
> news:[email protected]...
> > Hi,
> > Attached is a simple Macro I wrote to basically paste an array of strings
> > into a range of cells. If the array elements ("String" data type) are
> less
> > than or equal to 255 characters long, the macro works. If the array
> elements
> > are more than 255 characters in length, I'll get the following error:
> > "Run-time error '-2147417848 (800101088): Method 'Transpose' of object
> > WorksheetFunction failed"
> >
> > It does not matter how many elements the array has; it's all about the
> > element length, which is so bizzare. Does anyone know why?
> >
> > I've searched everywhere for an answer to this weird problem, but no luck
> so
> > far.
> >
> > Thanks for your help in advance! In the code below, the array elements
> are
> > exactly 255 characters long, and the macro should work fine. If you just
> add
> > 1 more character to those elements, the macro will crash.
> >
> > ===========================================
> > Sub test()
> > Dim testArray(0 To 1) As String
> > Dim xlap As Excel.Application
> > Dim wks As Worksheet
> >
> > Set xlap = Application
> > Set wks = xlap.ActiveSheet
> >
> > testArray(0) =
> >
> "123456789012345678901234567890123456789012345678901234567890123456789012345
> 6789012345678901234567890123456789012345678901234567890123456789012345678901
> 2345678901234567890123456789012345678901234567890123456789012345678901234567
> 8901234567890123456789012345"
> > testArray(1) =
> >
> "123456789012345678901234567890123456789012345678901234567890123456789012345
> 6789012345678901234567890123456789012345678901234567890123456789012345678901
> 2345678901234567890123456789012345678901234567890123456789012345678901234567
> 8901234567890123456789012345"
> >
> > wks.Cells(1, "A").Resize(2).Value =
> > xlap.WorksheetFunction.Transpose(testArray)
> >
> > Set wks = Nothing
> > Set xlap = Nothing
> > End Sub
>
>
>

If the functions in the freely downloadable file at
http://home.pacbell.net/beban are available to your workbook, I believe
that the ArrayTranspose function does not have the limitation that you
are describing for the built-in TRANSPOSE function.

Alan Beban

Ngan wrote:
> Thanks, Tom! It's so amazing that MS doesn't even have this issue addressed
> anywhere. In my actual code, the array elements are constructed dynamically
> and their contents are perfectly fine. Only when using the Transpose method
> does it crash. I ended up using a FOR loop to paste each array element into
> each cell instead of using the Transpose method.
>
> "Tom Ogilvy" wrote:
>
>
>>Why. I suspect it has to do with the origins of excel. In versions of
>>excel prior to xl97, text was limited to 255 characters. In xl97, they
>>added a storage mechanism to store larger strings - up to 32K. However, the
>>"record" or data structure used to maintain information about the cell still
>>stores the first 255 characters only. Also, 255 is the max value stored in
>>8 bits/1 word, so I suspect many structures related to strings only allow a
>>length of 255 - until the core excel code is rewritten, I suspect
>>limitations like this will continue to exist.
>>
>>--
>>Regards,
>>Tom Ogilvy
>>
>>
>>"Ngan" <[email protected]> wrote in message
>>news:[email protected]...
>>
>>>Hi,
>>>Attached is a simple Macro I wrote to basically paste an array of strings
>>>into a range of cells. If the array elements ("String" data type) are
>>
>>less
>>
>>>than or equal to 255 characters long, the macro works. If the array
>>
>>elements
>>
>>>are more than 255 characters in length, I'll get the following error:
>>>"Run-time error '-2147417848 (800101088): Method 'Transpose' of object
>>>WorksheetFunction failed"
>>>
>>>It does not matter how many elements the array has; it's all about the
>>>element length, which is so bizzare. Does anyone know why?
>>>
>>>I've searched everywhere for an answer to this weird problem, but no luck
>>
>>so
>>
>>>far.
>>>
>>>Thanks for your help in advance! In the code below, the array elements
>>
>>are
>>
>>>exactly 255 characters long, and the macro should work fine. If you just
>>
>>add
>>
>>>1 more character to those elements, the macro will crash.
>>>
>>>===========================================
>>>Sub test()
>>> Dim testArray(0 To 1) As String
>>> Dim xlap As Excel.Application
>>> Dim wks As Worksheet
>>>
>>> Set xlap = Application
>>> Set wks = xlap.ActiveSheet
>>>
>>> testArray(0) =
>>>
>>
>>"123456789012345678901234567890123456789012345678901234567890123456789012345
>>6789012345678901234567890123456789012345678901234567890123456789012345678901
>>2345678901234567890123456789012345678901234567890123456789012345678901234567
>>8901234567890123456789012345"
>>
>>> testArray(1) =
>>>
>>
>>"123456789012345678901234567890123456789012345678901234567890123456789012345
>>6789012345678901234567890123456789012345678901234567890123456789012345678901
>>2345678901234567890123456789012345678901234567890123456789012345678901234567
>>8901234567890123456789012345"
>>
>>> wks.Cells(1, "A").Resize(2).Value =
>>>xlap.WorksheetFunction.Transpose(testArray)
>>>
>>> Set wks = Nothing
>>> Set xlap = Nothing
>>>End Sub
>>
>>
>>

N,

If you set up the array vertically, then you can paste directly into the
worksheet without the transpose function...
'----------------------
Sub test()
Dim testArray(0 To 1, 1 To 1) As String
Dim wks As Worksheet

Set wks = ActiveSheet

testArray(0, 1) = "x1234567890... "
testArray(1, 1) = "x1234567890... "

wks.Cells(1, 1).Resize(2).Value = testArray()

Set wks = Nothing
End Sub
'------------------------------

Jim Cone
San Francisco, USA