How do you find Leapyear in VBA?
Solution 1
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.
Solution 2
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
Solution 3
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
Solution 4
Late answer to address the performance question.
TL/DR: the Math versions are about 5x faster
I see two groups of answers here
- Mathematical interpretation of the Leap Year definition
- 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
Solution 5
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.
Related videos on Youtube
Lance Roberts
Control Systems Engineer. Most people want to stick their head in the sand and ignore problems, in an effort to avoid conflict. I refuse to be that passive person. Problems are there to be fixed, which means that first they have to identified. Denial is not just a river in Egypt.
Updated on July 09, 2022Comments
-
Lance Roberts almost 2 years
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).
-
nathaniel over 15 yearsthis one might even be more efficient. i like that it specifically takes the definition of leap year and works it into the answer.
-
Lance Roberts over 15 yearsthe variable isLeap isn't being used
-
rp. over 15 yearsBeing evenly divisible by 4 doesn't a leap year make! 2100 isn't a leap year. The division by 400 part of the test should come before the division by 4.
-
Erik van Brakel over 15 yearscreative solution! I wonder how it performs against the others posted.
-
StingyJack over 15 yearsThat does not take into account all of the leap year rules.
-
Lance Roberts over 15 yearsActually, if you study what they're doing, it always works. They check to see if the february month has 29 days, and that makes it a leapyear. It basically pawns the leapyear rules onto Microsoft. Chip has a lot of good solutions.
-
Oorang almost 15 yearsIf efficiency is the goal you can get rid of isLeapYear = false, as boolean values default to false:)
-
Lance Roberts over 12 yearsdo you mean to use the DAY function instead of the MONTH function?