Programmatically creating validation list

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. A开发者_Python百科ny ideas?

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

Here's a little trick I used, in this "list" is an ArrayList:

Dim ValidateList As String
For Each x In list
ValidateList = ValidateList + x + Chr(44)
Next
 With Sheets(yoursheet).Range(yourCell).Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
     xlBetween, Formula1:=ValidateList
    .IgnoreBlank = True
    .InCellDropdown = True
    .InputTitle = ""
    .ErrorTitle = ""
    .InputMessage = ""
    .ErrorMessage = ""
    .ShowInput = True
    .ShowError = True
End With

Might seem a bit crude, but I think it works without any problems :)