Leap Year functions in VBA

There are a few ways to determine whether a given year is a leap year. One way is to check the remainder after dividing the year by 400 and 100 and 4. I can't seem to follow how that works. Here are some more:

About Calculating Leap Years

Determine if a Given Year is a Leap Year

My favorite is the one that checks if February 29th of a given year is a valid date. Very simple and shifts the burden onto VBA. I'd also like to suggest another test — if a given year has 366 days, it's also a leap year.

Public Function IsLeapYear(yr As Long) As Boolean
Const LEAP_YEAR_DAYS As Long = 366

Dim startYear As Date
Dim endYear As Date

startYear = CDate("1/1/" & yr)
endYear = CDate("1/1/" & (yr + 1))

' the difference between Jan 1st of the following year
' and the given year will be 366 in leap years
IsLeapYear = (endYear - startYear = LEAP_YEAR_DAYS)

End Function

Sample usage:

Sub TestLeapYear()
  Debug.Print IsLeapYear(1988)
End Sub
About JP

I'm just an average guy who writes VBA code for a living. This is my personal blog. Excel and Outlook are my thing, with a sprinkle of Access and Word here and there. Follow this space to learn more about VBA. Keep Reading »

Share This Article:

Share and bookmark this articledelicious buttonfacebook buttonlinkedin buttonstumbleupon buttontwitter button

comment bubble 5 Comment(s) on Leap Year functions in VBA:

  1. Hey Jimmy -

    Not sure if it affects your code or not, but … not every year divisible by 4 is a leap-year. Check out the official definition here: http://tinyurl.com/y9l7wfr


  2. I'm kicking myself for not having thought to this!

  3. This might be off topic, but I got curious and created these excel formulas.

    Checks if February 29th of a given year is a valid date:
    =MONTH(DATE(A1,2,29))=2 + ENTER

    If a given year has 366 days:
    =DATE(A1,12,31)-DATE(A2-1,12,31)=366 + ENTER

This article is closed to any future comments.
Excel School