Guys,

The following code gives the 1004 "Application-defined or
object-defined error" error when is inside a function:

Function myFunction() As Integer
myFunction = 0
On Error GoTo Error
Worksheets(1).Range("B2").Value = 3.14159
Exit Function
Error:
MsgBox Err.Number & vbCrLf & Err.Description, vbExclamation, "Error
in myFunction"
myFunction = -1
End Function

But the same code works inside a sub:

Sub mySub()
On Error GoTo Error
Worksheets(1).Range("B2").Value = 3.14159
Exit Sub
Error:
MsgBox Err.Number & vbCrLf & Err.Description, vbExclamation, "Error
in mySub"
End Sub

I can't figure out why and can't work around it. Does anyone have any
ideias? Any help would be deeply appreciated.

Thanks in advance!

What version of Excel.?

Function works in Excel 2003.

Error is a reserved word. Change it to something else like Errorhandler

On Error goto ErrorHandler

--
HTH...

Jim Thomlinson


"guiu" wrote:

Guys,

The following code gives the 1004 "Application-defined or
object-defined error" error when is inside a function:

Function myFunction() As Integer
myFunction = 0
On Error GoTo Error
Worksheets(1).Range("B2").Value = 3.14159
Exit Function
Error:
MsgBox Err.Number & vbCrLf & Err.Description, vbExclamation, "Error
in myFunction"
myFunction = -1
End Function

But the same code works inside a sub:

Sub mySub()
On Error GoTo Error
Worksheets(1).Range("B2").Value = 3.14159
Exit Sub
Error:
MsgBox Err.Number & vbCrLf & Err.Description, vbExclamation, "Error
in mySub"
End Sub

I can't figure out why and can't work around it. Does anyone have any
ideias? Any help would be deeply appreciated.

Thanks in advance!

If you're calling =myFunction() from a worksheet cell, then you can't change
other cells in this function. Functions can return values to the cell that
holds them.

guiu wrote:

Guys,

The following code gives the 1004 "Application-defined or
object-defined error" error when is inside a function:

Function myFunction() As Integer
myFunction = 0
On Error GoTo Error
Worksheets(1).Range("B2").Value = 3.14159
Exit Function
Error:
MsgBox Err.Number & vbCrLf & Err.Description, vbExclamation, "Error
in myFunction"
myFunction = -1
End Function

But the same code works inside a sub:

Sub mySub()
On Error GoTo Error
Worksheets(1).Range("B2").Value = 3.14159
Exit Sub
Error:
MsgBox Err.Number & vbCrLf & Err.Description, vbExclamation, "Error
in mySub"
End Sub

I can't figure out why and can't work around it. Does anyone have any
ideias? Any help would be deeply appreciated.

Thanks in advance!

--

Dave Peterson

Howard, it's Excel 2003.
Jim, the On Error works fine, that's how I know what error is
occurring.
Dave, what you say makes sense.

Thank you, all! I'll see what I can do here.
Regards

on which line does the error occur?

and how is the function being called (from code or from a worksheet)?

there are several ways to check the line but the simplest is to comment
out (or remove) the error trap


--
tony h
------------------------------------------------------------------------
tony h's Profile: http://www.excelforum.com/member.php...o&userid=21074
View this thread: http://www.excelforum.com/showthread...hreadid=500401

"guiu" wrote in message
oups.com...
Howard, it's Excel 2003.

I had called the function from within code.

If you use the function in a cell, the error 1004 does occur.

Use

' Worksheets(1).Range("B2").Value = 3.14159
myFunction = 3.14159