VBA Leap Year/Non-Leap Year Calculation

[johnston]

I'm making a table with all the dates in a month. The user picks month and year. I have just the code listed below that calculates leap year for February and all other months of any year. The problem is that it does not calculate February during a non-leap year. The Application-defined or object-defined error message comes up during the autofill part of my code. I bolded the code that triggers the error message. Anything helps!



If monthNum=11 Or monthNum=4 Or monthNum=9 Or monthNum= 6 Then
numDays=30

ElseIf monthNum=2 Then
If year Mod 4 =0 Then
If year Mod 100= 0 Then
If year Mod 400= Then
numDays= 28
Else
numDays =29
End If

numDays=28
Else
numDays=29
End If
End If
Else
numDays=31
End If

Range("A6")=DateSerial(year, monthNum, 1)
Range("A6").Autofill Destination:=Range("A6").Resize(numDays, 1), Type:=xlFillDefault

 

[njimack]

You can replace all of your code with the following:

numDays = Day(WorksheetFunction.EoMonth(DateValue("01-" & monthNum & "-" & Year), 0))

 

[njimack]

Just realised you're writing this to a range of cells, so rather than VBA, just use:
=DAY(EOMONTH("01-"&monthNum&"-"&Year),0)

 

[johnston]

So your saying to replace the "=Range("A6").Resize(numDays, 1), Type:=xlFillDefault

" with the code you listed?​

 

[Rick Rothstein]

Just realised you're writing this to a range of cells, so rather than VBA, just use:
=DAY(EOMONTH("01-"&monthNum&"-"&Year),0)

I am not 100% sure because I cannot test it, but I suspect that formula could give different answers depending on if the user's locale setting is m/d/y or d/m/y. I think you can solve this by changing your "01" (day number) to 13 or more.

This should also work no matter what...

=DAY(DATE(Year, monthNum+1,0))

 

[njimack]

No, replace all of your code with this:

Sub test()
Dim monthNum As Long
Dim numDays As Long
Dim Year As Long
 
numDays = Day(WorksheetFunction.EoMonth(DateValue("01-" & monthNum & "-" & Year), 0))
 
Range(Cells(6, 1), Cells(numDays + 6, 1)) = DateSerial(Year, monthNum, 1)
 
End Sub

 

[johnston]

Thank you, this will give me the correct leap year and non-leap year dates, but it adds three dates from the next month to the next three rows under.
Example:
2/1/2020...
2/29/2020
3/1/2020
3/2/2020

 

[johnston]

It makes numDays =31. This make it add extra dates

No, replace all of your code with this:

Sub test()
Dim monthNum As Long
Dim numDays As Long
Dim Year As Long
 
numDays = Day(WorksheetFunction.EoMonth(DateValue("01-" & monthNum & "-" & Year), 0))
 
Range(Cells(6, 1), Cells(numDays + 6, 1)) = DateSerial(Year, monthNum, 1)
 
End Sub

 

[Rick Rothstein]

I'm making a table with all the dates in a month. The user picks month and year. I have just the code listed below that calculates leap year for February and all other months of any year. The problem is that it does not calculate February during a non-leap year. The Application-defined or object-defined error message comes up during the autofill part of my code. I bolded the code that triggers the error message. Anything helps!



If monthNum=11 Or monthNum=4 Or monthNum=9 Or monthNum= 6 Then
numDays=30

ElseIf monthNum=2 Then
If year Mod 4 =0 Then
If year Mod 100= 0 Then
If year Mod 400= Then
numDays= 28
Else
numDays =29
End If

numDays=28
Else
numDays=29
End If
End If
Else
numDays=31
End If

Range("A6")=DateSerial(year, monthNum, 1)
Range("A6").Autofill Destination:=Range("A6").Resize(numDays, 1), Type:=xlFillDefault

First off, I do not like the name of your Year variable as Year is a built in VB function name, so I am using YearNum instead. With that said, once you have given values to your YearNum and MonthNum variables, these two lines of code will fill the range from cell A6 down to the last day of the month specified in MonthNum for YearNum...

[table="width: 500"]
[tr]
	[td]  NumDays = Day(DateSerial(YearNum, MonthNum + 1, 0))
  Range("A6").Resize(NumDays) = Evaluate("ROW(1:" & NumDays & ")")[/td]
[/tr]
[/table]

 

[johnston]

First off, I do not like the name of your Year variable as Year is a built in VB function name, so I am using YearNum instead. With that said, once you have given values to your YearNum and MonthNum variables, these two lines of code will fill the range from cell A6 down to the last day of the month specified in MonthNum for YearNum...

[TABLE="width: 500"]
<tbody>[TR]
[TD]  NumDays = Day(DateSerial(YearNum, MonthNum + 1, 0))
  Range("A6").Resize(NumDays) = Evaluate("ROW(1:" & NumDays & ")")[/TD]
[/TR]
</tbody>[/TABLE]

Thank you, this is getting me the correct number of days without extra dates, but when its formatted to Short Date it makes the monthNum and yearNum I entered a date like 1/1/1900 even though I entered monthNum as 2 and yearNum as 2018