Programmatically creating validation list

Question

I have an array of data that is coming in to the VBA code from an external source. I want to be able to assign that data to use as a validation in a dropdown box in a cell in one of the sheets in this workbook. However, I do not want to copy that data into a sheet and then use a named range - there may be quite a lot of data, and that would not feel very efficient!

I'm sure there must be a way - but I haven't found one yet. Any ideas?

Solution

1. Place the data in some text file delimiting it with comma eg(a,b,c).

2. Read that data using VBA into a string variable eg ValidationList.

3. Use some thing like this

   With Range("A1").Validation
   .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
   xlBetween, Formula1:=ValidationList
   .IgnoreBlank = True
   .InCellDropdown = True
   .InputTitle = ""
   .ErrorTitle = ""
   .InputMessage = ""
   .ErrorMessage = ""
   .ShowInput = True
   .ShowError = True
   End With