Maximum drop-down list/formula length in Excel

Question

I have the following VBA code:

myList = "test"

Range("A1:Z1").Validation.Delete
Range("A1:Z1").Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:=myList

If my list exceeds 256 characters, it is truncated. If it exceeds 1024, I get an execution error (I think it is the maximum length for a formula).

How can I get over those limits? I'm using Excel 2003.

Answer 1

Using the link posted in the comments above, I was able to create a dropdown list with more than 256/1024 chars. Here's what I managed to get after some more searches and tries:

ActiveWorkbook.Names.Add Name:="List", RefersTo:="=ValidationList!$A$1:$A$" & i
Range("A1:AZ1").Validation.Delete
Range("A1:AZ1").Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=List"

Nothing more than this. i is the number of lines of ValidationList sheet. I'm creating that sheet dynamically and populating the first A column with what I want to show up in the dropdown list.

Useful references:

• http://www.contextures.com/xlDataVal11.html
• http://www.ozgrid.com/forum/showthread.php?t=52916
• http://msdn.microsoft.com/en-us/library/office/ff839847.aspx
• http://social.msdn.microsoft.com/Forums/en-US/isvvba/thread/830b42cf-8c97-4aaf-b34b-d860773281f7/
• VBA excel code:cannot give named range to formula1 in validation