VBA is year a leap year


0. Quick guide - test for leap year


In this module:

  1. Leap year function - from first principles
  2. Leap year function - using VBA functions: DateSerial, Month, and Year

1. Leap year


A leap year is:

  1. Any year that is exactly divisible by 4 ✅
    1. except if exactly divisible by 100 ❌
      1. but if exactly divisible by 400 ✅

The extra day is added to the month of February, with 29th February often described as a leap day. Exactly divisible means remainder 0 and can be determined by the VBA Mod operator.


The leap year calendar adjustment is needed because the earth rotates at 365.242375 times per year.


2. VBA code leap year identification


2.1 Using logic components


Code 1 and 2 are variations of the logic depicted in part 1 points 1 to 3. The VBA Mod operator divides two numbers and returns only the remainder.



Code 1: Function xlfIsLeapYear_v1 returns the Boolean value TRUE if Year is a leap year 
Function xlfIsLeapYear_v1(Year As Integer) As Boolean
Dim bLY As Boolean
    '  -- point 1 ---      --- point 2 ----     --- point 3 ----
    If Year Mod 4 = 0 And (Year Mod 100 <> 0 Or Year Mod 400 = 0) Then bLY = True Else bLY = False
    xlfIsLeapYear_v1 = bLY
End Function

Code 2 uses an If...Then construct to achieve the same outcome.



Code 2: Function xlfIsLeapYear_v2 returns the Boolean value TRUE if Year is a leap year. 
Function xlfIsLeapYear_v11(Year As Integer) As Boolean
Dim Tmp As Boolean

    If Year Mod 400 = 0 Then        ' point 3
        Tmp = True
    ElseIf Year Mod 100 = 0 Then    ' point 2
        Tmp = False
    ElseIf Year Mod 4 = 0 Then      ' point 1
        Tmp = True
    Else
        Tmp = False
    End If

    xlfIsLeapYear_v2 = Tmp

End Function

VBA dates run from 1 January 100 to 31 December 9999 with corresponding serial date values -657434 to 2958465


Excel dates run from 1 January 1900 to 31 December 9999 with corresponding serial date values 1 to 2958465. Negative serial dates are not recognised in Excel. It is well known that the serial date sequence is incorrect because Excel includes 1900 as a leap. Thus 29th February 1900 with serial date value 60 is an error (inherited from Lotus 123). The VBA based functions in code 1, 2 and 3 each handle the 1900 date error correctly.


Table 1: A comparison of WS and VBA dates

Date Excel WS VBA
1 January 100 ######## -657434
30 December 1899 ######## 0, stored as 12:00:00 AM
31 December 1899 ######## 1
0 January 1900, WS date format applied to zero 0
1 January 1900 1 2
2 January 1900 2 3
28 February 1900 59 60
29 February 1900 (the 1900 leap year error) 60 61, stored as 1 March 1900
1 March 1900 61 61
31 December 2030 47848 47848

2.2 Using the VBA date functions


This is another variation uses VBA functions DateSerial, Month, and Year.



Code 3: Function xlfIsLeapYear_v3 returns the Boolean value TRUE if Year is a leap year 
Function xlfIsLeapYear_v3(Year As Integer) As Boolean
    xlfIsLeapYear_v3 = Month(DateSerial(Year, 2, 29)) = 2
End Function

Note: the WS version of code 3 would fail in the year 1900. See lines 2 and 3 of the worksheet in figure 1.


xlf leap year WS
Fig 1: Leap year WS formulas - with 1900 date error test data

WS formula line 3 =MONTH(DATE(1900,2,29))=2 returns TRUE ❌


WS formula called from VBA Application.ExecuteExcel4Macro("MONTH(DATE(1900,2,29))=2") returns FALSE