How to Use EoMonth in Excel VBA (5 Examples)

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

expression.EoMonth (Arg1, Arg2)

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:

Suitable Examples of EoMonth Function in Excel VBA

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.

Find the Last Day of Current Month Using EoMonth

  • After that, select last_day
  • Next, click on Run.

Find the Last Day of Current Month Using EoMonth

  • 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.

Find the Last Day of Current Month Using EoMonth

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.

EoMonth to Find the Last Day of the Previous Month in VBA

  • 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.

EoMonth to Find the Last Day of the Previous Month in VBA

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.

Number of Days in a Month Using EoMonth in VBA

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


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.

The First Day of a Month Using EoMonth

  • 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.

The First Day of a Month Using EoMonth

  • Then, select Short Date.

The First Day of a Month Using EoMonth

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.

End Date of a Future Month in VBA

  • 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.

End Date of a Future Month in VBA

  • Then, select Short Date.

End Date of a Future Month in VBA

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!


Further Readings

Shanto

Shanto

Hello! I am Shanto. An Excel & VBA Content Developer. My goal is to provide our readers with great tutorials on various Excel-related problems. I hope our easy but effective tutorials will enrich your knowledge. I have completed my BSc in Computer Science & Engineering from Daffodil International University. Working with data was always my passion. Love to work with data, analyze those, and find patterns. Also, love to research. Always look for challenges to keep me growing.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo