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).
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.
public function isLeapYear (yr as integer) as boolean
isLeapYear = false
if (mod(yr,400)) = 0 then isLeapYear = true
elseif (mod(yr,100)) = 0 then isLeapYear = false
elseif (mod(yr,4)) = 0 then isLeapYear = true
end function
Wikipedia for more... http://en.wikipedia.org/wiki/Leap_year
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
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
I found this funny one on CodeToad :
Public Function IsLeapYear(Year As Varient) As Boolean
IsLeapYear = IsDate("29-Feb-" & Year)
End Function
Although I'm pretty sure that the use of IsDate in a function is probably slower than a couple of if, elseifs.
Late answer to address the performance question.
TL/DR: the Math versions are about 5x faster
I see two groups of answers here
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
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
Public Function ISLeapYear(Y As Integer) AS Boolean
' Uses a 2 or 4 digit year
'To determine whether a year is a leap year, follow these steps:
'1 If the year is evenly divisible by 4, go to step 2. Otherwise, go to step 5.
'2 If the year is evenly divisible by 100, go to step 3. Otherwise, go to step 4.
'3 If the year is evenly divisible by 400, go to step 4. Otherwise, go to step 5.
'4 The year is a leap year (it has 366 days).
'5 The year is not a leap year (it has 365 days).
If Y Mod 4 = 0 Then ' This is Step 1 either goto step 2 else step 5
If Y Mod 100 = 0 Then ' This is Step 2 either goto step 3 else step 4
If Y Mod 400 = 0 Then ' This is Step 3 either goto step 4 else step 5
ISLeapYear = True ' This is Step 4 from step 3
Exit Function
Else: ISLeapYear = False ' This is Step 5 from step 3
Exit Function
End If
Else: ISLeapYear = True ' This is Step 4 from Step 2
Exit Function
End If
Else: ISLeapYear = False ' This is Step 5 from Step 1
End If
End Function
Public Function isLeapYear(Optional intYear As Variant) As Boolean
If IsMissing(intYear) Then
intYear = Year(Date)
End If
If intYear Mod 400 = 0 Then
isLeapYear = True
ElseIf intYear Mod 4 = 0 And intYear Mod 100 <> 0 Then
isLeapYear = True
End If
End Function
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
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.
© 2022 - 2024 — McMap. All rights reserved.