Data Validation LIst from String: OK when saved as xlsb; unreadable when saved as xlsm RRS feed

  • Question

  • Excel 2007; Windows 7 pro x64
    I have also posted in the Answers forum, but no response as yet.

    I generate a data validation list using VBA and using a comma separated string with 122 elements and a length of 1546 characters.  The validation works properly.

    If I save the workbook in xlsm format, and try to reopen it, I get an "Excel found unreadable content" error message.  If I repair it, it tells me it has removed the data validation (which it did).

    BUT, if I save the workbook in binary (.xlsb) format, there is no error message and the validation is retained.

    I don't understand why there should be a behavioural difference, or how to predict it.

    I know that in earlier versions of Excel, there was a 255 character limit on data validation strings. But then it would not work at all.

    Below is the code I used for those that don't want to download.  Note that the range r is the source of the data validation string in this instance.  Also note that although, in this code, I applied the DV to 5200 cells, the problem also occurs when applying it to just a single cell.

    Thanks for any insight.

    Option Explicit
    Sub DVTest()
        Dim s As String
        Dim v() As String
        Dim i As Long
        Dim r As Range
    'This range has 122 entries   
    Set r = Range("A1", Cells(Rows.Count, "A").End(xlUp))
    ReDim v(1 To r.Rows.Count)
    For i = 1 To r.Rows.Count
        v(i) = r(i)
    Next i
    Set r = Range("D2:D5200")
    With r.Validation
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:=Join(v, ",")
        .ErrorMessage = "Add from drop down list"
        .ErrorTitle = "Type Control Labels"
        .InCellDropdown = True
        .InputMessage = "Select from dropdown list"
        .InputTitle = "TCL"
    End With
    End Sub


    Monday, February 25, 2013 12:33 PM

All replies

  • Ron,

    Why not just reference the range with the values?

    With Range("D2:D5200").Validation
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
         xlBetween, Formula1:="=" & Range("A1", Cells(Rows.Count, "A").End(xlUp)).Address
    End With

    Monday, February 25, 2013 2:53 PM
  • Bernie,

       The workbook I have posted is merely an example defining the problem which I am trying to understand -- why the comma-delimited list is failing when opening from xlsm, but not from xlsb (or maybe the question is why it should work at all :-))

    The validation list is generated on the fly from a particular worksheet that has multiple duplicates in the column, unsorted, and cannot be altered.  And the list has to be dynamic. The fastest way I have found to generate this list is to

    • Read the column into an array
    • add the array elements to a collection (to remove the duplicates)
    • place the collection items into an array
    • sort the array
    • using the Join function, produce a comma-delimited string for the Formula1 argument.

    In the application, I would workaround this issue by generating a hidden worksheet where I could place the validation list in a range, but I am trying to avoid that time penalty if possible.

    Since I haven't run across any documented limitations in this area with regard to data validation string length in Excel 2007, I am posting to determine if I have overlooked something, or if this is a known phenomenon.


    Monday, February 25, 2013 3:28 PM
  • Ron,

    Conveniently, I recently ran into the same issue.  I had been dynamically generating validations on each cell on the 'selection change' event.  Like you, I built a string for the validation (although I was using .find over looping).  Anyhow, I kept getting repair issues on validation as well.

    My solution, which isn't pretty, was to strip the validation on those columns beforesave.  Since I build the validation on selection change, I have no need to save it. 

    Tuesday, March 12, 2013 3:10 PM