#VALUE! from Function()
 

I've written a function() that validates the interrelationship
between 3 cells. If the data is valid, the function looks up
the date from one of the cells in a table of holidays and
returns the name of the holiday. It's acceptable for all
3 cells to be empty (no lookup is done in that case) and
certain combinations of the 3 cells containing/
not-containing data are also valid.

When I run the function with all 3 cells empty, I get the
#VALUE! error. Can anyone spot what's triggering
the #VALUE! error, or suggest further debugging ideas?

Here is the code that runs when the #VALUE! error
occurs. I've added DEBUGGING messages to make
it easier to describe here what route the code takes.

DEBUGGING message (1) DOES occur and confirms
the empty contents of all 3 cells.

DEBUGGING message (2) DOES occur and confirms
that the code has recognized that all 3 cells are empty.

DEBUGGING message (3) DOES occur and shows
that the return value is set to "ERROR(7)".

DEBUGGING message (4) DOES NOT OCCUR,
confirming that the function() did in fact return with
its return value as set in message (3), and that No
further code was executed in the function().

CODE Snippet _________________________

Function checkHolidayHours( _
Optional holidayDate As Variant, _
Optional hours As Variant, _
Optional optHolFlag As Variant, _
Optional holidayDates As Range, _
Optional holidayNames As Range)

Dim holidayName As Variant
'(1) DEBUGGING
MsgBox ("Input to Function:" & vbCrLf & _
",holidayDate=" & holidayDate & _
",hours=" & hours & _
",optHolFlag=" & optHolFlag)

If (Len(holidayDate) = 0) Then
If (Len(hours) = 0 & Len(optHolFlag) = 0) Then
'(2) DEBUGGING
MsgBox ("Entry has no data")
checkHolidayHours = "ERROR(7)"
Else
MsgBox ("Hours and/or Optional Holiday Flag but no Date")
checkHolidayHours = "ERROR(2)"
End If
'(3) DEBUGGING
MsgBox ("Function return value is " & checkHolidayHours)
Return
End If

'(4) DEBUGGING
MsgBox ("Entry has data")

TIA

Phil

#VALUE! from Function()
 

I didn't get a solution from either forum where I posted this message. I
found the error to be in using a RETURN statement instead of an EXIT
statement where I wanted an immediate return from the function, rather than
letting the funtion exit dy default at the END FUNCTION statement.

Kindof suprised that the compiler did not flag the RETURN statement, which
apparently is not a legal VBA statement.


"pwrichcreek" wrote:


I've written a function() that validates the interrelationship
between 3 cells. If the data is valid, the function looks up
the date from one of the cells in a table of holidays and
returns the name of the holiday. It's acceptable for all
3 cells to be empty (no lookup is done in that case) and
certain combinations of the 3 cells containing/
not-containing data are also valid.

When I run the function with all 3 cells empty, I get the
#VALUE! error. Can anyone spot what's triggering
the #VALUE! error, or suggest further debugging ideas?

Here is the code that runs when the #VALUE! error
occurs. I've added DEBUGGING messages to make
it easier to describe here what route the code takes.

DEBUGGING message (1) DOES occur and confirms
the empty contents of all 3 cells.

DEBUGGING message (2) DOES occur and confirms
that the code has recognized that all 3 cells are empty.

DEBUGGING message (3) DOES occur and shows
that the return value is set to "ERROR(7)".

DEBUGGING message (4) DOES NOT OCCUR,
confirming that the function() did in fact return with
its return value as set in message (3), and that No
further code was executed in the function().

CODE Snippet _________________________

Function checkHolidayHours( _
Optional holidayDate As Variant, _
Optional hours As Variant, _
Optional optHolFlag As Variant, _
Optional holidayDates As Range, _
Optional holidayNames As Range)

Dim holidayName As Variant
'(1) DEBUGGING
MsgBox ("Input to Function:" & vbCrLf & _
",holidayDate=" & holidayDate & _
",hours=" & hours & _
",optHolFlag=" & optHolFlag)

If (Len(holidayDate) = 0) Then
If (Len(hours) = 0 & Len(optHolFlag) = 0) Then
'(2) DEBUGGING
MsgBox ("Entry has no data")
checkHolidayHours = "ERROR(7)"
Else
MsgBox ("Hours and/or Optional Holiday Flag but no Date")
checkHolidayHours = "ERROR(2)"
End If
'(3) DEBUGGING
MsgBox ("Function return value is " & checkHolidayHours)
Return
End If

'(4) DEBUGGING
MsgBox ("Entry has data")

TIA

Phil