How to Use Year Function in Excel VBA (5 Suitable Examples)

The Year function in Excel VBA is under the Date and Time category functions. If we use it in a VBA code, it extracts the year from the date which we supplied. Actually, it’s just like the Excel YEAR function in the worksheet. Sometimes we need only the year instead of a long date while working in Excel. And this article will guide you with some easy examples to use the Year function in Excel.

Download Practice Workbook

You can download the free Excel template from here and practice on your own.

Introduction to the Year Function in VBA

Purpose:

To extract the year as a whole number from a date.

Syntax:

Year(Date)

Arguments:

Return Parameter:

Returns a whole number (Integer) representing the year.

5 Examples of Using Year Function in Excel VBA

1. Use Year Function in Excel VBA to Show Year in Worksheet

Suppose there is a date in your worksheet but you don’t’ need the whole date, you need the year only for your task. Then you can easily do it with the Year function in Excel VBA. To show that I have picked a date in Cell C4 and I’ll show the year in Cell C5.

Steps:

• Right-click your mouse on the sheet title.
• Then select View Code from the context menu.

A VBA window will open up.

Or you can press Alt+F11 to open the VBA window directly.

• Now type the following codes-

Sub Year_Num()
Range("C5").Value = Year(Range("C4"))
End Sub

• Later, just press the Run icon to run the codes.

Now you will see that we have got the year.

2. Insert Year Function in Excel VBA to Get Year in Message Box

If you don’t need the year in your worksheet then there is a way to show the year in a pop-up message box using the Year function in Excel VBA.

Steps:

• Like the previous example right-click your mouse on the sheet title.
• Then select View Code from the context menu.

A VBA window will open up.

• Write the codes given below-

Sub Year_Example()
    MsgBox Year(Range("C4")), vbInformation, "The Year is:"
End Sub

• Then just press the Run icon to run the written codes.

And you will get the output in a pop-up message box like the image below.

3. Apply Year Function in Excel VBA to Display Last Two Digits of the Year Using Format

In some cases, we just use the last two digits of a year. Excel VBA allows us to do it too by using the format. We’ll extract the last two digits of the current year and we’ll get the output in a pop-up message box. Let’s see how to do it.

Steps:

• Like the previous examples open the VBA window by selecting the View Code option from the context menu after right-clicking your mouse on the sheet title.

• After that copy the following codes in the VBA window-

Sub Last2Digits()
    Dim sCurrentYear As Date
    xCurrentYear = Date
    MsgBox Format(xCurrentYear, "yy"), vbInformation, "Current Year is:"
End Sub

• Later, just click the Run icon to run the codes.

Then you will get the last two digits of the current year in a pop-up notification.

Similar Readings

• Get the Day of Week Using VBA (4 Suitable Methods)
• How to Convert Date from String Using VBA (7 Ways)
• Use the VBA DateAdd Function in Excel
• How to Sort Dates in Excel by Year (4 Easy Ways)

4. Use Year Function in Excel VBA to Check Leap Year

We know that nearly every four years is a leap year which has 366 days. Excel VBA has a wide range of applications so we can check the year from date whether it is a leap year or not by using the Year function in Excel VBA.

Steps:

• Open the VBA window like the previous examples.

• Then copy the following codes-

Sub LeapYear()
Dim booIsLeapYear As Boolean
Dim dt As Date: dt = Selection
booIsLeapYear = ((Year(dt) Mod 4 = 0) And (Year(dt) Mod 100 <> 0)) Or (Year(dt) Mod 400 = 0)
MsgBox booIsLeapYear
End Sub

• Next, go back to your worksheet and select the cell that contains the date.

• After that just press the Run icon in the VBA window.

If it is a leap year then it will show True in a pop-up message box otherwise will show False. As 2022 is not a leap year that’s why it is showing False.

5. Apply Year Function in Excel VBA to Count Year Occurrences

Suppose you may have too many dates in your worksheet and you want to know the occurrences of a specific date then you can easily do it with the Year function in Excel VBA. To show that I have placed some dates in my dataset and I’ll check for the year 2021 which I have placed in Cell B12.

Steps:

• Open the VBA window by selecting the View Code option from the context menu after right-clicking your mouse on the sheet title.

• Type the following codes in the window-

Sub YearOccurrences()
YearCount = 0
yearRef = InputBox("Enter the Cell Reference of the Year to Count Occurances: ")
yearRef = Range(yearRef).Value
years = InputBox("Enter the Range with the Dates: ")
For j = 1 To Range(years).Rows.Count
    If Year(Range(years).Cells(j, 1).Value) = yearRef Then
        YearCount = YearCount + 1
    End If
Next j
MsgBox YearCount & " occurrences of the year " & yearRef
End Sub

• Later, press the Run icon and a dialog box will appear.

• Now type the cell reference which contains your selected year.
• Then press OK and you will get another dialog box.

• At this moment, you will have to put the date range.
• Finally, just press OK.

Soon after you will get the occurrences in a message box.

Conclusion

I hope all of the methods described above will be well enough to use the Year function in Excel VBA. Feel free to ask any question in the comment section and please give me feedback.

Further Readings

• How to Calculate Due Date with Formula in Excel (7 Ways)
• Use IF Formula with Dates (6 Easy Examples)
• How to Format Date with VBA in Excel (4 Methods)
• Insert Current Date in Excel (3 Ways)