Number of the days in a month - vba

[pauloalex]

Hello, everybody!

I'm trying to catch the number of the days in a given month with a variable. To do that, I'm using the follow line:

dias_do_mes = Day(Application.WorksheetFunction.EoMonth(Application.WorksheetFunction.Date(Range("K3").Value, Range("J3").Value, 1), 0))

Month.xlsm
	J	K
2	Month	Year
3	1	2021
Planilha1

But I'm getting the follow message:

Do somebody know how to solve it?

Thanks in advance!

 

[jasonb75]

Try

Dim dias_do_mes As Long
dias_do_mes = Evaluate("=Day(EoMonth(Date(" & Range("K3").Value & "," & Range("J3").Value & ",1),0))")

 

[mohadin]

TRy this

 nb_days = Day(DateSerial(Range("K3").Value, Range("J3").Value + 1, 1) - 1)

 

[Dave Patton]

Either of the following will yield the number of days

T202101b.xlsm
	J	K	L	M
3	1	2021	31	31
4
3a

Cell Formulas
Range	Formula
L3	L3	=DAY(EOMONTH(DATE(K3,J3,1),0))
M3	M3	=DAY(DATE(K3,J3+1,0))

 

[pauloalex]

@jasonb75 @mohadin @Dave Patton You guys are Awesomes! Thank you very much! I'm learning a lot from you!

 

[jasonb75]

Taking the DateSerial function that @mohadin used (which I had forgotten about), borrowing one of @Dave Patton's formulas, and throwing in a bit of shorthand.

dias_do_mes = day(dateserial([K3],[J3]+1,0))

I'm sure there are many other ways as well although some would be less practical, using Format instead of Day is one such alternative that comes to mind.