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

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

Jon

In the VBE, go Tools, References and select the atpvbaen.xls option. EOMONTH should work.


Tony

----- Jon wrote: -----

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

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