Find Last Day of the Month using VBA (Excel 2007)

Calculate Last Day of the Month

EOMONTH returns the serial number for the last day of the month that is the indicated number of months before or after start_date. Use EOMONTH to calculate maturity dates or due dates that fall on the last day of the month.

Function Last_Day_Of_the_Month()

Dim WrkMonths As Integer
Dim StartDate As Date
Dim EndDate As Date

StartDate = Now
EndDate = WorksheetFunction.EoMonth(StartDate, 0)

MsgBox "Last day of the current month is := " & EndDate

End Function

The above code will work only in Excel 2007