String character limit to add a csv validation list via VBA

David Kingston 21 Reputation points
2022-03-23T21:17:31.893+00:00

Is there a method for adding long comma separated variable strings to cell validation via VBA that doesn't involve listing the variables somewhere else in the workbook in a named range? I get error 1004 with any string greater than 16,382 characters.

This code works:

Dim sList As String
Dim lChar As Long

For lChar = 1 To 1638
    sList = sList & "abcdefghi," '16,380 characters
Next lChar
sList = sList & "12" '16,382 characters

With Cells(1, 1)
    .Validation.Delete
    .Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertWarning, Formula1:=sList
End With

This code doesn't:
Dim sList As String
Dim lChar As Long

For lChar = 1 To 1638
    sList = sList & "abcdefghi," '16,380 characters
Next lChar
sList = sList & "123" '16,383 characters

With Cells(1, 1)
    .Validation.Delete
    .Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertWarning, Formula1:=sList
End With

Any help or advice is greatly appreciated. Thanks in advance.
DK

{count} votes