UDF/VBA Function, trap error, avoid #VALUE!

[Guest]

I have created a function that returns a string. When it fails, Excel
reports "#VALUE!". I would like to report my description of the error
instead. I have tried to use error handling, but it seems to be
ignored. In the area in which I "Try to do some useful Stuff", an error

occasionally occurs. The debugger shows that control does not transfer
to my ErrorHandler. Instead, execution ends immediately, and the cell
value is set to "#VALUE!".

Public Function myFunction() As String
On Error GoTo ErrorHandler


' Try to do some useful Stuff


myFunction = "RESULT"


FunctionExit:
Exit Function


ErrorHandler:
Err.Clear
myFunction = "My Error Description"
Resume FunctionExit


End Function


Thanks for your assistance!

 

[JE McGimpsey]

What's the code that causes the abort?

 

[Guest]

This is the exact line:
Set theDateRec = fiscalCalendar.Item(CStr(dateIn))

Note that:
theDateRec is a class, calendarRecord
fiscalCalendar is a collection of calendardRecords
The collection is Keyed by Dates

The error occurs when an invalid date/key is passed.

 

[Guest]

I have created a new function to try to recreate an example function
that mimics my intent:

Public Function HelloWorld(causeError As Boolean) As String

On Error GoTo ErrorHandler
If (causeError) Then
Err.Raise 1234
Else
HelloWorld = "Hello World"
End If

FunctionExit:
Exit Function

ErrorHandler:
Err.Clear
HelloWorld = "ERROR HAPPENED"
Resume FunctionExit

End Function

=HelloWorld(FALSE) returns "Hello World"
=HelloWorld(TRUE) returns #VALUE!

 

[JE McGimpsey]

Hmm... I get

=HelloWorld(FALSE) returns "Hello World"
=HelloWorld(TRUE) returns "ERROR HAPPENED"

I can cause an error if I use an argument that can't be coerced to a
Boolean:

=HelloWorld("TRUE ") returns #VALUE!

 

[Guest]

Hmmm. Well, thanks for your inputs.. I can't figure out why my code
works for you and not me. I've been poking around in Excel options,
etc. to no avail.

Thanks!!

 

[Guest]

I am not too familiar with VBA/Excel, etc. I wonder if I am doing
something different at another level. I choose Tools/Macros/Visual
Basic Editor, then I create new code module and paste this code in
there. Is that the right way? Is that what you are doing as well? I am
trying to figure out our difference.

Thanks!!