Check if a range in Excel contains DataValidation

Question

One of the answers to Determine if cell contains data validation got me thinking about an edge case I needed to solve. If cell happens to be a multi-cell range what's the best way to answer? The answer below returns False if the range contains both a cell with and without validation.

Function HasValidation(cell As Range) As Boolean
 Dim t: t = Null
 On Error Resume Next
 t = cell.Validation.Type
 On Error GoTo 0
 HasValidation = Not IsNull(t)
End Function

The answer I arrived at allows for non-contiguous ranges. I chose Variant as the return type in the case that there's a mix of validated and non-validated cells. Since it's neither True nor False a Null seemed the best fit.

Public Function HasValidation(ByVal cell As Range) As Variant
 Dim tempVariable As Variant
 If cell.Cells.Count > 1 Then
 On Error Resume Next
 Dim singleCell As Range
 For Each singleCell In cell
 Debug.Print singleCell.Address(True, True)
 tempVariable = Empty
 tempVariable = singleCell.Validation.Type
 If IsEmpty(tempVariable) Then
 Dim cellWithoutValidationFound As Boolean
 cellWithoutValidationFound = True
 Else
 Dim cellWithValidationFound As Boolean
 cellWithValidationFound = True
 End If
 If cellWithValidationFound And cellWithoutValidationFound Then
 On Error GoTo 0
 HasValidation = Null
 Exit Function
 End If
 Next
 HasValidation = cellWithValidationFound
 Else
 tempVariable = Null
 tempVariable = cell.Validation.Type
 HasValidation = IsNull(tempVariable)
 End If
End Function

• IvenBach: No. I was focusing on the multi-cell option that I neglected to properly update a single-cell range.

Answer 1

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

Answer 2

I would suggest that HasValidation() returns either True or False, not maybe?...

---

Option Explicit
Public Function HasValidation(ByVal rng As Range) As Boolean
 Dim itHas As Boolean, eID As Long, eSRC As String, eDESC As String
 If Err.Number <> 0 Then
 eID = Err.Number
 eSRC = Err.Source
 eDESC = Err.Description
 Err.Clear
 End If
 On Error Resume Next
 If Not rng Is Nothing Then
 itHas = Not Intersect(rng.SpecialCells(xlCellTypeAllValidation), rng) Is Nothing
 End If
 If Err.Number <> 0 Or eID <> 0 Then
 If eID = 0 Then Err.Clear Else Err.Raise eID, eSRC, eDESC
 End If
 HasValidation = itHas
End Function

---

Or GetValidationRange() that returns a Range or Nothing

---

Public Function GetValidationRange(ByVal rng As Range) As Range
 Dim vRng As Range, eID As Long, eSRC As String, eDESC As String
 If Err.Number <> 0 Then
 eID = Err.Number
 eSRC = Err.Source
 eDESC = Err.Description
 Err.Clear
 End If
 On Error Resume Next
 If Not rng Is Nothing Then
 Set vRng = Intersect(rng.SpecialCells(xlCellTypeAllValidation), rng)
 End If
 If Err.Number <> 0 Or eID <> 0 Then
 If eID = 0 Then Err.Clear Else Err.Raise eID, eSRC, eDESC
 End If
 Set GetValidationRange = vRng
End Function

---

.

In your code

• The parameter name (cell) implies that it expects a single cell
• The Null return type can cause issues (not very used in normal operations)
• As a user of the function I don't really know what to do with the result
  • Should I be looking some more for cells with validation or not?

---

Edit

@DanielMcCracken has a valid point about changing the error chain

• I updated the answer to preserve the previous error