How to use Year function in VBA?

In VBA (Visual Basic for Applications), the Year function is used to extract the year part from a given date. It returns an integer representing the year of the specified date.

Here’s how to use the Year function in VBA:

  • Have a date, either hard-coded, from a cell in Excel, or from a variable in your code.
  • Use the Year function with that date as the argument.



Sub GetYearExample()
    Dim exampleDate As Date
    Dim theYear As Integer
    exampleDate = #12/31/2023# ' Assign a specific date (December 31, 2023)
    theYear = Year(exampleDate) ' Extract the year part from the date
    MsgBox "The year is: " & theYear ' Display the year in a message box
End Sub

In this example, GetYearExample is a subroutine that sets exampleDate to December 31, 2023, and then uses the Year function to get the year part from it. The result, which is 2023, is displayed in a message box.

You can also use the Year function with dates stored in Excel cells:


Sub GetYearFromCell()
    Dim cellDate As Date
    Dim theYear As Integer
    cellDate = Range("A1").Value ' Assume that cell A1 contains a date
    theYear = Year(cellDate) ' Get the year part from the cell date

    MsgBox "The year from the cell is: " & theYear
End Sub

When working with dates, ensure that your date variables are properly formatted as dates or that the cells you work with have valid dates, otherwise you may run into errors or unexpected results.

Unlock Your Potential


Basic - Advanced


Access Basic - Advanced

Power BI

Power BI Basic - Advanced

Help us grow the project

Leave a Reply

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