VBA Run Macro Last Day of the Month

Question

I want to return the last day in the month. The month is selected from a drop-down combo box. If I select January, this will return "1/31/2017" but I just want it to return 31. What am I missing?

EndDate = WorksheetFunction.EoMonth(ComboBox1.Value & Year(Date), 0)

Answer 1

The function WorksheetFunction.EoMonth returns a Date, while you want a numeric value representing the Day (of the last day of the month).

So you need a Long variable, and you can use the Day function.

EndDate = WorksheetFunction.EoMonth(ComboBox1.Value & Year(Date), 0)

Dim myDay As Long
myDay = Day(EndDate)