Hi all,
if I enter =TODAY() into a cell, its format changes into Date. How could my
own UDF do the same?

I've tried
Public Function YESTERDAY() As Date
YESTERDAY = DateAdd("d", -1, Now)
Application.ThisCell.NumberFormat = "m/d/yyyy"
End Function

But setting the NumberFormat property inside UDF has no effect.
Any way here?

Thanks,
Jan

Can't. A UDF can't do anything except return a value to the cell(s) in
which the function appears. There are some obscure exceptions, but this
isn't one of them.


--
shg
------------------------------------------------------------------------
shg's Profile: http://www.thecodecage.com/forumz/member.php?userid=13
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=24593

'shg' has answered your question, but I just wanted to point out you can
simplify your function as follows...

Public Function YESTERDAY() As Date
YESTERDAY = Date - 1
End Function

This is slightly different than your function in that it does not return the
time. If you wanted the time returned, just use Now instead of Date.

--
Rick (MVP - Excel)


"Jan Kuèera" wrote in message
...
Hi all,
if I enter =TODAY() into a cell, its format changes into Date. How could
my own UDF do the same?

I've tried
Public Function YESTERDAY() As Date
YESTERDAY = DateAdd("d", -1, Now)
Application.ThisCell.NumberFormat = "m/d/yyyy"
End Function

But setting the NumberFormat property inside UDF has no effect.
Any way here?

Thanks,
Jan

Okay, thank you shg for answering and Rick for the pointout!

Jan


"Rick Rothstein" wrote in message
...
'shg' has answered your question, but I just wanted to point out you can
simplify your function as follows...

Public Function YESTERDAY() As Date
YESTERDAY = Date - 1
End Function

This is slightly different than your function in that it does not return
the time. If you wanted the time returned, just use Now instead of Date.

--
Rick (MVP - Excel)


"Jan Kuèera" wrote in message
...
Hi all,
if I enter =TODAY() into a cell, its format changes into Date. How could
my own UDF do the same?

I've tried
Public Function YESTERDAY() As Date
YESTERDAY = DateAdd("d", -1, Now)
Application.ThisCell.NumberFormat = "m/d/yyyy"
End Function

But setting the NumberFormat property inside UDF has no effect.
Any way here?

Thanks,
Jan