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) =
"1234567890123456789012345678901234567890123456789 01234567890123456789012345678901234567890123456789 01234567890123456789012345678901234567890123456789 01234567890123456789012345678901234567890123456789 01234567890123456789012345678901234567890123456789 012345"
testArray(1) =
"1234567890123456789012345678901234567890123456789 01234567890123456789012345678901234567890123456789 01234567890123456789012345678901234567890123456789 01234567890123456789012345678901234567890123456789 01234567890123456789012345678901234567890123456789 012345"

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

Set wks = Nothing
Set xlap = Nothing
End Sub

Transpose Function not Working with Long Array Elements
 

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" wrote in message
...
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) =

"1234567890123456789012345678901234567890123456789 01234567890123456789012345
67890123456789012345678901234567890123456789012345 67890123456789012345678901
23456789012345678901234567890123456789012345678901 23456789012345678901234567
8901234567890123456789012345"
testArray(1) =

"1234567890123456789012345678901234567890123456789 01234567890123456789012345
67890123456789012345678901234567890123456789012345 67890123456789012345678901
23456789012345678901234567890123456789012345678901 23456789012345678901234567
8901234567890123456789012345"

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

Set wks = Nothing
Set xlap = Nothing
End Sub