In Microsoft Excel’s Visual Basic for Applications (VBA), there are a lot of built-in functions that can ease the ways of solving complex problems. If you are working with VBA for a while, you know these functions help you save a lot of time. EoMonth is one of them. In this tutorial, you will learn to use the EoMonth function using VBA in Excel with suitable examples and proper illustrations.
Download Practice Workbook
Download this practice workbook
Overview of EoMonth Function in VBA
⏺ Function Objective
Returns the serial number for the last day of the month that is the implied number of months before or after start_date.
⏺ Syntax
The expression represents a WorksheetFunction object.
⏺ Arguments Explanations
Argument
Required/Optional
Data Type
Explanations
Arg1
Required
Variant
Start_date – a date that denotes the starting date. You should enter the Dates by using the DATE function, or as a consequence of other formulas or functions. For example, use DATE(2008,5,23) for the 23rd day of May 2008. If your dates are in Text from, it will cause an error.
Arg2
Required
Variant
Months – the number of months before or after start_date. A positive value for months generates a forthcoming date; a negative value generates a past date.
⏺ Returns
It returns Double.
Important note to remember:
If you directly enter a date in a cell like this: 1/10/2022, it will show a serial number 44571. Excel stores the date as a serial number. You can change the serial number to date format from the Number Format option in Excel.
5 Suitable Examples of EoMonth Function in Excel VBA
In the following sections, we will provide you with five suitable examples with proper explanations about the EoMonth function in VBA. We recommend you learn and apply all these methods to your worksheet. Surely, it will increase your Excel knowledge.
Have a look at the following dataset:
Here, you can see some dates in a column. Now, we will perform all of our EoMonth function of VBA codes in this. So, stay tuned to learn more.
1. Find the Last Day of Current Month Using EoMonth
Now, you can find the last day of the current month using the EoMonth in VBA. It is pretty simple and easy to perform. When people work with the EoMonth function, this is the basic operation they perform.
📌 Steps
- First, press Alt+F11 on your keyboard to open Visual Basic Application.
- Then, Select Insert > Module.
- Then, type the following code:
Sub last_day()
Dim rng As Range
Set rng = Selection
Dim cell As Range
For Each cell In rng
cell.Offset(0, 1) = Application.WorksheetFunction.EoMonth(cell, 0)
Next cell
End Sub
- Now, save the file.
- Now, select the range of cells B5:B12. Then, press Alt+F8 on your keyboard to open the Macro dialog box.
- After that, select last_day
- Next, click on Run.
- Now, you can see the dates as a serial number. To resolve this, select all the serial numbers. Then, from the Home tab, go to the Number.
- Then, select Short Date.
In the end, you will see the serial numbers are now in date format. Clearly, you can see we have successfully found the last day of these dates using the EoMonth in VBA.
Read more: How to Get the Day of Week Using VBA
2. EoMonth to Find the Last Day of the Previous Month in VBA
Now, using the previous VBA code, you can easily find the last day of the previous month. Just you have to enter a negative value. This negative value represents the previous month. And after that, the EoMonth function will get the last month of the previous month.
📌 Steps
- First, press Alt+F11 on your keyboard to open Visual Basic Application.
- Then, Select Insert > Module.
- Then, type the following code:
Sub previous_month()
Dim rng As Range
Set rng = Selection
Dim cell As Range
For Each cell In rng
cell.Offset(0, 1) = Application.WorksheetFunction.EoMonth(cell, -1)
Next cell
End Sub
- Now, save the file.
- Now, select the range of cells B5:B12. Then, press Alt+F8 on your keyboard to open the Macro dialog box.
- After that, select previous_month.
- Next, click on Run.
- Now, you can see the dates as a serial number. To resolve this, select all the serial numbers. Then, from the Home tab, go to the Number.
- Then, select Short Date.
Finally, you can see we have successfully used the EoMonth in VBA to find the last day of the previous month.
Read more: How to Get the Current Date in VBA
3. Number of Days in a Month Using EoMonth in VBA
You can easily find the number of days using the EoMonth in VBA. Basically, we are combining this function with the Day function of VBA. The day function gives us the day of the current date. Mainly, the EoMonth function will return the last date and the Day function will extract the date as the final day.
📌 Steps
- First, press Alt+F11 on your keyboard to open Visual Basic Application.
- Then, Select Insert > Module.
- Then, type the following code:
Sub number_of_days()
Dim rng As Range
Set rng = Selection
Dim cell As Range
For Each cell In rng
cell.Offset(0, 1) = Day(Application.WorksheetFunction.EoMonth(cell, 0)) & " Days"
Next cell
End Sub
- Now, save the file.
- Now, select the range of cells B5:B12. Then, press Alt+F8 on your keyboard to open the Macro dialog box.
- After that, select number_of_days.
- Next, click on Run.
As you can see, we have successfully found the number of days in a month using the EoMonth in VBA. Now, try this with any date in your worksheet.
Read more: How to Insert Current Date in Excel
Similar Readings
- Use IF Formula with Dates (6 Easy Examples)
- How to Format Date with VBA in Excel (4 Methods)
- Use VBA DateValue Function in Excel (6 Examples)
- How to Use Excel Date Shortcut
4. The First Day of a Month Using EoMonth
In this example, you will learn to find the first day of a month. It is a little bit tricky. We will use a negative number in the argument to extract the last date of the previous month. After that, we will add 1 to this equation to get the next day. That will be our first day of the month. Follow these steps to have a better understanding.
📌 Steps
- First, press Alt+F11 on your keyboard to open Visual Basic Application.
- Then, Select Insert > Module.
- Then, type the following code:
Sub first_day()
Dim rng As Range
Set rng = Selection
Dim cell As Range
For Each cell In rng
cell.Offset(0, 1) = Application.WorksheetFunction.EoMonth(cell, -1) + 1
Next cell
End Sub
- Now, save the file.
- Then select the range of cells B5:B12. Then, press Alt+F8 on your keyboard to open the Macro dialog box.
- After that, select first_day.
- Next, click on Run.
- Now, you can see the dates as a serial number. To resolve this, select all the serial numbers. Then, from the Home tab, go to the Number.
- Then, select Short Date.
As you can see, we have successfully found the first day of a month using the EoMonth in VBA.
Read more: How to Calculate Due Date with Formula in Excel
5. End Date of a Future Month in VBA
If you have read all the previous examples which we highly suggest, you can easily perform this. We will find the last date of a future month using the EoMonth. Here, we want to find the end date of a month which is four months away from our dataset. We will enter 4 in the EoMonth argument.
📌 Steps
- First, press Alt+F11 on your keyboard to open Visual Basic Application.
- Then, Select Insert > Module.
- Then, type the following code:
Sub future_month()
Dim rng As Range
Set rng = Selection
Dim cell As Range
For Each cell In rng
cell.Offset(0, 1) = Application.WorksheetFunction.EoMonth(cell, 4)
Next cell
End Sub
- Now, save the file.
- Now, select the range of cells B5:B12. Then, press Alt+F8 on your keyboard to open the Macro dialog box.
- After that, select future_month.
- Next, click on Run.
- Now, you can see the dates as a serial number. To resolve this, select all the serial numbers. Then, from the Home tab, go to the Number.
- Then, select Short Date.
In the end, you can see we have successfully found the last day of the future month using the EoMonth in VBA.
Read more: How to Use Excel VBA MONTH Function
💬 Things to Remember
✎
If you provide a decimal in the month argument, it will only take the integer portion to start_date.
✎
By default, January 1, 1900, is serial number 1.
✎
If start_date is not a valid date, EoMonth will return the #NUM! Error.
✎
EoMonth will also return the #NUM! error if start_date plus months produces an invalid date.
Conclusion
To conclude, I hope this tutorial has provided you with a piece of useful knowledge about the EoMonth using VBA in Excel. We recommend you learn and apply all these instructions to your dataset. Download the practice workbook and try these yourself. Also, feel free to give feedback in the comment section. Your valuable feedback keeps us motivated to create tutorials like this.
Don’t forget to check our website Exceldemy.com for various Excel-related problems and solutions.
Keep learning new methods and keep growing!