How do you find Leapyear in VBA?

Question

What is a good implementation of a IsLeapYear function in VBA?

Edit: I ran the if-then and the DateSerial implementation with iterations wrapped in a timer, and the DateSerial was quicker on the average by 1-2 ms (5 runs of 300 iterations, with 1 average cell worksheet formula also working).

Answer 1

As a variation on the Chip Pearson solution, you could also try

Public Function isLeapYear(Yr As Integer) As Boolean  

  ' returns FALSE if not Leap Year, TRUE if Leap Year  

  isLeapYear = (DAY(DateSerial(Yr, 3, 0)) = 29)  

End Function

Answer 2

Here's another simple option.

Leap_Day_Check = Day(DateValue("01/03/" & Required_Year) - 1)

If Leap_Day_Check = 28 then it is not a leap year, if it is 29 it is.

VBA knows what the date before 1st March is in a year and so will set it to be either 28 or 29 February for us.

Answer 3

Public Function isLeapYear(Yr As Integer) As Boolean  

    ' returns FALSE if not Leap Year, TRUE if Leap Year  

    isLeapYear = (Month(DateSerial(Yr, 2, 29)) = 2)  

End Function  

I originally got this function from Chip Pearson's great Excel site.

Pearson's site

Answer 4

If efficiency is a consideration and the expected year is random, then it might be slightly better to do the most frequent case first:

public function isLeapYear (yr as integer) as boolean
    if (mod(yr,4)) <> 0 then isLeapYear  = false
    elseif (mod(yr,400)) = 0 then isLeapYear  = true
    elseif (mod(yr,100)) = 0 then isLeapYear  = false
    else isLeapYear = true
end function

Answer 5

Late answer to address the performance question.

TL/DR: the Math versions are about 5x faster

---

I see two groups of answers here

1. Mathematical interpretation of the Leap Year definition
2. Utilize the Excel Date/Time functions to detect Feb 29 (these fall into two camps: those that build a date as a string, and those that don't)

I ran time tests on all posted answers, an discovered the Math methods are about 5x faster than the Date/Time methods.

---

I then did some optimization of the methods and came up with (believe it or not Integer is marginally faster than Long in this case, don't know why.)

Function IsLeapYear1(Y As Integer) As Boolean
    If Y Mod 4 Then Exit Function
    If Y Mod 100 Then
    ElseIf Y Mod 400 Then Exit Function
    End If
    IsLeapYear1 = True
End Function

For comparison, I came up (very little difference to the posted version)

Public Function IsLeapYear2(yr As Integer) As Boolean
    IsLeapYear2 = Month(DateSerial(yr, 2, 29)) = 2
End Function

The Date/Time versions that build a date as a string were discounted as they are much slower again.

The test was to get IsLeapYear for years 100..9999, repeated 1000 times

Results

• Math version: 640ms
• Date/Time version: 3360ms

---

The test code was

Sub Test()
    Dim n As Long, i As Integer, j As Long
    Dim d As Long
    Dim t1 As Single, t2 As Single
    Dim b As Boolean

    n = 1000

    Debug.Print "============================="
    t1 = Timer()
    For j = 1 To n
    For i = 100 To 9999
        b = IsYLeapYear1(i)
    Next i, j
    t2 = Timer()
    Debug.Print 1, (t2 - t1) * 1000

    t1 = Timer()
    For j = 1 To n
    For i = 100 To 9999
        b = IsLeapYear2(i)
    Next i, j
    t2 = Timer()
    Debug.Print 2, (t2 - t1) * 1000
End Sub

Answer 6

I see many great concepts that indicate extra understanding and usage of date functions that are terrific to learn from... In terms of code efficiency.. consider the machine code needed for a function to execute

rather than complex date functions use only fairly fast integer functions BASIC was built on GOTO I suspect that something like below is faster

  Function IsYLeapYear(Y%) As Boolean
     If Y Mod 4 <> 0 Then GoTo NoLY ' get rid of 75% of them
     If Y Mod 400 <> 0 And Y Mod 100 = 0 Then GoTo NoLY
     IsYLeapYear = True

NoLY:

 End Function