Example of how to programatically create a long drop-down list in Excel

Long drop-down example.bas

Sub Macro4()
    'longVal is collectively more than 255 characters
    Dim longVal As String: longVal = "Lorem,ipsum,dolor,sit,amet,consectetur,adipiscing,elit.,Fusce,dolor,dui,eleifend,in,blandit,ac,,vulputate,ac,purus.,In,sollicitudin,dolor,at,massa,interdum,vulputate.,Etiam,eu,bibendum,elit.,Lorem,ipsum,dolor,sit,amet,consectetur,adipiscing,elit.,Fusce,dolor,dui,eleifend,in,blandit,ac,vulputate,ac,purus."
    With ActiveSheet.Range("a1").Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:=longVal
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
End Sub