Excel VBA: Need Workaround for 255 Transpose Character Limit When Returning Variant Array to Selected Range

Question

I am struggling with a common problem involving an apparent Excel 255-character-limit. I encounter an error when attempting to return a variant-array from a Function to the selected range on the worksheet. When each of the cells in the Function's returning array are under 255 characters, they post to the sheet just as they should: one element appears in each cell within the selected range. However, if any element in my returning variant array is longer than 255 characters I get a Value! error. These errors are bad because I need my long elements and want to keep the data together!

Versions of this problem appear over and over again in many forums, yet I am able to find a clear simple, all-purpose solution for returning variant arrays to the selected range (not necessarily containing formulas) when the array cells exceed 255 characters. My largest elements are around 1000, but it would be better if the solution could accommodate elements up to 2000 characters.

Preferably, I want this to be implemented with a function, or lines of additional code which can be added to my function (not a subroutine). My reason for wanting to avoid subroutines: I do not want to have to hard-code any ranges. I want this to be flexible and for the output location to be dynamically based on my current selection.

Please, if you can help find a way to produce a function, which takes a Variant Array as input, and which maintains the desired array:cell 1:1 relationship, I'd appreciate it greatly.

So this function with short cells works:

Function WriteUnder255Cells()

Dim myArray(3) As Variant  'this the variant array I will attempt to write

' Here I fill each element with less than 255 characters
' it should output them if you call the function properly.
myArray(0) = "dog"
myArray(1) = "cat"
myArray(2) = "bird"
myArray(3) = "fly"

WriteUnder255Cells = Application.Transpose(myArray())

End Function

But this fuction, with cells exceeding 255 will not output.

Function WriteOver255Cells()

Dim myArray(3) As Variant  'this the variant array I will attempt to write

' Here I fill each element with more than 255 characters
' exceeding the 255-character limit causes the VALUE! errors when you output them
myArray(0) = "ThequickbrownfoxjumpedoverthelazydogThequickbrownfoxjumpedoverthelazydogThequickbrownfoxjumpedoverthelazydogThequickbrownfoxjumpedoverthelazydogThequickbrownfoxjumpedoverthelazydogThequickbrownfoxjumpedoverthelazydogThequickbrownfoxjumpedoverthelazydogThequickbrownfoxjumpedoverthelaxydog"
myArray(1) = "ThequickbrownfoxjumpedoverthelazydogThequickbrownfoxjumpedoverthelazydogThequickbrownfoxjumpedoverthelazydogThequickbrownfoxjumpedoverthelazydogThequickbrownfoxjumpedoverthelazydogThequickbrownfoxjumpedoverthelazydogThequickbrownfoxjumpedoverthelazydogThequickbrownfoxjumpedoverthelazydog"
myArray(2) = "ThequickbrownfoxjumpedoverthelazydogThequickbrownfoxjumpedoverthelazydogThequickbrownfoxjumpedoverthelazydogThequickbrownfoxjumpedoverthelazydogThequickbrownfoxjumpedoverthelazydogThequickbrownfoxjumpedoverthelazydogThequickbrownfoxjumpedoverthelazydogThequickbrownfoxjumpedoverthelazydog"
myArray(3) = "ThequickbrownfoxjumpedoverthelazydogThequickbrownfoxjumpedoverthelazydogThequickbrownfoxjumpedoverthelazydogThequickbrownfoxjumpedoverthelazydogThequickbrownfoxjumpedoverthelazydogThequickbrownfoxjumpedoverthelazydogThequickbrownfoxjumpedoverthelazydogThequickbrownfoxjumpedoverthelazydog"

WriteOver255Cells = Application.Transpose(myArray())

End Function

This is how you produce the output and results:

First you need to create the two modules(to insert one function into each module, paste the code from one into the respective module). To run "WriteUnder255Cells()", select an area of 4 rows x 1 column on the sheet (this where you return the module) and type "=WriteUnder255Cells()" into the formula bar (do not enter the quotes). Note these are called like array formulas, so instead of hitting (enter) to create the output, you need to hit (control + shift + enter). Repeat the same process for WriteOver255Cells() to produce the errors.

Here are some documents/forums discussions which address it. The solutions seem to be either overly specific or clunky because they evoke subroutines (which I want to avoid):

https://support.microsoft.com/en-us/kb/213841

http://www.mrexcel.com/forum/excel-questions/852781-visual-basic-applications-evaluate-method-255-character-limit.html

Excel: Use formula longer that 255 characters

VBA code error when array value exceeds 255 characters

http://dailydoseofexcel.com/archives/2005/01/10/entering-long-array-formulas-in-vba/

https://forums.techguy.org/threads/solved-vba-access-to-excel-255-char-limit-issue.996495/

http://www.mrexcel.com/forum/excel-questions/494675-255-character-cell-limit-visual-basic-applications-workaround.html

Array formula with more than 255 characters

http://www.mrexcel.com/forum/excel-questions/388250-size-limit-transferring-variant-range-excel-2007-a.html

Answer 1

This works for me:

Function Over255()

    Dim myArray(3)  As String '<<<<< not variant

    myArray(0) = String(300, "a")
    myArray(1) = String(300, "b")
    myArray(2) = String(300, "c")
    myArray(3) = String(300, "d")

    'Over255 = Application.Transpose(myArray())
    Over255 = TR(myArray)

End Function

'like Application.Transpose...
Function TR(arrIn) As String()
    Dim arrOut() As String, r As Long, ln As Long, i As Long

    ln = (UBound(arrIn) - LBound(arrIn)) + 1
    ReDim arrOut(1 To ln, 1 To 1)
    i = 1
    For r = LBound(arrIn) To UBound(arrIn)
        arrOut(i, 1) = arrIn(r)
        i = i + 1
    Next r
    TR = arrOut

End Function

Seems like you need to return a string array and Application.Transpose doesn't do that