I popped by here when researching the 255 Transpose Limit, as I expect others have and may. I got a bit thrown of course, but finally straightened it out in my brain, and so thought I could make a worthwhile contribution for others passing in the future.
There are two issues here, which may not be immediate obvious.
_1) The Transpose function does not like it if it is working on a Variant element type array, where one or more of the array elements are a string of more than 255 characters.
If we are dealing with 1 dimensional arrays, as in the original question, then there is a way to get over this without looping, and still using the Transpose function: Use the Join Function on the Variant array (with arbitrary separator), then use Split function on that. We then end up with a String array, and the Transpose is happy with any elements with more than 255 characters in them
This next demo coding almost gets what was wanted here, and variations of it may be sufficient for some people having an issue with the 255 Transpose Limit.
Sub RetVariantArrayToRange() '
Let ActiveSheet.Range("M2:M5") = TransposeStringsOver255()
End Sub
Function TransposeStringsOver255()
Dim myArray(3) As Variant 'this the variant array I will attempt to write
' Here I fill each element with more than 255 characters
myArray(0) = String(300, "a")
myArray(1) = String(300, "b")
myArray(2) = String(300, "c")
myArray(3) = String(300, "d") '
' Let TransposeStringsOver255 = Application.Transpose(myArray()) ' Errors because Transpose does not work on a Variant type array if any element is a string greater than 255 characters
Dim strTemp As String, myArrayStr() As String
Let strTemp = Join(myArray(), "|")
Let myArrayStr() = Split(strTemp, "|")
Let TransposeStringsOver255 = Application.Transpose(myArrayStr())
End Function
_2) That last coding does not do exactly what was wanted. The specific requirement was along these lines, (if using the function above) :
…..select an area of 4 rows x 1 column and type "=TransposeStringsOver255()" into the formula bar (do not enter the quotes). and hit (control + shift + enter)…..
That last coding does not work to do exactly that.
As Tim Williams pointed out, the final array seems to need to be a String array (even if being held in a Variant variable ). Why that should be is a mystery, since the demo coding above seems to work as a workaround to Transpose Strings Over 255 in a Variant Array To a Range.
To get over the problem, we loop the array elements into a String array. Then the mysterious problem goes away.
This next coding would be the last coding with that additional bit
Function TransposeStringsOver255VariantArrayToSelectedRange()
Dim myArray(3) As Variant 'this the variant array I will attempt to write
' Here I fill each element with more than 255 characters
myArray(0) = String(300, "a")
myArray(1) = String(300, "b")
myArray(2) = String(300, "c")
myArray(3) = String(300, "d") ' -
' Let TransposeStringsOver255VariantArrayToSelectedRange = Application.Transpose(myArray()) ' Errors because Transpose does not work on a Variant type array if any element is a string greater than 255 characterts
Dim strTemp As String, myArrayStr() As String
Let strTemp = Join(myArray(), "|")
Let myArrayStr() = Split(strTemp, "|")
' Let TransposeStringsOver255VariantArrayToSelectedRange = Application.Transpose(myArrayStr()) ' Errors because "Seems like you need to return a string array" Tim Williams: https://stackoverflow.com/a/35399740/4031841
Dim VarRet() As Variant
Let VarRet() = Application.Transpose(myArrayStr())
Dim strRet() As String, Rw As Long
ReDim strRet(1 To UBound(VarRet(), 1), 1 To 1)
For Rw = 1 To UBound(VarRet(), 1)
Let strRet(Rw, 1) = VarRet(Rw, 1)
Next Rw
Let TransposeStringsOver255VariantArrayToSelectedRange = strRet()
End Function
To compare in the watch window:
The first coding ends up getting this array, which in many situations will get the job done for you
https://i.postimg.cc/fWYQvsTy/c-Transpose-Strings-Over255.jpg
But for the exact requirement of this Thread, we need what the second coding gives us, which is this:
https://i.postimg.cc/FRL585yP/f-Transpose-Strings-Over255-Variant-Array-To-Selected-Range.jpg
_.______________________________________-
Since we are now having to loop through each element, then we might just as well forget about the Transpose function , and change the loop slightly to do the transpose at the same time
Function TransposeStringsOver255VariantArrayToSelectedRange2()
Dim myArray(3) As Variant 'this the variant array I will attempt to write
' Here I fill each element with more than 255 characters
myArray(0) = String(300, "a")
myArray(1) = String(300, "b")
myArray(2) = String(300, "c")
myArray(3) = String(300, "d") ' -
Dim strRet() As String, Rw As Long
ReDim strRet(1 To UBound(myArray()) + 1, 1 To 1)
For Rw = 1 To UBound(myArray()) + 1
Let strRet(Rw, 1) = myArray(Rw - 1)
Next Rw
Let TransposeStringsOver255VariantArrayToSelectedRange2 = strRet()
End Function
We have now arrived at a solution similar to that from Tim Williams.
(One thing that initially threw me off a bit, was the second function from Tim Williams, as some smart people told me that to get an array out of a function, then it must be
Function MyFunc() As Variant
I never saw a function like
Function MyFunc() As String()
Before)
Hoping this bit of clarification may help some people passing as I did
Alan
