Calculating Last day of month

J

Jon

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
 
R

Rob van Gelder

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 said:
I am having a problem with calculating the last day of the current month
Click to expand...
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
Click to expand...
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
 
A

acw

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
 
B

Bob Phillips

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)
 
Top