vba 你如何在VBA中找到Leapyear？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/128104/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do you find Leapyear in VBA?
提问by Lance Roberts
What is a good implementation of a IsLeapYear function in VBA?
在 VBA 中 IsLeapYear 函数的良好实现是什么？
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).
编辑：我运行 if-then 和 DateSerial 实现，迭代包含在计时器中，DateSerial 平均快了 1-2 毫秒（5 次运行 300 次迭代，1 个平均单元格工作表公式也有效）。
回答by Lance Roberts
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.
我最初从 Chip Pearson 很棒的 Excel 网站获得了这个功能。
回答by seanyboy
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
维基百科更多... http://en.wikipedia.org/wiki/Leap_year
回答by Brent.Longborough
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
回答by Pascal Paradis
I found this funny one on CodeToad:
我在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.
尽管我很确定在函数中使用 IsDate 可能比使用 if、elseifs 慢。
回答by RonnieDickson
As a variation on the Chip Pearson solution, you could also try
作为 Chip Pearson 解决方案的变体，您也可以尝试
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
回答by Bob
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
回答by Dan
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
回答by Harry S
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
而不是复杂的日期函数只使用相当快的整数函数 BASIC 是建立在 GOTO 上我怀疑像下面这样的东西更快
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
回答by chris neilsen
Late answer to address the performance question.
解决性能问题的迟到答案。
TL/DR: the Mathversions are about 5x faster
TL/DR：Math版本快了大约5 倍
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)
- 闰年定义的数学解释
- 利用 Excel 日期/时间函数来检测 2 月 29 日（这些分为两个阵营：将日期构建为字符串的那些，以及那些不构建日期的）
I ran time tests on all posted answers, an discovered the Mathmethods are about 5x fasterthan the Date/Time methods.
我对所有发布的答案进行了时间测试，发现数学方法比日期/时间方法快约5 倍。
I then did some optimization of the methods and came up with (believe it or not Integeris marginally faster than Longin this case, don't know why.)
然后我对方法进行了一些优化并提出了（不管你信不信 Integer比Long这种情况快一点，不知道为什么。）
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 IsLeapYearfor years 100..9999, repeated 1000 times
测试是要得到IsLeapYear100..9999 年，重复 1000 次
Results
结果
- Math version: 640ms
- Date/Time version: 3360ms
- 数学版：640ms
- 日期/时间版本：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
回答by AndrewJD
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.
如果 Leap_Day_Check = 28 则不是闰年，如果是 29 则是。
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.
VBA 知道一年中 3 月 1 日之前的日期，因此我们将其设置为 2 月 28 日或 29 日。
