UDF using Dates

[Guest]

I have wriiten UDF including the =NOW() function in it. When checking using
De-bug it places the result of the UDF in the workbook correctly. When
calling the UDF in the workbook it gives VALUE error. Only have this problem
with Date Functions.
Anyone has a solution please Thanks Guys..Jim.

 

[Bob Phillips]

Post the UDF.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

Hi Bob see below cheers Jim

Function FreezeDate()

ActiveCell.Select


ActiveCell.FormulaR1C1 = "=NOW()"

'this macro places date then copies it to the same cell and changes the cell
to value.
'this allows the date to remain frozen


Selection.Copy

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Selection.NumberFormat = "d/mm/yyyy;@"



End Function

 

[Bob Phillips]

Jim,

You can't do things like that with UDFs. They return a value into the cell,
they cannot change the cell value as you are trying to do, that would
overwrite the function that is running.

Why not just use

=TEXT(TODAY(),"d/mm/yyyy;@")

in the cell?



--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

Hi Bob

Give me the name of your boss and I tell him to pay you double.

Great work Pal and thanks, it works like magic

Keep well

Cheers from sunny Sydney.

 

[Dave Peterson]

This doesn't keep the date frozen, though.

Is there a reason, you just don't hit ctrl-; and format the cell the way you
like?

 

[Guest]

Dave
Sorry i dont follow what you meant.

I agree it did not work.

Jim.

 

[Guest]

Dave

Yes the reason is I have so many to update manually that i want to do this
in a function saves me a ton of time. see the statement earlier.

cheers

jim.

 

[Dave Peterson]

I don't see anything in your earlier posts that describe what you're trying to
accomplish.

If you want a cell in a row to change when something in that row changes, maybe
you can look at the way J.E. McGimpsey approached it:

http://www.mcgimpsey.com/excel/timestamp.html

 

[Guest]

Hi Dave

I am realy trying to do this :

I need to get excel to enter to-days date and then freeze this date so is
does not update later. I dont want to copy-paste special-values. I need to
have it in a function statement.
I tried =IF(C17="not completed","",TEXT(TODAY(),"d/mm/yyyy;@"))

but it still updates even though the cell format is text.

I think i will need to write a macro, but have difficulty linking it with
the worksheet.

I am reading thru' J.E.McGimpsey reply.

cheers.

 

[Dave Peterson]

I think J.E.'s event macro is what you want.

If you have trouble implementing it, post back with your code.

Hi Dave

I am realy trying to do this :

I need to get excel to enter to-days date and then freeze this date so is
does not update later. I dont want to copy-paste special-values. I need to
have it in a function statement.
I tried =IF(C17="not completed","",TEXT(TODAY(),"d/mm/yyyy;@"))

but it still updates even though the cell format is text.

I think i will need to write a macro, but have difficulty linking it with
the worksheet.

I am reading thru' J.E.McGimpsey reply.

cheers.