Excel VBA - getting previous and last DATE from TODAY

[dluhut]

Hello,

In Excel VBA, is there a way to get the beginning date and the last date for the previous month from TODAY()?

i.e. If TODAY is Jan 3, 2014, then I would like to have the beginning date as Dec 1, 2013 and the end date as Dec 31, 2013.
i.e. If TODAY is Mar 18, 2012, then I would like to have the beginning date as Feb 1, 2012 and the end date as Feb 29, 2012 (yes 29, not 28).

I believe you guys have the picture now.

So basically, I need to have the AI to know the beginning of the year (January 2014 - takes Dec 2013) and if it happens to be February, the 'end date' is either 28th or 29th depending on the year.

[zbor]

Use this and add into vba:
start previous month:

Please Login or Register  to view this content.

end previous month

Please Login or Register  to view this content.

[Richard Buttrey]

Do you really need VBA when you could keep these value in a couple of cells. i.e.

Beginning of previous month:

Please Login or Register  to view this content.

Last day of previous month

Please Login or Register  to view this content.

If you really do want a VBA variable then either read the variable from cells like the above or use the VBA equivalent

Please Login or Register  to view this content.

[dluhut]

Hello, Yes I do need it to be done in VBA, since everything is going to be run on VBA, and there's no "input" on excel.

I tried to put the code, but it ask me that "sub or function not defined" error.

[dluhut]

By the way, I am familiar with EOMONTH, but didn't know that it'll take into consideration of the "year" as well...1st scenario that I've put on the 1st thread...that is , if it's Jan 2014, it'll know that beginning date is Dec 1, 2013 and end date is Dec 31, 2013

[zbor]

As RB wrote, you need to use worksheetfunction

[dluhut]

Got it!

Solved!

Reps up to you both!

[Richard Buttrey]

Hello, Yes I do need it to be done in VBA, since everything is going to be run on VBA, and there's no "input" on excel.

I tried to put the code, but it ask me that "sub or function not defined" error.

Hi,

I wasn't suggesting that any 'input' was necessary, other than the very first creation of the two functions in two cells. Once the two functions have been created you could hide them away on a non visible sheet if you wish.