Find Number of Days in a month using VBA (Excel 2007)

Calculate Number of Days in a 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 No_of_Days_in_Month()

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

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

' if you want the days count
MsgBox "No of days in current month := " & Day(EndDate)


End Function

The above code will work only in Excel 2007