Data Validation limitations and oddities

[JenniferMurphy]

In another thread, it was suggested to me to give Data Validation a try as a way of reducing the need to do such extensive data checking in the UDF. So I did a little studying. It looks like it will make my life easier, but, as if often the case with Microsoft products, it comes with some puzzling gotchas, too.

Here are the limitations and oddities that I encountered. Please let me know if I misunderstood something.

1. All of the numeric settings (Whole number, Decimal, Date, Time, & Text length) require a range to be specified. So there is no way to specify that it is “any integer”. I have to specify a range even if any integer is OK with me. Setting “not between” to “1” and “1” will cause an error for “1”, even though “1” is not between “1” and “1”.
2. When a new validation setting is assigned, it will not check the existing cell contents, so as long as it is left alone, invalid data will remain in that cell. I consider this a bug.
3. In a cell with a Text length validation rule, Excel will evaluate a formula and it will pass the validation test if the length of the result is within the specified range. The formula can be any length. I guess this is right, but it's weird (to me).
4. The Date rule will allow date+time for any date in the range except the last one. I guess they are testing that it is <= the end date and not < the end date + 1. This is technically wrong as 11 pm on the last date is still the last date. I consider this a bug.
5. The List option is handy, but, sadly, lacks an auto-fill capability. I have to type the complete selection, click on the dropdown, or press Alt+Down Arrow and scroll to the desired selection. I consider this a design bug and a fairly serious one.

I have often suspected that the development team at M$FT was run by Greek gods who are famous for inventing eternal tortures such as for Prometheus & Sisyphus.

PS: I just discovered that checking the Circle Invalid Data option will circle data that is invalid, but was entered before the validation rule was applied.

 

[JenniferMurphy]

PPS: I just discovered another odd limitation. The Custom validation rule does not allow functions in add-in modules. Very odd.

 

[RoryA]

If you define a name that uses the UDF, then use the name as the DV formula, it works.

 

[JenniferMurphy]

If you define a name that uses the UDF, then use the name as the DV formula, it works.

How, exactly, do I do that? I couldn't get it to work.

 

[RoryA]

Simply use the UDF in the refersto box, passing any required arguments. If those arguments are ranges, and should be relative to the cell you are applying the DV to, then you need to follow the usual rules of relative referencing in names, namely that all the relativities need to be based on the active cell when you define the name.

 

[JenniferMurphy]

Simply use the UDF in the refersto box, passing any required arguments. If those arguments are ranges, and should be relative to the cell you are applying the DV to, then you need to follow the usual rules of relative referencing in names, namely that all the relativities need to be based on the active cell when you define the name.

That worked, thanks. I'd sure like to hear someone justify why that works, but putting the call to the UDF in the custom validation field doesn't. Greek Gods. Torture.

 

[JenniferMurphy]

Simply use the UDF in the refersto box, passing any required arguments.

I wrote a little UDF:

Function IsInteger(ByVal pValue) As Boolean
  If Not IsNumeric(pValue) Or IsEmpty(pValue) Then IsInteger = False: Exit Function
  IsInteger = pValue = Int(pValue)
End Function

Then I created the name CallIsInteger defined as "=isinteger(Sheet1!C11)". (This was done from C11.)

Then I created a Data Validation rule for C11 with Allow: = Custom & Formula: = "=CallIsInteger".

Then I set a breakpoint (F9) on the first line of the IsInteger UDF.

Finally, I entered some data into C11, both valid and not. The validation appears to be working, but the breakpoint in the UDF was never triggered.

And just to make sure that my UDF really is getting called, I changed it to one line:

IsInteger = pValue = "XYZ"

And then anything but "XYZ" got flagged as an error.



????? What is going on?

 

[JenniferMurphy]

PS: I also tried putting a Stop statement in the UDF, but that did not case a breakpoint either.

 

[RoryA]

You can't debug them when called by DV. You also really should have error handling. Any unhandled errors can lead to events not running (though it's rare).

 

[Rick Rothstein]

Here are the limitations and oddities that I encountered. Please let me know if I misunderstood something.

1. All of the numeric settings (Whole number, Decimal, Date, Time, & Text length) require a range to be specified. So there is no way to specify that it is “any integer”. I have to specify a range even if any integer is OK with me. Setting “not between” to “1” and “1” will cause an error for “1”, even though “1” is not between “1” and “1”.

1. 
   You can choose "Whole number" and then select "not equal to" and then specify a number that is so large it would never be entered (9999999999 for example).
   
   
   By the way, you missed one of "Data Validations" biggest (to me) limitations which (to me) makes it somewhat worthless (and a reason to choose event coding over "Data Validation")... you can copy/paste "bad" values into a cell with "Data Validation" and that "bad" value will be accepted even if it does not meet the data validation rule for that cell.