Error 1004: Application Defined or object defined error
 

i am trying to create a simple code (before i make it more complicated) but i
fall on the first hurdle.

what is wrong with this?

Public Function returndate(inidate As Date) As Date

Dim calcdate As Double

calcdate = CDbl(inidate + 5)
On Error GoTo Errfunction
Application.Worksheets("Dates Lookup").Range("D6").Value = calcdate

Errfunction:

MsgBox Err.Description
MsgBox Err.Number

End Function

Error 1004: Application Defined or object defined error
 

First of all, you should have an 'Exit Function' immediately
before your Errfunction label. But more importantly, if you are
trying to call this function from a worksheet cell, it won't work
because you are attempting the change the value of a cell. A
function that is called from a worksheet cell may only return a
value to the calling cell. It may not change any part of the
Excel environment, and that includes changing the value of a
cell.

Try

Public Function returndate(inidate As Date) As Date
Dim calcdate As Double
calcdate = CDbl(inidate + 5)
On Error GoTo Errfunction
returndate = calcdate
Exit Function
Errfunction:
MsgBox Err.Description
MsgBox Err.Number
End Function



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com




"Panagiotis Marantos"
wrote in message
...
i am trying to create a simple code (before i make it more
complicated) but i
fall on the first hurdle.

what is wrong with this?

Public Function returndate(inidate As Date) As Date

Dim calcdate As Double

calcdate = CDbl(inidate + 5)
On Error GoTo Errfunction
Application.Worksheets("Dates Lookup").Range("D6").Value =
calcdate

Errfunction:

MsgBox Err.Description
MsgBox Err.Number

End Function

Error 1004: Application Defined or object defined error
 

Hi
you can't change the value on a sheet inside a function. So you must
seperate the bit of code that defines the function from the bit of code
that writes to the worksheet. You can test the code below with a date
in A1 of oyur Dates LookUp sheet.

Public Function returndate(inidate As Date) As Date
returndate = inidate + 5
End Function

Sub Writedate()
Dim mydate As Date
On Error GoTo Errfunction
mydate = Worksheets("Dates Lookup").Range("A1").Value
Application.Worksheets("Dates Lookup").Range("D6").Value =
returndate(mydate)

Errfunction:
MsgBox Err.Description
MsgBox Err.Number
End Sub

Note that the name of the function must be used inside the function to
return its value. Your use of Cdbl(inidate+5) would create a problem
too as it is a Double, not a date.

regards
Paul

Panagiotis Marantos wrote:
i am trying to create a simple code (before i make it more complicated) but i
fall on the first hurdle.

what is wrong with this?

Public Function returndate(inidate As Date) As Date

Dim calcdate As Double

calcdate = CDbl(inidate + 5)
On Error GoTo Errfunction
Application.Worksheets("Dates Lookup").Range("D6").Value = calcdate

Errfunction:

MsgBox Err.Description
MsgBox Err.Number

End Function