VBA Excel-Validation causes corrupt workbook file

Question

In Excel VBA a cell-validation may be defined using the following function:

myRange.Validation.add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= xlBetween, Formula1:=myListString

If myListString is longer than 255 characters, Excel reports an error when opening the file and tries to 'repair' the file without concise informations. All validations, diagrams, buttons and shapes are removed during repait and the file is not really useful afterwards.

Is there a way to repair the .xlsx file manually?

Answer 1

Thank You, Ron Rosenfeld for the discussion and hints.

In Excel validations of list-type, the string that either holds a range-name or range-adress may not be longer than 255 characters. Manually it is not possible to define longer strings. In VBA, however, the string length of the property formula1 is not limited.

Workbooks with such overlength-validations may be stored in any format without any problems.

When re-opening them, the old Excel 2003-format (*.xls) just shows empty lists instead of the overlength-validation-string. Newer formats (*.xlsx, *.xlsm and *.xlsb) are reported corrupt and are crudly repaired by dropping many items, as described in the quesiton.

It is possible to repair corrupt *.xlsx, *.xlsm files by untzipping their contents to a directory and editing the sheetn.xml - file. There the validation string may be edited and shortened. I used 7zip to unzip the file-contents, notepad++ to pretty-print the xml and edit it and 7zip to rezip the file.

The validation in the uncompressed file xl\worksheets\sheet1.xml might look like the following:

<dataValidations count="1">
    <dataValidation type="list" allowBlank="1" showInputMessage="1" showErrorMessage="1" sqref="A1">
        <formula1>"C16 (S7),C24 (S10),C30 (S13),C35,C40,D30,D35,D40,D50,D60,D70,GL24h (BS11),GL24c,GL28h (BS14),GL28c,GL32h (BS16),GL32c,GL36h (alt),GL36c (alt),C20,GL20h,GL20c,GL22h,GL22c,GL26h,GL26c,GL30h,GL30c,GLT24,GLT30"</formula1>
    </dataValidation>
</dataValidations>

Reduce the length of the <formula1>-tag, rezip all the files and you may open the file without troubles.