title: Excel VBA: Need Workaround for 255 Transpose Character Limit When Returning Variant Array to Selected Range
body: <p>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. <strong>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.</strong> These errors are bad because I need my long elements and want to keep the data together!</p>
  
  <p><strong>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.</strong> My largest elements are around 1000, but it would be better if the solution could accommodate elements up to 2000 characters.</p>
  
  <p>Preferably, I want this to be implemented with a function, or lines of additional code which can be added to my function <strong>(not a subroutine)</strong>. 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.</p>
  
  <p><strong>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.</strong> </p>
  
  <p>So this function with short cells works:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>But this fuction, with cells exceeding 255 will not output.</p>
  
  <pre><code>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
  </code></pre>
  
  <p>This is how you produce the output and results:</p>
  
  <p>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, <strong>you need to hit (control + shift + enter)</strong>. Repeat the same process for WriteOver255Cells() to produce the errors.</p>
  
  <p>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):</p>
  
  <p><a href="https://support.microsoft.com/en-us/kb/213841" rel="nofollow noreferrer">https://support.microsoft.com/en-us/kb/213841</a></p>
  
  <p><a href="http://www.mrexcel.com/forum/excel-questions/852781-visual-basic-applications-evaluate-method-255-character-limit.html" rel="nofollow noreferrer">http://www.mrexcel.com/forum/excel-questions/852781-visual-basic-applications-evaluate-method-255-character-limit.html</a></p>
  
  <p><a href="https://stackoverflow.com/questions/12386414/excel-use-formula-longer-that-255-characters">Excel: Use formula longer that 255 characters</a></p>
  
  <p><a href="https://stackoverflow.com/questions/22999740/vba-code-error-when-array-value-exceeds-255-characters">VBA code error when array value exceeds 255 characters</a></p>
  
  <p><a href="http://dailydoseofexcel.com/archives/2005/01/10/entering-long-array-formulas-in-vba/" rel="nofollow noreferrer">http://dailydoseofexcel.com/archives/2005/01/10/entering-long-array-formulas-in-vba/</a></p>
  
  <p><a href="https://forums.techguy.org/threads/solved-vba-access-to-excel-255-char-limit-issue.996495/" rel="nofollow noreferrer">https://forums.techguy.org/threads/solved-vba-access-to-excel-255-char-limit-issue.996495/</a></p>
  
  <p><a href="http://www.mrexcel.com/forum/excel-questions/494675-255-character-cell-limit-visual-basic-applications-workaround.html" rel="nofollow noreferrer">http://www.mrexcel.com/forum/excel-questions/494675-255-character-cell-limit-visual-basic-applications-workaround.html</a></p>
  
  <p><a href="https://stackoverflow.com/questions/22949221/array-formula-with-more-than-255-characters">Array formula with more than 255 characters</a></p>
  
  <p><a href="http://www.mrexcel.com/forum/excel-questions/388250-size-limit-transferring-variant-range-excel-2007-a.html" rel="nofollow noreferrer">http://www.mrexcel.com/forum/excel-questions/388250-size-limit-transferring-variant-range-excel-2007-a.html</a></p>