Problem with IsError and Data Validation Type detection

delcampo95

New Member
Joined
May 7, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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):

VBA Code:
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:

VBA Code:
ErrorFlag = IsError(InputCell.Validation.Type)

Am I using IsError in the wrong way?
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
2,036
Office Version
  1. 2010
Platform
  1. Windows
try this which is the usual way of handling error with VBA:
VBA Code:
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
 
Solution

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,856
Office Version
  1. 2019
Platform
  1. Windows
Hi,
welcome to forum

see if this update to your Function does what you want

VBA Code:
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

New Member
Joined
May 7, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
try this which is the usual way of handling error with VBA:
VBA Code:
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

Well-known Member
Joined
Dec 23, 2017
Messages
2,036
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

Yes that will work
 

delcampo95

New Member
Joined
May 7, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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?

VBA Code:
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

New Member
Joined
May 7, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
Hi,
welcome to forum

see if this update to your Function does what you want

VBA Code:
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

Well-known Member
Joined
Dec 23, 2017
Messages
2,036
Office Version
  1. 2010
Platform
  1. Windows
You don't have to put:
VBA Code:
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.
 

Forum statistics

Threads
1,144,572
Messages
5,725,071
Members
422,590
Latest member
Mikeyyy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top