Jon,

Along the same lines as Rob, but a little shorter


myDate = DateSerial(Year(Date), Month(Date) + 1, 0)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Rob van Gelder" wrote in message
...
Jon,

A cool behaviour of DateSerial is that it accepts values outside the
bounds
of a calendar month/day. This means you can choose month 13, which is
interpreted as month 12 + 1


dtmTemp = Now()
dtmTemp = DateSerial(Year(dtmTemp), Month(dtmTemp) + 1, 1) - 1

Rob


"Jon" wrote in message
...
I am having a problem with calculating the last day of the current month
in excel. If I use the EOMONTH function with parameters NOW() and 0 i.e.
EOMONTH(NOW(), 0) in the cell where I want the date to go everything works
fine. However
I want to put the EOMONTH into a macro at which point the compiler
complain the EOMONTH is not a defined sub or function. Are there any VB
functions that will return the last day of the current month

thank you in advanced

Jon