VBA Excel-Validation causes corrupt workbook file

user_3957090 0
Viewed 3038

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?

xlsx validation vba excel
2 Answers

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.

user_39570900

Option:

I like the validation as replacement of comboboxes as they do not need any active-X component nor additional form-elements are required.

However, I set up a system of spread-sheets with a central AddIn that gets list entries from a central database. The list is set to all validations. User defined functions are then used, to get values corresponding to the selection in the validation cell.

Instead of dynamically filling the validation strings on Workbook_Open, I changed to a method using named ranges instead.

The Addin has a named range (say AddinList) that gets its contents using a user defined matrix-VBA-function returning the list as array (say public function myList() as variant).

In any consumer workbook, another name is defined referring to the external Addin-name (say consumerList=addin.xla!AddinList). Finally, any validation in the consumer workbook is defined as a list that may contain any of the =consumerList entries.

The only requirement is, that the addin-file is open, wich is the case anyways, if the consumer workbook is linked to the addin.

user_39570900
Related