title: Pass array variable to Excel named range for data validation list
body: <blockquote>
    <p>This question has no satisfactory answer. I will be happy to vote for
    any new responses that contain workarounds.</p>
  </blockquote>
  
  <p>How to pass array variable to Excel named range without prior dumping it to cells? I want such a dynamic named range to be used in data validation list. I do not want to set up validation list directly in VBA through the array variable: </p>
  
  <pre><code>Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
      xlBetween, Formula1:=Join(ReturnArr, ",")
  </code></pre>
  
  <p>because of limitation of the length of <code>Formula1</code> parameter to <a href="https://docs.microsoft.com/en-us/office/vba/api/excel.validation.add#parameters" rel="nofollow noreferrer">255 characters</a>. So the above code works fine as long as the <code>Len(Join(ReturnArr, ","))</code> does not exceed 255 characters. </p>
  
  <p>I have tried this:</p>
  
  <pre><code>Option Explicit
  
  Function ReturnArr() As Variant
      Dim Arr(0 To 2)
      Arr(0) = "Spinosaur"
      Arr(1) = "T-Rex"
      Arr(2) = "Triceratops"
  
      ReturnArr = Arr
  End Function
  </code></pre>
  
  <p>Then I add a named range pointing to the function returning array:<br>
  <a href="https://i.stack.imgur.com/p3PQx.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/p3PQx.png" alt="enter image description here"></a></p>
  
  <p>Everything seems to be ok so far. I set up data validation list.
  <a href="https://i.stack.imgur.com/phhNm.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/phhNm.png" alt="enter image description here"></a></p>
  
  <p>I have also tried the following paths with no success:</p>
  
  <ul>
  <li>transposing the array: <code>ReturnArr = Application.Transpose(Arr)</code></li>
  <li><code>INDEX(ReturnArr(),,1)</code></li>
  <li>named range with <code>=OFFSET(ReturnArr(),0,0,ROWS(ReturnArr()),1)</code></li>
  <li>I event descended to such low level as to take use of one cell and I put the formula <code>=OFFSET(ReturnArr(),0,0,ROWS(ReturnArr()),1)</code> into <code>A1</code> cell and referenced to it with <code>INDIRECT("$A$1")</code></li>
  <li>I experimented with undocumented secret <code>EVALUATE</code> function which works with named ranges as in this <a href="https://stackoverflow.com/a/39351425/1903793">case</a>. I tried <code>EVALUATE</code> with all combinations of other functions returning array. </li>
  </ul>
  
  <p>Further references:<br>
  <a href="https://stackoverflow.com/questions/54984247/fast-way-to-output-array-into-range">Fast way to output array into range</a> for those who would like to contribute to dumping approch</p>