Is This a Leap Year?

Although VBA provides very rich date and time support, it includes no built-in function that will tell you whether a given year is a leap year. Calculating this answer is actually more complex than checking to see whether the year is evenly divisible by four. If that’s all it took, you could just check like this:

' (Assuming that intYear holds the year in question)
' MOD returns the remainder when you divide, so
' the following expression will return True if
' intYear is evenly divisible by 4.
If intYear MOD 4 = 0 Then

But that’s not all there is. The year is defined as the length of time it takes to pass from one vernal equinox to another. If the calendar gains or loses days, the date for the equinox shifts. Because the physical year isn’t exactly 365.25 days in length (as the calendar says it should be), the current calendar supplies three too many leap years every 385 years. To make up for that, years divisible by 100 aren’t leap years unless they’re a multiple of 400. Got all that? (In case you’re concerned, this schedule will result in an error of only three days in 10,000 years. Not to worry….) This means that 1700, 1800, and 1900 weren’t leap years, but 2000 will be.

Yes, you could write the code to handle this yourself, and it’s not all that difficult. But why do it? VBA is already handling the algorithm internally. It knows that the day after February 28 (in all but a leap year) is March 1 but in a leap year it’s February 29. To take advantage of this fact, dhIsLeapYear (shown in Listing 2.16) calculates the answer for you.

Listing 2.16: Is the Specified Year a Leap Year?

Function dhIsLeapYear(Optional varDate As Variant) As Boolean
    ' Is the supplied year a leap year?
    ' Check the day number of the day
    ' after Feb 28 to find out.
    ' Missing? Use the current year.
    If IsMissing(varDate) Then
        varDate = Year(Now)
    ' Is it a date? Then use that year.
    ElseIf VarType(varDate) = vbDate Then
        varDate = Year(varDate)
    ' Is it an integer? Use that value, if it's OK.
    ' Otherwise, use the current year.
    ElseIf VarType(varDate) = vbInteger Then
        ' Only years 100 through 9999 are allowed.
        If varDate < 100 Or varDate > 9999 Then
            varDate = Year(Now)
        End If
    ' If it's not a date or an integer, just use the
    ' current year.
    Else
        varDate = Year(Now)
    End If
    dhIsLeapYear = (Day(DateSerial(varDate, 2, 28) + 1) = 29)
End Function

Most of the code in dhIsLeapYear handles the “optional” parameter; because you can pass either a date or an integer representing a year, you need a larger amount of error-checking code than normal. If you pass nothing at all, the code uses the current year:

If IsMissing(varDate) Then
    varDate = Year(Now)

If you pass a date, the function uses the year portion of the date:

' Is it a date? Then use that year.
ElseIf VarType(varDate) = vbDate Then
    varDate = Year(varDate)

If you pass an integer, the code treats that integer as the year to check. Because VBA can only process years between 100 and 9999, it verifies that your integer falls in that range. If you pass a value that’s neither a date nor an integer, it uses the current year:

ElseIf VarType(varDate) = vbInteger Then
    ' Only years 100 through 9999 are allowed.
    If varDate < 100 Or varDate > 9999 Then
        varDate = Year(Now)
    End If
' If it's not a date or an integer, just use the
' current year.
Else
    varDate = Year(Now)
End If

For example, you might try calling the procedure in any of these three ways:

If dhIsLeapYear() Then
    ' You know the current year is a leap year.
If dhIsLeapYear(1956) Then
    ' You know 1956 was a leap year.
If dhIsLeapYear(#12/1/92#) Then
    ' You know 1992 was a leap year.

This function compares the date of the day following February 28 to the value 29. If it is 29, the function returns True. Otherwise, it must not be a leap year, and it returns False.

The moral of this story (if there is one) is to let VBA do as much work as possible for you. Although you could have written the dhIsLeapYear function to take into account the algorithm used by the Gregorian calendar, what’s the point? The VBA developers have done that work already. You’ll get better performance (and fewer bugs) by taking advantage of the work that’s already been done.