Skip to main content
Code Review

Return to Answer

Removed unused Range variable "cell"
Source Link
paul bica
paul bica
  • 1.2k
  • 8
  • 15

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, cell 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
        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, cell 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

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, cell 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
        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, cell 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

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
Replaced For loop with Intersect result
Source Link
paul bica
paul bica
  • 1.2k
  • 8
  • 15
Option Explicit

Public Function HasValidation(ByVal rng As Range) As Boolean
    Dim itHas As Boolean, cell 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
        For Each cell In rng
            itHas = Not IsNullIntersect(cell.Validationrng.TypeSpecialCells(xlCellTypeAllValidation)
            If itHas Then Exit For
     , rng) Is NextNothing
    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

Public Function GetValidationRange(ByVal rng As Range) As Range
    Dim vRng As Range, cell 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
  • I updated the answer to preserve the previous error (if any)
Option Explicit

Public Function HasValidation(ByVal rng As Range) As Boolean
    Dim itHas As Boolean, cell 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
        For Each cell In rng
            itHas = Not IsNull(cell.Validation.Type)
            If itHas Then Exit For
        Next
    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

Public Function GetValidationRange(ByVal rng As Range) As Range
    Dim vRng As Range, cell 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
  • I updated the answer to preserve the previous error (if any)
Option Explicit

Public Function HasValidation(ByVal rng As Range) As Boolean
    Dim itHas As Boolean, cell 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
        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

Public Function GetValidationRange(ByVal rng As Range) As Range
    Dim vRng As Range, cell 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
  • I updated the answer to preserve the previous error
Added Version 2 (GetValidationRange())
Source Link
paul bica
paul bica
  • 1.2k
  • 8
  • 15
Option Explicit

Public Function HasValidation(ByVal rng As Range) As Boolean
    Dim itHas As Boolean, cell As Range, eID As Long, eSRC As String, eDESC As String

    eID =If Err.Number <> 0 Then
    If eID <> 0 TheneID = Err.Number
        eSRC = Err.Source
        eDESC = Err.Description
        Err.Clear
    End If
 
    On Error Resume Next
    If Not rng Is Nothing Then
        IfFor rng.Cells.CountLargeEach =cell 1In Thenrng
            itHas = Not IsNull(rngcell.Validation.Type)
        Else
    If itHas Then Exit For
     Dim cell As RangeNext
    End If
    If Err.Number <> For0 EachOr celleID In<> rng0 Then
        If eID = 0 Then Err.Clear Else Err.Raise eID, eSRC, eDESC
    itHasEnd If
    HasValidation = NotitHas
End IsNullFunction

Or GetValidationRange() that returns a Range or Nothing

Public Function GetValidationRange(cell.Validation.TypeByVal rng As Range) As Range
    Dim vRng As Range, cell As Range, eID As Long, eSRC As String, eDESC As String

    If itHasErr.Number <> 0 Then 
 Exit For      eID = Err.Number
        eSRC = Err.Source
  Next      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
    HasValidationEnd If
    Set GetValidationRange = itHasvRng
End Function

.

Option Explicit

Public Function HasValidation(ByVal rng As Range) As Boolean
    Dim itHas As Boolean, eID As Long, eSRC As String, eDESC As String

    eID = Err.Number
    If eID <> 0 Then
        eSRC = Err.Source
        eDESC = Err.Description
        Err.Clear
    End If
 
    On Error Resume Next
    If Not rng Is Nothing Then
        If rng.Cells.CountLarge = 1 Then
            itHas = Not IsNull(rng.Validation.Type)
        Else
            Dim cell As Range
            For Each cell In rng
                itHas = Not IsNull(cell.Validation.Type)
                If itHas Then Exit For
            Next
        End If
    End If
    If Err.Number <> 0 Then If eID = 0 Then Err.Clear Else Err.Raise eID, eSRC, eDESC
    HasValidation = itHas
End Function

Option Explicit

Public Function HasValidation(ByVal rng As Range) As Boolean
    Dim itHas As Boolean, cell 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
        For Each cell In rng
            itHas = Not IsNull(cell.Validation.Type)
            If itHas Then Exit For
        Next
    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, cell 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

.

Added error preservation
Source Link
paul bica
paul bica
  • 1.2k
  • 8
  • 15
Added parameter validation: If Not rng Is Nothing
Source Link
paul bica
paul bica
  • 1.2k
  • 8
  • 15
Source Link
paul bica
paul bica
  • 1.2k
  • 8
  • 15