How to Get the Current Year or Month with Excel VBA
Question details
The user needs to use VBA macros to retrieve and insert the current year and month into an Excel cell without relying on standard worksheet formulas.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Automating data entry, stamping reports, or dynamically generating dates where the current year or month needs to be populated via VBA code.
- Observed behavior
- The goal is to store the date components directly as numerical values, text strings, or custom-formatted dates using VBA scripts.
Ensure that the Developer tab is enabled in your Excel ribbon and that macros are allowed in your workbook's Trust Center settings before writing or running VBA code.
Extract and Store Year or Month as Static Values
Use VBA's built-in Date, Year, and Format functions to insert static text or numbers representing the current time directly into cells.
This method calculates the current date components within the VBA engine and writes the static outcome into the selected cell. Since it does not rely on active worksheet formulas like =TODAY(), the value will not change when the workbook is reopened on a different day.
Press Alt + F11 on your keyboard to open the Visual Basic for Applications (VBA) editor.
Click 'Insert' from the top menu and select 'Module' to create a blank script window.
To get just the current year as a numerical value, type the following code: ActiveCell.Value = Year(Date)
To extract both the month and year as a text string, use the Format function: ActiveCell.Value = Format(Date, "mm/yyyy")
Close the editor and press Alt + F8 to run your newly created macro on the currently selected cell.
Change Cell NumberFormat to Display Year or Month
Keep the underlying full date value in the cell but change how it visually appears using the NumberFormat property.
Automate Dates with VBA in WPS Spreadsheet
WPS Office fully supports VBA and macros, allowing you to run your Excel date automation scripts seamlessly. Write, edit, and execute VBA code to manage date formats without compatibility issues.
- 1. Open WPS Spreadsheet: Launch WPS Office and open a new or existing Spreadsheet document.
- 2. Access the Developer Tab: Navigate to the 'Developer' tab on the top ribbon to access all macro and VBA tools.
- 3. Open the VBA Editor: Click the 'Visual Basic' icon or press Alt + F11 to launch the integrated code editor.
- 4. Input the Date Code: Insert a new module and paste your date extraction code, such as ActiveCell.Value = Year(Date).
- 5. Execute the Macro: Run the macro to instantly insert the formatted current year or month into your active spreadsheet cell.
Frequently Asked Questions
Can I extract the current month as a standalone number in VBA?
Yes. You can use the built-in Month function in VBA by typing ActiveCell.Value = Month(Date), which will return a numerical value between 1 and 12 representing the current month.
Why does my VBA formatted date appear as a full date in the formula bar?
If you used the ActiveCell.NumberFormat method, VBA only changes how the date visually displays within the worksheet cell grid. The formula bar will always show the complete underlying date value because the fundamental data was not altered.
How do I get the previous month or year using VBA?
You can use the DateAdd function in VBA to subtract time intervals. For example, DateAdd("m", -1, Date) will calculate the exact date for one month ago. You can then wrap this in a Format or Year function to get the specific component you need.
