I'd recommend caution re: using On Error statements in utility functions like this. Any custom error handling in the main procedure (ie "On Error GoTo lineNum") will be overwritten by this function, so you'll have to remember to manually change it back any time you use the function in a procedure with custom error handling.
My advice would be to pick a personal rule-- either "Never use On Error statements in utility functions, only in main procedures" or "Only use On Error statements in utility functions, never in main procedures"-- and stick to it.
If you do use them, make sure that you switch back to On Error GoTo 0 for all exits from the function. In the first portion of the "IF" block in your current code, you only switch back if the function returns null:
On Error Resume Next
Dim singleCell As Range
For Each singleCell In cell
'...do stuff, then
If cellWithValidationFound And cellWithoutValidationFound Then
On Error GoTo 0
HasValidation = Null
Exit Function
End If
Next
'On Error GoTo 0 needs to be added here
HasValidation = cellWithValidationFound
In this case, it's possible to avoid the "On Error" statements entirely by using the SpecialCells and Intersect methods. Only caveat is that it assumes that your range's parent worksheet contains at least one blank cell:
Function hasValidation(rng As Range) As Variant
'Find first empty cell in ws and add validation
'This ensures that at least 1 cell in ws contains validation
Dim emptyCell As Range
Set emptyCell = rng.Parent.Cells.SpecialCells(xlCellTypeBlanks).Cells(1)
emptyCell.Validation.Add Type:=xlValidateList, Formula1:="1"
'Get range of all cells in ws with validation, then remove validation from empty cell
Dim validationCells As Range
Set validationCells = rng.Parent.Cells.SpecialCells(xlCellTypeAllValidation)
emptyCell.Validation.Delete
'Get intersection of validation cells and range being tested
Dim rngUnion As Range
Set rngUnion = Intersect(rng, validationCells)
'Determine whether entire/partial range has validation and return value
If rngUnion Is Nothing Then
hasValidation = False
ElseIf rngUnion.Count = rng.Count Then
hasValidation = True
Else
hasValidation = Null
End If
End Function
It'd be possible to design a slightly safer version that accounts for sheets with no blank cells, but that scenario is so unlikely that it's probably not worth the effort.
EDIT: Actually, it turns out that Range.SpecialCells(xlCellTypeBlanks) is way worse than I thought. Not only is it obscenely slow for large ranges, it also only searches the Used Range, not the entire sheet. So you may have no option but to use error handling.
Luckily, you can use the same basic format while incorporating On Error. Since it uses Intersect instead of looping through cells, it should be faster than your version. Also, rather than returning Null in mixed cases, it uses optional parameter that alters how the function treats ranges with a mix of validation/no validation. That's just a stylistic preference, I'm not a big fan of Null. Here's my final version:
Function hasValidation(rng As Range, Optional entireRange As Boolean = False) As Boolean
'Get range of all cells in sheet containing validation
On Error Resume Next
Dim validationCells As Range
Set validationCells = rng.Parent.Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo 0
'If no cells contain validation, return False and exit
If validationCells Is Nothing Then
hasValidation = False
Exit Function
End If
'Get intersection of validation cells and range being tested
Dim rngUnion As Range
Set rngUnion = Intersect(rng, validationCells)
'Determine whether entire/partial range has validation and return value
If rngUnion Is Nothing Then
hasValidation = False
Else
hasValidation = IIf(rngUnion.Count = rng.Count, True, Not entireRange)
End If
End Function
