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.
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.
Table of Contents
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.
After selecting the Developer icon, select “Visual Basic” in the far left-hand corner. It opens the VBA window where you can start coding.
In the VBA Editor, in the title bar, click the “Insert” button and select the “Module” option.
Step 2: Define a sub-procedure to count the number of days passed in a year given a date.
Step 3: Declare a date variable and provide a date value.
Step 4: Declare the number of days as an integer.
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.
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.
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.
Step 1: Name the subroutine to show the current year in the Worksheet.
Step 2: Get the current using the VBA Now function.
Step 3: Find the VBA Year Now by calling the year function and storing the result in an integer function.
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.
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.
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.
Step 2: Using the VBA Date function, get the current date.
Step 3: Get the current year from the current date using the VBA Year function.
Step 4: Print the above result using the Message Box function.
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.
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.
Step 2: Using the VBA Date function, get the current date.
Step 3: Using the VBA Format function, show only the last two digits by defining “yy” as a parameter in the Format function.
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.
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.
Step 2: Get the current date using the VBA Date function.
Step 3: Using a Boolean variable, check whether the given year is a leap year or not. In this, three conditions are checked.
- 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.
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.
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.
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.
Step 1: Define a subroutine to count the instances of each year.
Step 2: Create a scripting dictionary object to store the year occurrences.
Step 3: Set the range of the table in a Range value in VBA.
Step 4: Define an iterative variable to run through each cell in the range defined above.
Step 5: Check if all the dates are in a valid format using the VBA IsDate function.
Step 6: Extract the year value from each cell and store it in an integer variable.
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.
Step 8: End the If statement from Step 5. Then, continue the FOR-loop.
Step 9: If the year instance does not exist in the dictionary, then add a new instance of it in the dictionary.
Step 10: Outside the FOR-loop, declare another iterative variable to print the scripting dictionary.
Step 11: Print the values in the scripting dictionary based on the year in the Immediate tab using Debug.Print.
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.
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)
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.
No, the Year function in VBA cannot handle invalid dates. It expects valid date values as input; otherwise, it may result in errors.
No, the Year function in VBA cannot handle dates before the year 100. It returns years in the range from 100 to 9999.
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.
Recommended Articles
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 –
Leave a Reply