title: Can I use VBA function to return a (dynamic) list of acceptable values into Excel's data validation?
body: <p>For a given cell, I select Data/Validation and set Allow to "List".  I now wish to set Source like so:</p>
  
  <p>=rNames(REGS)</p>
  
  <p>but that does not work (name not found).  So I go Insert/Name/Define and create "REGNAMES" by simply assigning the formula above (no cell range).  I then return to the Data/Validation and when I set Source like so:</p>
  
  <p>=REGNAMES</p>
  
  <p>Now I get "Source currently evaluates to error".  Unfortunately, this error does not go away even after I ignore it.  I can create a range formula in the sheet like so:</p>
  
  <p>{=REGNAMES}</p>
  
  <p>and drag this to the right across a couple cells and the rNames function faithfully returns</p>
  
  <p>Option #1 | Options #2 | ...</p>
  
  <p>That is, the function returns a range as intended.</p>
  
  <p>I know that I can use macro code to manipulate the List setting for that cell out of VBA.  I don't like these side-effects much.  I would prefer a clean dependency tree built on functions.  Any ideas how to get the Data/Validation to accept the array values returned from rNames?</p>
  
  <p>Thanks.</p>
  
  <p>PS: rNames returns the result range as a Variant, if that has any bearing.</p>