4

This question has no satisfactory answer. I will be happy to vote for any new responses that contain workarounds.

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: 

Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
    xlBetween, Formula1:=Join(ReturnArr, ",")

because of limitation of the length of Formula1 parameter to 255 characters. So the above code works fine as long as the Len(Join(ReturnArr, ",")) does not exceed 255 characters.

I have tried this:

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

Then I add a named range pointing to the function returning array:
enter image description here

Everything seems to be ok so far. I set up data validation list. enter image description here

I have also tried the following paths with no success:

  • transposing the array: ReturnArr = Application.Transpose(Arr)
  • INDEX(ReturnArr(),,1)
  • named range with =OFFSET(ReturnArr(),0,0,ROWS(ReturnArr()),1)
  • I event descended to such low level as to take use of one cell and I put the formula =OFFSET(ReturnArr(),0,0,ROWS(ReturnArr()),1) into A1 cell and referenced to it with INDIRECT("$A$1")
  • I experimented with undocumented secret EVALUATE function which works with named ranges as in this case. I tried EVALUATE with all combinations of other functions returning array.

Further references:
Fast way to output array into range for those who would like to contribute to dumping approch

Przemyslaw Remin
  • 6,276
  • 25
  • 113
  • 191
  • 1
    Short Answer: It can't be done that way. See https://answers.microsoft.com/en-us/office/forum/office_2013_release-excel/data-validation-named-range-returning-array/bdb44056-700b-413e-9333-bf1942ac2f2c – Scott Craner Mar 06 '19 at 14:45
  • You can't even use a name as a list of values for data validation. If you insert `={1,2,3}` into `Refers To` of your name, you can use it e. g. as a matrix formula in your worksheet, but not for data validation. – Asger Mar 06 '19 at 19:02
  • Please add a vote for this at user voice! https://excel.uservoice.com/forums/304921-excel-for-windows-desktop-application/suggestions/11008998-data-validation-should-be-allowed-to-come-from-udf – lori_m Mar 06 '19 at 20:15
  • @ScottCraner It can't be done until workaround is found. Please check this https://stackoverflow.com/a/39351425/1903793 as an example of beating Excel limitation commonly regarded as impossible. Thank you for sound foundation that we deal with a challenge that finally turns me on:-) – Przemyslaw Remin Mar 07 '19 at 12:16

1 Answers1

1

You can populate data validation without a named range by using a comma delineated list (A, B, C). You could then put your Array function in place of the array in the below code and that will create your validation list on the cell you specify.

Public Sub DataValidation()
Dim ws As Worksheet
Dim range1 As Range, rng As Range
'change Sheet1 to suit
Set ws = ThisWorkbook.Worksheets("Sheet1")

Set rng = ws.Range("A1")

Dim Arr(0 To 2)
Arr(0) = "Spinosaur"
Arr(1) = "T-Rex"
Arr(2) = "Triceratops"

With rng.Validation
    .Delete 'delete previous validation
    Dim arrVal As String
    For Each arrItem In Arr()
        arrVal = arrVal & ", " & arrItem
    Next
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:=arrVal
End With
End Sub

Found here and modified slightly: How to add data validation to a cell using VBA

This does not use a named range in the traditional sense but uses a string variable from within the script in place of it.

TOTM
  • 107
  • 7
  • I cannot see any advantage in your answer. What do you want to advise? Dumping the array first to the cells and then use the dumped data as a dynamic named range? – Przemyslaw Remin Mar 07 '19 at 12:19
  • I edited my answer to hopefully apply more to the edits you made to your post. – TOTM Mar 07 '19 at 13:58
  • I seek the possibility to populate items of DV list from VBA array variable. My question is how to avoid the intermediate step of writing the array to cells (even in a hidden sheet). Please check if your concept require having the items to be in cells? If yes, then following this path does not bring us any closer to solution. – Przemyslaw Remin Mar 07 '19 at 14:50
  • Unfortunately if you're trying to validate off of a named range, then there has to be a range in a worksheet somewhere to be validated against. Otherwise, you could try to use a `For Each` loop as part of a `Change Event` on the cell to be validated to loop through the array of data to validate against and run your procedure from there. – TOTM Mar 07 '19 at 15:33
  • The DV list is not only for checking if user has input the correct data, but DV is for generating drop-down list to suggest user the correct data. – Przemyslaw Remin Mar 07 '19 at 15:36
  • Hopefully this is what you are looking for. – TOTM Mar 07 '19 at 16:28
  • So you have reached my starting point:-) Please see `Formula1:=Join(ReturnArr, ",")` where ReturnArr is the array variable. You build a long string first and then pass it to Formula1 argument. It will crash if the string is longer then 255 char. – Przemyslaw Remin Mar 07 '19 at 16:45
  • That's the only option I can see for you unfortunately. Hopefully someone else will have a better solution. – TOTM Mar 07 '19 at 17:00