VBA Year Function

VBA Year function is used to extract the year component from a given date. It returns an integer value representing the year in a four digit format. This function is commonly used to perform calculations involving dates and to make formatting changes in reports or spreadsheets.

VBA Year Function – Purpose, Syntax and Arguments

Purpose:

This function is particularly useful when working with data that contains dates, as it allows for efficient data analysis and manipulation. In this blog post, we will dive deeper into the Year function, exploring its usage and providing examples of how it can be implemented in VBA code.

The Year function is a built-in function in VBA and has a simple syntax:

Syntax:

Year(date)

Arguments:

• date: A required argument that specifies the date from which the year component needs to be extracted. This argument can be entered directly as a date or as a reference to a cell containing a date value. The date can be in any valid date format, including text dates.

Example:

Suppose we have a cell with the date 10/15/2021. To extract the year component, we can use the following VBA code:

Dim myDate As Date 
myDate = Range("A1").Value 
Dim myYear As Integer 
myYear = Year(myDate) 
Range("B1").Value = myYear 

The result in cell B1 will be 2021, which is the year component extracted from the date in cell A1.

Remarks:

• The VBA Year function is similar to the Excel YEAR function. The only difference is that the VBA function is used in VBA code, while the Excel function is used in cell formulas.
• This function follows the standard calendar, meaning it returns the year component as a four digit number, even for dates before the year 1900.
• If the date argument is not a valid date, the function will return the runtime error ’13’: Type mismatch.

Important Notes:

• The VBA Year function can only extract the year component from a date value, not a time value. If the date argument contains a time component, it will be ignored in the calculation.
• The year component is returned as an integer value, meaning it cannot have decimals or fractions.
• If the date argument is a serial number, the function will return the year component of that serial number, which may not be the expected result.

The VBA Year function is a useful tool for working with dates in VBA code. It allows for the extraction of the year component, which can then be used for calculations or formatting changes. However, it is important to keep in mind the limitations and potential errors of this function, such as when the date argument includes a time component. With an understanding of its purpose, syntax, arguments, and usage tips, the VBA Year function can be a valuable addition to any VBA programmer’s toolkit.

Understanding VBA Year Function with Examples

Let’s look at some examples to better understand the usage of the Year function in VBA.

Example 1: Basic Usage of Year Function

In this first example, we will use the Year function to extract the year from a specific date and display the result in a message box.

Sub YearFunction()
    Dim myDate As Date
    
    myDate = "5/17/2021" 'assigning a specific date to the variable
    MsgBox "The year from the given date is " & Year(myDate) 'displays the result in a message box
End Sub

1. First, we declare a variable named ‘myDate’ as Date data type, which will hold the given date.
2. Next, we assign a specific date to the ‘myDate’ variable using the format “mm/dd/yyyy”.
3. Then, we use the Year function to extract the year from the given date, which is 2021 in this case.
4. Finally, we use the ‘MsgBox’ function to display the result in a message box, which says “The year from the given date is 2021”.

Example 2: Using Year Function with Variables

In the previous example, we assigned a specific date directly to the ‘myDate’ variable. But what if we want to use a date variable in our code instead? Let’s see an example of how we can use the Year function with variables.

Sub YearFunction()
    Dim myDate As Date
    Dim yearResult As Integer
    
    myDate = Range("A1").Value 'assigning the value from cell A1 to the variable
    yearResult = Year(myDate) 'extracting the year using the Year function
    
    MsgBox "The year from the given date is " & yearResult 'displays the result in a message box
End Sub

1. First, we declare two variables – ‘myDate’ as Date data type and ‘yearResult’ as Integer data type.
2. Next, we assign the value from cell A1 (which is a date) to the ‘myDate’ variable using the Range property.
3. Then, we use the Year function to extract the year from the given date and store it in the ‘yearResult’ variable.
4. Finally, we display the result in a message box, which says “The year from the given date is 2021”.

Example 3: Using Year Function with Date Range

Sometimes, we may need to extract the year from a range of dates rather than a single date. In such cases, we can use the ‘For Each’ loop to iterate through each date in the range and apply the Year function to extract the year. Let’s see an example of how it can be done.

Sub YearFunction()
    Dim myDateRange As Range
    Dim cell As Range
    Dim yearResult As Integer
    
    Set myDateRange = Range("A1:A10") 'assigning the date range to the variable
    
    For Each cell In myDateRange
        yearResult = Year(cell.Value) 'extracting the year from each date in the range
        Debug.Print yearResult 'printing the result in the Immediate window
    Next cell
End Sub

1. First, we declare three variables – ‘myDateRange’ as Range data type, ‘cell’ as Range data type, and ‘yearResult’ as Integer data type.
2. Next, we assign the date range (A1:A10) to the ‘myDateRange’ variable using the Set statement.
3. Then, we use the ‘For Each’ loop to iterate through each cell in the ‘myDateRange’ variable.
4. Inside the loop, we use the Year function to extract the year from each date and store it in the ‘yearResult’ variable.
5. Finally, we use the ‘Debug.Print’ statement to print the result in the Immediate window for each date in the range.

Example 4: Dealing with Incorrect Date Formats

The Year function works well with date values in the correct format. But what if the date is not in the correct format? In such cases, the Year function might return an unexpected result or generate an error. Let’s see how we can handle incorrect date formats using error handling.

Sub YearFunction()
    Dim myDate As Date
    
    On Error GoTo ErrorHandler 'specifying the error handler
    
    myDate = "May 17, 2021" 'assigning an incorrect date to the variable
    MsgBox "The year from the given date is " & Year(myDate) 'displays the result in a message box
    
ExitSub: 'exit the subroutine if no error occurs
    Exit Sub
ErrorHandler: 'handle the error by displaying a message
    MsgBox "Invalid date format! Please enter a valid date."
    Resume ExitSub 'resume execution at the ExitSub label
End Sub

1. First, we use the ‘On Error’ statement to specify the error handler that will handle any errors that occur in our code.
2. Next, we assign an incorrect date format to the ‘myDate’ variable (May 17, 2021 is not a valid date format for VBA).
3. Then, we use the ‘MsgBox’ function to display the result in a message box. In this case, the result will be “The year from the given date is 1900” since VBA recognizes ‘May’ as the month and ’17, 2021′ as the year.
4. If no error occurs, the code will proceed to the ‘ExitSub’ label and exit the subroutine.
5. If an error occurs, the code will jump to the ‘ErrorHandler’ label and display a message stating that the date format is invalid.

Conclusion:

In this blog post, we have explored the Year function in VBA, which is used to extract the year from a given date. We have looked at various examples that demonstrate the usage of this function in different scenarios, such as extracting the year from a specific date, using variables, and dealing with incorrect date formats. By understanding and implementing the Year function in our VBA code, we can efficiently work with date data and automate tasks that involve manipulating dates.