# Thread: VBA Leap Year/Non-Leap Year Calculation Thanks: 0 Likes: 0

1. ## VBA Leap Year/Non-Leap Year Calculation

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  Reply With Quote

2. ## Re: VBA Leap Year/Non-Leap Year Calculation

You can replace all of your code with the following:

Code:
`numDays = Day(WorksheetFunction.EoMonth(DateValue("01-" & monthNum & "-" & Year), 0))`  Reply With Quote

3. ## Re: VBA Leap Year/Non-Leap Year Calculation

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

4. ## Re: VBA Leap Year/Non-Leap Year Calculation

So your saying to replace the "=Range("A6").Resize(numDays, 1), Type:=xlFillDefault
" with the code you listed?  Reply With Quote

5. ## Re: VBA Leap Year/Non-Leap Year Calculation Originally Posted by njimack 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))  Reply With Quote

6. ## Re: VBA Leap Year/Non-Leap Year Calculation

No, replace all of your code with this:

Code:
```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```  Reply With Quote

7. ## Re: VBA Leap Year/Non-Leap Year Calculation

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  Reply With Quote

8. ## Re: VBA Leap Year/Non-Leap Year Calculation

It makes numDays =31. This make it add extra dates Originally Posted by njimack No, replace all of your code with this:

Code:
```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```  Reply With Quote

9. ## Re: VBA Leap Year/Non-Leap Year Calculation Originally Posted by 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
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...
Code:
```  NumDays = Day(DateSerial(YearNum, MonthNum + 1, 0))
Range("A6").Resize(NumDays) = Evaluate("ROW(1:" & NumDays & ")")

```  Reply With Quote

10. ## Re: VBA Leap Year/Non-Leap Year Calculation Originally Posted by Rick Rothstein 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...
Code:
```  NumDays = Day(DateSerial(YearNum, MonthNum + 1, 0))
Range("A6").Resize(NumDays) = Evaluate("ROW(1:" & NumDays & ")")

```
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  Reply With Quote

## User Tag List

code, end, mod, vba, year 