UDF to set cell number style

[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

 

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

 

[Jan Kuèera]

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

Jan