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:

Argument Required/Optional Explanation
date Required A date

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.

Year Function in Excel VBA to Show Year in Worksheet

  • 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.

Year Function in Excel VBA to Show Year in Worksheet

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.

Year Function in Excel VBA to Get Year in Message Box

  • 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.

Year Function in Excel VBA to Get Year in Message Box

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.

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

  • 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.

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

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


Similar Readings


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.

Year Function in Excel VBA to Check Leap Year

  • 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.

Year Function in Excel VBA to Check Leap Year

  • 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.

Year Function in Excel VBA to Count Year Occurrences

  • 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.

Year Function in Excel VBA to Count Year Occurrences

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

Year Function in Excel VBA to Count Year Occurrences

  • 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

Mithun

Mithun

Hello! I am Md. Sourov Hossain Mithun. Welcome to my profile. Currently, I am working at Exceldemy as an Excel and VBA Content Developer. Excel is amazing software. Here I will post excel related useful articles. I am a graduate of Bangladesh University of Engineering and Technology. I love to learn new things and work with them. Thank you.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo