1004 "Application-defined or object-defined error"

[guiu]

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!

 

[Howard Kaikow]

What version of Excel.?

Function works in Excel 2003.

 

[Guest]

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

On Error goto ErrorHandler

 

[Dave Peterson]

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]

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

 

[tony h]

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

 

[Howard Kaikow]

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