Transpose Function not Working with Long Array Elements

[Ngan]

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

 

[Tom Ogilvy]

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.

 

[Ngan]

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.

 

[Alan Beban]

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

 

[Jim Cone]

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