#VALUE! Error from UDF

[John Pierce]

The following UDF works perfectly - except for the first line, i.e.
when CheckDate is a Sunday or Holiday. The result is #VALUE! but I
want just a blank or empty cell. How can I suppress or fix this?

Public Function RecissionDate(CheckDate, DaysAfter As Integer,
Holidays As Range) As Date
Dim BDADate As Date
Dim i As Integer

If isSunday(CheckDate) Or isHoliday(CheckDate, Holidays) Then
BDADate = ""

Else
BDADate = CheckDate
For i = 1 To DaysAfter
BDADate = BDADate + 1
If isSunday(BDADate) Or isHoliday(BDADate, Holidays)
Then
BDADate = BDADate + 1
Else: BDADate = BDADate
End If
If isSunday(BDADate) Or isHoliday(BDADate, Holidays)
Then
BDADate = BDADate + 1
Else: BDADate = BDADate
End If
Next i
End If
SunTrustRecissionDate = BDADate
End Function

 

[atpgroups]

The following UDF works perfectly - except for the first line, i.e.
when CheckDate is a Sunday or Holiday. The result is #VALUE! but I
want just a blank or empty cell. How can I suppress or fix this?

The problem is that you have defined the function to return a Date,
and "" isn't a valid date. (The date datatype is a specialised numeric
type)
If you change the "...) As Date" to "...) As Variant" it should
work.

Also note that an "If" doesn't need an "Else", and your "Else"s seem
to do nothing.

 

[John Pierce]

The problem is that you have defined the function to return a Date,
and "" isn't a valid date. (The date datatype is a specialised numeric
type)
If you change the "...)  As Date" to "...) As Variant" it should
work.

Also note that an "If" doesn't need an "Else", and your "Else"s seem
to do nothing.

Thanks, changing 'as Date' to 'as Variant' in both places fixed it.