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.

[Jim Thomlinson]

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!
>
>

[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 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

[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]

"guiu" <[email protected]> wrote in message
news:[email protected]...
> 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