UDF to set cell number style

J

Jan Kuèera

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
 
S

shg

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.
 
R

Rick Rothstein

'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.
 
Top