Mar 25

Need to Determine the Last Day of a Month in VBA?

Here's a code snippet that shows how:

strDate = DateValue(“01/01/2000”)
strMonth = Month(strDate)
stryear = Year(strDate)
MsgBox DateSerial(stryear, strMonth + 1, 0)

Using a zero for the day argument of the DateSerial function causes VBA to return the last day of the month. Note that you have to add 1 to the month argument to get the end of the current month, otherwise DateSerial would return the last day of the previous month.