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.

LongList

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.

LongListXML

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:

Data validation repair list

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?

Advertisements

About Colin Legg

RAD Developer Microsoft MVP - Excel 2009 - 2014
This entry was posted in Microsoft Excel and tagged , , , . Bookmark the permalink.

One Response to Naughty Data Validation Lists

  1. Ali Glasby says:

    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.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s