Error 1004: Application Defined or object defined error

[Guest]

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

 

[Chip Pearson]

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"

 

[paul.robinson]

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