Page 1 of 2 12 LastLast
Results 1 to 10 of 14

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

  1. #1
    New Member
    Join Date
    Mar 2018
    Posts
    46
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default 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

  2. #2
    Board Regular
    Join Date
    Jun 2005
    Location
    London
    Posts
    7,761
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    1 Thread(s)

    Default 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))
    Neil

  3. #3
    Board Regular
    Join Date
    Jun 2005
    Location
    London
    Posts
    7,761
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    1 Thread(s)

    Default 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)
    Neil

  4. #4
    New Member
    Join Date
    Mar 2018
    Posts
    46
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default 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?

  5. #5
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    35,318
    Post Thanks / Like
    Mentioned
    93 Post(s)
    Tagged
    33 Thread(s)

    Default Re: VBA Leap Year/Non-Leap Year Calculation

    Quote Originally Posted by njimack View Post
    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))
    Last edited by Rick Rothstein; Mar 14th, 2018 at 05:28 PM.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  6. #6
    Board Regular
    Join Date
    Jun 2005
    Location
    London
    Posts
    7,761
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    1 Thread(s)

    Default 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
    Neil

  7. #7
    New Member
    Join Date
    Mar 2018
    Posts
    46
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default 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

  8. #8
    New Member
    Join Date
    Mar 2018
    Posts
    46
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Leap Year/Non-Leap Year Calculation

    It makes numDays =31. This make it add extra dates
    Quote Originally Posted by njimack View Post
    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

  9. #9
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    35,318
    Post Thanks / Like
    Mentioned
    93 Post(s)
    Tagged
    33 Thread(s)

    Default Re: VBA Leap Year/Non-Leap Year Calculation

    Quote Originally Posted by johnston View Post
    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 & ")")
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  10. #10
    New Member
    Join Date
    Mar 2018
    Posts
    46
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Leap Year/Non-Leap Year Calculation

    Quote Originally Posted by Rick Rothstein View Post
    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
    Last edited by johnston; Mar 14th, 2018 at 06:25 PM.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •