End of month Function

Hello everyone,

I need some help or for someone to point me in the right direction. I would like to create a function in VBA that will return the last day of the month when a date is passed to it. It needs to be accurate for all years including leap years. I know that excel treats dates as numbers. Does this mean that i have to have heaps of if functions and say if the data is between x and y then z is the end of the month. I can't think of any other way to do it. Does anyone have an pointers for me. Thanks in advance.

This will get the last day of any month and eyar, etc

VB Code:

MsgBox DateAdd("d", -Day(DateAdd("m", 1, Date)), DateAdd("m", 1, Date))