Excel Validation - IsEmpty or #CALC!?-CodePudding

I'm trying to add some validation to cell D16 which is configured using a list.

Normally this cell will show a list of options for the user to select. However if they haven't added values to another sheet, this list will show #CALC!

Currently I've got:

Set Check = ThisWorkbook.Sheets("Home").Range("D16")
If IsEmpty(Check.Value) Then
    MsgBox "Warning...."
End If

This works when no option is selected, but if the user hasn't added the options to the other sheet, the list offers #CALC! and if the user selects that the check doesn't work.

The value of the option will always be over 10 characters, so I've tried:

   If (Len(Check.Value) < 10) Then
       MsgBox "Warning...."
   End If

But that doesn't work.

Is there any way to check for a blank entry or #CALC! or check the length of whats in the cell and would that include #CALC! ?

Thanks

CodePudding user response：

Is there any way to check for a blank entry or #CALC! or check the length of whats in the cell and would that include #CALC! ?

The Value() property of a Range returns a Variant. This is a datatype which may be a number, a string, an array, or several other types of data. If a spreadsheet cell has an error in it (displayed as #NUM!,#VALUE!,#CALC! etc) then this is a separate Error type, and can be tested using IsError().

Each error has a number associated with it: for #CALC! this is 2050. If you want to check the error was specifically #CALC! you can compare the value to CvErr(2050), or convert to a string representation using CStr():

Sub TestError()
    Dim v as Variant
    v = CVErr(2050)
    
    If IsError(v) Then
        Debug.Print CStr(v)
    End If
End Sub

which prints (in the Immediate window):

Error 2050