UDF to set cell number style
 

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

UDF to set cell number style
 

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

UDF to set cell number style
 

'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

UDF to set cell number style
 

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