Problem with IsError and Data Validation Type detection

[delcampo95]

Hi all - I'm at my wits end. I am a forum- and Google-taught VBA coder, and I am trying to figure out some weird behavior with IsError being interpreted strangely in VBA code.

I made a function to return the numerical type for a Data Validation for an input cell (ex: 3 for a List, 7 for Custom), and to return a -1 when there is no Data Validation applied to the cell. Here is my function (I know it is not very streamlined - I have broken it up into big steps to try to troubleshoot):

Function DataValidationType(InputCell As Range) As Variant
'Returns a data validation type from a cell

Dim ErrorFlag As Variant

'Sets function to refresh automatically
Application.Volatile True

'If multiple cells are passed to function, fail with error
If (InputCell.Cells.Count > 1) Then

    DataValidationType = CVErr(xlErrValue)
    Exit Function
    
End If
    
'Set flag if there is no data validation set for this cell
ErrorFlag = IsError(InputCell.Validation.Type)

'If there is no data validation set for this cell, return -1
If ErrorFlag Then

    DataValidationType = -1
    Exit Function

End If

'If no errors encountered, return the validation type (an index number)
DataValidationType = InputCell.Validation.Type

End Function

Instead of returning a -1 upon having no Data Validation, the function returns an error of #VALUE!. It also appears that this function is aborting and returning this value when it gets to the following line:

ErrorFlag = IsError(InputCell.Validation.Type)

Am I using IsError in the wrong way?

 

[offthelip]

try this which is the usual way of handling error with VBA:

Function DataValidationType(InputCell As Range) As Variant
'Returns a data validation type from a cell

Dim ErrorFlag As Variant

'Sets function to refresh automatically
Application.Volatile True

'If multiple cells are passed to function, fail with error
If (InputCell.Cells.Count > 1) Then

    DataValidationType = CVErr(xlErrValue)
    Exit Function
    
End If
    
'Set flag if there is no data validation set for this cell
On Error GoTo invalid
tt = InputCell.Validation.Type
On Error GoTo 0

'If no errors encountered, return the validation type (an index number)
DataValidationType = InputCell.Validation.Type
Exit Function
invalid:
    DataValidationType = -1
    Exit Function
End Function

 

[dmt32]

Hi,
welcome to forum

see if this update to your Function does what you want

Function DataValidationType(ByVal InputCell As Range) As Variant
    
    'Returns a data validation type from a cell
    
    'Sets function to refresh automatically
    Application.Volatile True
    'Set error handling
    On Error GoTo myerror
    'If multiple cells are passed to function, fail with error
    If InputCell.CountLarge > 1 Then Err.Raise xlErrValue
    
    'return the validation type (an index number)
    DataValidationType = InputCell.Validation.Type
    
myerror:
    'If there is no data validation return -1
    If Err <> 0 Then DataValidationType = IIf(Err = xlErrValue, CVErr(xlErrValue), -1)
End Function

Dave

 

[delcampo95]

try this which is the usual way of handling error with VBA:

Function DataValidationType(InputCell As Range) As Variant
'Returns a data validation type from a cell

Dim ErrorFlag As Variant

'Sets function to refresh automatically
Application.Volatile True

'If multiple cells are passed to function, fail with error
If (InputCell.Cells.Count > 1) Then

    DataValidationType = CVErr(xlErrValue)
    Exit Function
   
End If
   
'Set flag if there is no data validation set for this cell
On Error GoTo invalid
tt = InputCell.Validation.Type
On Error GoTo 0

'If no errors encountered, return the validation type (an index number)
DataValidationType = InputCell.Validation.Type
Exit Function
invalid:
    DataValidationType = -1
    Exit Function
End Function

@offthelip - thanks for this code. So if I "couch" a likely error-producing operation between "On Error GoTo", and "On Error GoTo 0", does this give me the ability to several such blocks of error-detecting code to send the subroutine to different results? In other words, could I use this method to have 3 different return values based on 3 different types of errors?

 

[offthelip]

Yes that will work

 

[delcampo95]

Yes that will work

Awesome! You just opened up my world of handling errors! Here's my new code, and I think this works great. It returns a -1 if the user accidentally passed multiple cells to the function, and -2 if no data validation was found. Is this a more "clean" approach to "tidy up" with On Error GoTo 0 after every error-producing opportunity?

Function DataValidationType(InputCell As Range) As Long
'Returns a data validation type from a cell

'Sets function to refresh automatically
Application.Volatile True

'Check for multiple cells, return error code if found
'Set error handling
On Error GoTo MultipleCells
'If multiple cells are passed to function, fail with error
If (InputCell.Cells.Count > 1) Then Err.Raise xlErrValue
'Reset error handling
On Error GoTo 0

'Find data validation type, return error code if none found
'Set error handling
On Error GoTo NoDataValidation
'Return the validation type (an index number), knowing that no validation type will go to the error handler
DataValidationType = InputCell.Validation.Type
'Reset error handling
On Error GoTo 0
    
Exit Function

'Error handler for more than one cell being passed to function
MultipleCells:
'Return a value indicating multiple cells were passed to the function
DataValidationType = -1
Exit Function

'Error handler for no data validation found
NoDataValidation:
'Return a value indicating no data validation was found
DataValidationType = -2
Exit Function

End Function

 

[delcampo95]

Hi,
welcome to forum

see if this update to your Function does what you want

Function DataValidationType(ByVal InputCell As Range) As Variant
   
    'Returns a data validation type from a cell
   
    'Sets function to refresh automatically
    Application.Volatile True
    'Set error handling
    On Error GoTo myerror
    'If multiple cells are passed to function, fail with error
    If InputCell.CountLarge > 1 Then Err.Raise xlErrValue
   
    'return the validation type (an index number)
    DataValidationType = InputCell.Validation.Type
   
myerror:
    'If there is no data validation return -1
    If Err <> 0 Then DataValidationType = IIf(Err = xlErrValue, CVErr(xlErrValue), -1)
End Function

Dave

@dmt32 Thanks for the help - the code you suggested also worked great.

 

[offthelip]

You don't have to put:

On Error GoTo 0

However it is a very good habit to get into if you have one line of code which you are anticipating will throw an error in normal operation. This is because it disables the error handling in the current procedure and returns it to the normal excel error handling and so if another unexpected error occurs further down the normal excel error handling will trap it and give you a clue as to what the cause is. If you don't your special error handler will be called and it is then very confusing because it will look like a different error entirely.