Making Excel Crash - Validation Gotcha

Post date: Nov 29, 2012 2:18:35 AM

When using VBA to create validation, we can put values in the validation object that we shouldn't - or - leave out an important piece.

Setup: Using VBA, add VALIDATION.TYPE to 3 (xlValidateList) to a cell and either:

• • Neglect to add a value to FORMULA1 (blank is a value)

  • Add a value that is not a comma separated list

  • Add a comma separated list greater than 255 characters

  • Add a range reference that is not single column

Save and close the workbook.

When the workbook opens, invalid FORMULA1 values will be removed and there is a good chance that when the worksheet with the improper validation displays the workbook will crash. I have replicated this on a few different versions of XL and different PCs.

Counter Measure: Assuming you can get the workbook to open, goto VBA and either delete the offending validation:

-or- correct it: