Naughty Data Validation Lists

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 Item0 to Item40.

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?

Advertisement