Leap Year Test - VBA

Thanks:  0
Likes:  0

# Thread: Leap Year Test - VBA

1. ## Leap Year Test - VBA

Want to test for a leap year in VBA. How do I check if the result of dividing the year by 4 is an whole number?

2. ## Re: Leap Year Test - VBA

Actually it's pretty easy...just use the MOD operator to determine if there is a remainder from the division.

Sub TestLeapYear()
If (Year(Date) Mod 4) Then
MsgBox "Not a Leap Year!"
Else
MsgBox "Leap Year!"
End If
End Sub

3. ## Re: Leap Year Test - VBA

I knew that

Must be christmas dinner still slowing me down

cheers
ziggy12

4. ## Re: Leap Year Test - VBA

Hi Tommy and Ziggy:

The century years, of course must be divisible by 400 to qualify as a Leap Year -- with the exception of 1900, which is incorrectly assumed in EXCEL to be a Leap Year.

5. ## Re: Leap Year Test - VBA

That's only years ending in 00 e.g. 2000. for all other years if the result of dividing by 4 is an integer then it's a leap year.

cheers ziggy12

6. ## Re: Leap Year Test - VBA

With minimal testing:

Code:
```Function isLeapYear(ByVal YY As Long) As Boolean
isLeapYear = IIf(YY Mod 100 = 0, YY Mod 400 = 0, YY Mod 4 = 0)
End Function```
This is usable either in VBA or as an XL function.

Originally Posted by Ziggy12
Want to test for a leap year in VBA. How do I check if the result of dividing the year by 4 is an whole number?

7. ## Re: Leap Year Test - VBA

Public Function IsLeapYear(Y As Integer)
IsLeapYear = Month(DateSerial(Y, 2, 29)) = 2
End Function

Works too but... Both code replies with true or not true when the input is the year only (no dd and mm). In my case the input is day, month ánd year what results in wrong response.

Example: Input of cell A1 is 12-12-2004. However formatted to "yyyy" Cell A2 (formula =IsleapYear) shows "not true" and not "true".

Is there a way to get this fixed?

8. ## Re: Leap Year Test - VBA

Originally Posted by Wil Moosa
Public Function IsLeapYear(Y As Integer)
IsLeapYear = Month(DateSerial(Y, 2, 29)) = 2
End Function

Works too but... Both code replies with true or not true when the input is the year only (no dd and mm). In my case the input is day, month ánd year what results in wrong response.

Example: Input of cell A1 is 12-12-2004. However formatted to "yyyy" Cell A2 (formula =IsleapYear) shows "not true" and not "true".

Is there a way to get this fixed?
Hi Will:

If I have understood you correctly, Tushar's function as posted in this thread can be modified to use date rather than year as in the following ...
Code:
```Function isyLeapYear(ByVal Date1 As Date) As Boolean
YY = Year(Date1)
isyLeapYear = IIf(YY Mod 100 = 0, YY Mod 400 = 0, YY Mod 4 = 0)
End Function```
Is this what you are looking for?

9. ## Re: Leap Year Test - VBA

Yogi thanks, it works fine...

10. ## Re: Leap Year Test - VBA

Simple test for leap year - use the built-in vba function IsDate:

IsDate("2/29/" & TestYear)

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•