I created a named range which is the result of a user definer function.

Let say my rage is named List and has three items, if I select three cells and enter =List and press Ctrl+Shift+Enter, I get my three items in the cells... so everything looks fine.

If I try to use the range as a data validation source (as a list), I get the "Source Currently evaluates to an error".

Is this a limitation of Excel? If not what is wrong, and how can I do?

The function:

Public Function testarrayreturn() As Variant
    Dim Arr(2) As String
    Arr(0) = "a"
    Arr(1) = "b"
    Arr(2) = "c"

    testarrayreturn = Application.Transpose(Arr)
End Function

The named range is defined as testarrayreturn()

  • Is the range on another worksheet ? Data validation is limited to what is on the same sheet as the validation. – ApplePie Aug 14 '14 at 15:53
  • Do you mean that the function must be on the code of the sheet? When there is no user defined function involved, the range can be anywhere, it works fine… – gregseth Aug 14 '14 at 18:55

TL;DR it's not possible as described (cf. this post on SO).

Long(er) answer: there's a workaround. The user-defined function must return a Range not an array of value. So the solution is to use a disposable worksheet to "paste" temporary data.

Since avoiding temporary data is the exact reason for which I wanted to use an UDF, that's too bad, but it can be some use for others.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.