Data validation is a wonderful thing. I know it has its shortfalls (and I always hope for improvements in each Office release) but, nevertheless, I do use it a lot in my spreadsheets – particularly data validation lists.
The source for a data validation list comes in 2 basic flavours: a comma separated string or a range. If you play around with the user interface [Data > Validation > Allow: List > Source xxx] you’ll quickly discover that the character limit on the comma separated string is 255 characters (tested in Excel 2013). This limitation isn’t often going to be a problem (and you could always use a range if it were) but what is interesting is that the Excel Object Model will let you bypass it.
This little piece of code creates a comma separated string of 276 characters and creates a data validation list in A1 with it.
Sub foo() Dim strarrValidation(0 To 40) As String Dim strValidation As String Dim i As Long For i = LBound(strarrValidation) To UBound(strarrValidation) strarrValidation(i) = "Item" & CStr(i) Next i strValidation = Join$(strarrValidation, ",") Debug.Print Len(strValidation) '276 With Range("A1").Validation .Delete .Add Type:=xlValidateList, Formula1:=strValidation End With End Sub
Sure enough, if you run it and then click on the data validation dropdown you’ll find it goes from
Now let’s check if this long list can be saved. Save the file as an .xlsm, close it in Excel, browse to the file in Windows explorer and change its file extension to .zip. Then open the zip file and browse through xl > worksheets and open the sheet’s xml file. If you track the
formula1 element to the right you’ll find that all 41 items were saved down in the file.
Now for the gotcha. Change the file extension back and try opening it in Excel. Excel will tell you it found a problem with the file and, if you choose to repair it, the data validation list will get stripped out:
Unfortunately the repairs log file doesn’t give any extra information such as why the data validation was removed – but it is because it exceeded the character limit.
Potentially one could add the long list in during the
Workbook_Open event and then delete it in the
Workbook_BeforeSave event to avoid the repair issues – but I haven’t found a good use for bypassing the character limit using code. Have you?
Encountered this same problem a while back. A pricer sheet asks the quant library what fields to draw and any validation to be added. If the user then saves the sheet some of the fields have lists greater than 256 and we got the corrupt on open. The solution you arrived at has worked well to date for me (except in rare cases of events being left disabled before save). I write the validation lists to a CustomXLPart and delete and then load back in on open.
We found an issue like this where the 256 limit can be bypassed in VBA, but then after we start copying those data validated cells around in Excel VBA, the GUI will stop responding after a while.
It suggested to me that there is a reason the Excel GUI doesn’t let you add in more than 256 characters……