Re: 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" <Ngan@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:DB3E16BB-BD50-42D8-9374-594B36CFBD04@xxxxxxxxxxxxxxxx
> 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 Work***
>
> Set xlap = Application
> Set wks = xlap.Active***
>
> 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


.