VBA YEAR

What is Excel VBA Year Function?

The VBA Year function is used to extract the year from a given date. It takes a date value as its argument and returns an integer representing the year portion of that date. Consider the following example using the VBA Year format.

Excel VBA Year Function 1

Define a subroutine to show the VBA year of date using the function of the same name. The VBA year format is accepted in the format “dd-mm-yyyy” or “mm-dd-yyyy” in VBA. After running the subroutine, the year is shown in the Immediate tab.

Excel VBA Year Function 1-1
Key Takeaways
  • The Year function is used in VBA to extract the year portion from a given date.
  • Its syntax is: Year(date). The date parameter is a required argument representing the date from which you want to extract the year.
  • It returns an integer representing the year portion of the provided date.
  • The function only considers the year component of the date and disregards the month and day. The year returned is in the range of 100 to 9999.
  • The Year function is useful when you need to perform operations or comparisons based specifically on the year component of a date.

Syntax

The syntax to get the VBA Year format is as shown. The formula is:

Year(date)

Where,

  • date: The variable storing a “Date” value.

How to Use VBA Year Function?

Learn how to use the VBA Year function properly by following the steps below.

Step 1: Select the “Developer” icon in the Excel toolbar.

How to use VBA Year function 1

After selecting the Developer icon, select “Visual Basic” in the far left-hand corner. It opens the VBA window where you can start coding.

How to use VBA Year function 1-1

In the VBA Editor, in the title bar, click the “Insert” button and select the “Module” option.

How to use VBA Year function 1-2

Step 2: Define a sub-procedure to count the number of days passed in a year given a date.

How to use VBA Year function 1-3

Step 3: Declare a date variable and provide a date value.

How to use VBA Year function 1-4

Step 4: Declare the number of days as an integer.

How to use VBA Year function 1-5

The number of days is calculated using the VBA DateDiff function between the first day of the year and the given date defined above. The result is incremented by 1 since the first day will also be subtracted. You need to find which day of the year it is. For that, even the first day “01/01/2023” will be counted in this case.

Step 5: Print the number of days using the Message Box function. Customize it to your preferences to make it look more interesting and provide context.

How to use VBA Year function 1-6

Code:

Sub CountNumberOfDays()

    Dim dt As Date: dt = #3/21/2023#

    Dim dno As Integer

    dno = DateDiff(“d”, CDate(“1/1/” & year(dt)), dt) + 1

    MsgBox “It is the ” & dno & “th day of the year ” & year(dt), vbInformation

End Sub

Step 6: Click “F5” or the “Run” icon on the activity bar to run the VBA sub-procedure.

How to use VBA Year function 1-7

Now that we know how to use the Year function in Excel VBA, let us view some exciting examples.

Examples

See the different ways in which you can use the VBA Year format below.

Example #1

Suppose you’ve been using the same workbook for years. Every time a new year comes, you want to record the new year in your worksheet. It can be done using the VBA Year Month Day function.

VBA YEAR - Example 1

Step 1: Name the subroutine to show the current year in the Worksheet.

VBA YEAR - Example 1-1

Step 2: Get the current using the VBA Now function.

VBA YEAR - Example 1-2

Step 3: Find the VBA Year Now by calling the year function and storing the result in an integer function.

VBA YEAR - Example 1-3

Step 4: Define the worksheet name and the cell location where you want to store the year value. Add some string values to provide context.

VBA YEAR - Example 1-4

Code:

Sub ShowYearInWorkSheet()

    Dim d As Date: d = Now

    Dim Yr As Integer: Yr = year(d)

    Worksheets(“Sheet1”).Range(“B4”).value = “It is the year ” & Yr

End Sub

Step 5: Run the above subroutine and view the current year in the worksheet of your choice.

VBA YEAR - Example 1-5

It is the year 2024.

Example #2

Suppose you want to find the current year and show it in a message box. Follow these simple four steps to print the current year in the Message Box.

Step 1: Initialize a subroutine to find the current year.

VBA YEAR - Example 2

Step 2: Using the VBA Date function, get the current date.

VBA YEAR - Example 2-1

Step 3: Get the current year from the current date using the VBA Year function.

VBA YEAR - Example 2-2

Step 4: Print the above result using the Message Box function.

VBA YEAR - Example 2-3

You can declare icons. Here, the information icon is printed using the keyword “vbInformation.”

After declaring icons, you can also customize the title of the Message Box. Enter whatever you need in the quotes since it is a string variable.

Code:

Sub CurrYear()

    currDate = Date

    currYr = year(currDate)

    MsgBox “The current year is ” & currYr, vbInformation, “What is the current year?”

End Sub

Step 5: Click the green arrow button when you want to find the current year in the Workbook. It will pop up a Message Box once it is run.

VBA YEAR - Example 2-4

Example #3

You need not use the VBA Year function all the time. In fact, if you want to display the last two digits, you can use the VBA Year Format function. Follow these simple steps to do so.

Step 1: Name the subroutine to find the last two digits of the year.

VBA YEAR - Example 3

Step 2: Using the VBA Date function, get the current date.

VBA YEAR - Example 3-1

Step 3: Using the VBA Format function, show only the last two digits by defining “yy” as a parameter in the Format function.

VBA YEAR - Example 3-2

Code:

Sub ShowLastTwoDigits()

    Dim todaysdate As Date

    todaysdate = Date

    MsgBox “The last two digits of the current year are ” & Format(todaysdate, “yy”), vbInformation

End Sub

Step 4: Run the above subroutine. It will trigger a message box.

VBA YEAR - Example 3-3

Example #4

Leap years are a fascination for many! A leap year is characterized as a year having 366 days as opposed to the standard 365 days per year. This phenomenon happens once every 4 years. It is because February has 29 days in leap years.

Step 1: To start with finding the leap year, declare a subroutine to perform this functionality.

VBA YEAR - Example 4

Step 2: Get the current date using the VBA Date function.

VBA YEAR - Example 4-1

Step 3: Using a Boolean variable, check whether the given year is a leap year or not. In this, three conditions are checked.

VBA YEAR - Example 4-2
  • year(currDate) Mod 4 = 0 checks whether the given year is divisible by 4. It is checked in conjunction with two other conditions.
  • year(currDate) Mod 100 <> 0 checks if the current year is not divisible by 100. The other condition is checked using a VBA Or function.
  • year(dt) Mod 400 = 0 checks if the year of the date is divisible by 400.

Either of the above two conditions should be true.

Step 4: Use an If-Else condition in VBA for conditions when the Boolean variable prints either true or false. In this case, the false condition is checked using the If condition.

VBA YEAR - Example 4-3

Print a Message Box intimidating the user that the year isn’t a leap year.

Step 5: Declare an Else condition if the Boolean variable returns true.

VBA YEAR - Example 4-4

Code:

Sub IsLeapYear()

    Dim currDate As Date: currDate = Date

    Dim booIsLeapYear As Boolean

    booIsLeapYear = ((year(currDate) Mod 4 = 0) And (year(currDate) Mod 100 <> 0)) Or (year(dt) Mod 400 = 0)

    If Not booIsLeapYear Then

        MsgBox year(currDate) & ” is not a Leap Year!”, vbExclamation

    Else

        MsgBox year(currDate) & ” is a Leap Year!”, vbInformation

    End If

End Sub

Step 6: Run the above subroutine to check whether the current year is a leap year or not.

VBA YEAR - Example 4-5

Example #5

Suppose you have a table of values; you want to find the number of occurrences of each available year. It can be done using the VBA Year function.

VBA YEAR - Example 5

Step 1: Define a subroutine to count the instances of each year.

VBA YEAR - Example 5-1

Step 2: Create a scripting dictionary object to store the year occurrences.

VBA YEAR - Example 5-2

Step 3: Set the range of the table in a Range value in VBA.

VBA YEAR - Example 5-3

Step 4: Define an iterative variable to run through each cell in the range defined above.

VBA YEAR - Example 5-4

Step 5: Check if all the dates are in a valid format using the VBA IsDate function.

VBA YEAR - Example 5-5

Step 6: Extract the year value from each cell and store it in an integer variable.

VBA YEAR - Example 5-6

Step 7: Check if the year instance already exists in the Scripting dictionary. If it exists, add 1 to the existing instance of that year.

VBA YEAR - Example 5-7

Step 8: End the If statement from Step 5. Then, continue the FOR-loop.

VBA YEAR - Example 5-8

Step 9: If the year instance does not exist in the dictionary, then add a new instance of it in the dictionary.

VBA YEAR - Example 5-9

Step 10: Outside the FOR-loop, declare another iterative variable to print the scripting dictionary.

VBA YEAR - Example 5-10

Step 11: Print the values in the scripting dictionary based on the year in the Immediate tab using Debug.Print.

VBA YEAR - Example 5-11

Code:

Sub CountYearOccurrences()

    Dim yearCounts As Object

    Set yearCounts = CreateObject(“Scripting.Dictionary”)

    Dim dateRange As Range

    Set dateRange = Worksheets(“Sheet2”).Range(“A1:A10”)

    Dim cell As Range

    For Each cell In dateRange

        If IsDate(cell.value) Then

            Dim yearValue As Integer

            yearValue = year(cell.value)

            If yearCounts.Exists(yearValue) Then

                yearCounts(yearValue) = yearCounts(yearValue) + 1

            Else

                yearCounts.Add yearValue, 1

            End If

        End If

    Next cell

    Dim y As Variant

    For Each y In yearCounts.keys

        Debug.Print “Year ” & y & “: ” & yearCounts(y) & ” occurrences”

    Next y

End Sub

Step 6: Run the above subroutine to find the number of instances of each year in your data.

VBA YEAR - Example 5-12

Important Things To Note

  • Ensure that the argument passed to the Year function is a valid date value. Invalid dates can lead to unexpected results or errors.
  • Format date values consistently and appropriately according to the requirements of your application or user interface.
  • The Year function returns an integer representing the year portion of a date.
  • Be aware that date formats and conventions may vary based on the regional settings of the user’s system.
  • When dealing with dates and years, be mindful of leap years, as they can affect the calculation of certain date-related operations.
  • When performing arithmetic operations involving dates, be mindful of potential pitfalls such as date wrapping (e.g., December 31st to January 1st) and the handling of negative values.

Frequently Asked Questions (FAQs)

1) Can the Year function in VBA handle dates with different formats?

Yes, the Year function in VBA can handle dates with different formats. It extracts the year component from the provided date regardless of the format.

2) Can the Year function in VBA handle invalid dates?

No, the Year function in VBA cannot handle invalid dates. It expects valid date values as input; otherwise, it may result in errors.

3) Can the Year function in VBA handle dates before the year 100?

No, the Year function in VBA cannot handle dates before the year 100. It returns years in the range from 100 to 9999.

4) Can I use the Year function in VBA to extract the year from a datetime value with time included?

Yes, you can use the Year function in VBA to extract the year from a datetime value with time included. It will only consider the date portion and ignore the time component.

This has been a Guide to VBA YEAR. Here we explain how to use VBA Year function using syntax along with examples & downloadable excel template. You can learn more from the following articles –

Reader Interactions

Leave a Reply

Your email address will not be published. Required fields are marked *