How to Use Excel VBA MONTH Function (7 Suitable Examples)

The MONTH function falls under the Date or Time category. The MONTH function works both in worksheet and VBA Macros. It takes only one argument. In this article, we demonstrate the basics and uses of the VBA MONTH function.

Overview of Month function-VBA MONTH

In the above screenshot, we demonstrate how a Macro fetches months from dates.


Download Excel Workbook


Excel VBA MONTH Function: Syntax and Arguments

⦽ Function Objective:

 The MONTH function fetches the month (an Integer from 1 to 12) of a given date.

⦽ Syntax:

MONTH(Date)

MONTH syntax

⦽ Arguments Explanation:

Argument Required/Optional Explanation
Date Required It can be in any variant, numeric expression, string expression or any combination

⦽ Return Parameter:

An Integer value between 1 to 12.

⧬ Note:

The MONTH function returns the month number irrespective of format. You can use any type of valid date format while assigning your data, you’ll get exactly the same month as it is.

⦽ Applies To:

Microsoft Excel version 2003, Excel MAC version 2011, and onwards.


⧭ Opening and Inserting Module in Microsoft Visual Basic

Before proceeding to demonstrate any examples, it’s necessary to know the ways to open and insert a Module in  Microsoft Visual Basic in Excel.

There are mainly three ways to open Microsoft Visual Basic Window:

🔼 Using Keyboard Shortcuts

 Press ALT+F11 altogether to open Microsoft Visual Basic window.

🔼 Using Developer Tab

 In an Excel worksheet, Go to Developer Tab > Select Visual Basic. The Microsoft Visual Basic window appears.

developer tab-VBA MONTH

🔼 Using Worksheet Tab

Go to any worksheet, Right-Click on it > Choose View Code (from the Menu List).

worksheet tab


Inserting a Module in Microsoft Visual Basic

After opening the Microsoft Visual Basic window, Select a Worksheet > Right-Click on it > Select Insert (from the Menu List) > then Choose Module.

inserting module-VBA MONTH

You can also do it by Selecting Insert (from the Toolbar) > then Choosing Module.

Module insertion


7 Suitable Examples to Use Excel VBA MONTH Function

Example 1: VBA MONTH Gets Month in Message Box (Single Variable)

From the syntax, we know the MONTH function takes a date as an argument and returns the month of the given date. In this case, we assign a random date in macro codes and bring out the month number in the message box.

Paste the following macro code in a Module of Microsoft Visual Basic window

Sub Month_InMessageBoxSV()
Dim SM As Integer
SM = Month("1/12/2022")
MsgBox SM
End Sub

single variable -VBA MONTH

in the code,

1 – start the macro procedure by declaring the Sub name. You can assign any name to the code.

2 – define the variable SM as Integer. As we assign it to the resultant value of the MONTH function, it must be an integer.

3 – Assign the date value to the variable.

4 – show the outcome in a message box.

Press F5 to run the Macro and it brings out a message box depicting the month name.

Result


Example 2: VBA MONTH Gets Month in Message Box (Double Variable)

Similar to a single variable code, you can write a double variable code for getting the month in the message box.

Paste the below code in a Module of Microsoft Visual Basic window.

Sub Month_InMessageBoxDV()
Dim DM As Date
Dim MonthNum As Integer
DM = "12 January 2022"
MonthNum = Month(DM)
MsgBox MonthNum
End Sub

double variable-VBA MONTH

inside the code,

1 – start the macro procedure stating the Sub name. You can assign any name to the code.

2 – declare the variable DM as Date and MonthNum as Integer. As we assign it to the resultant value of the MONTH function, the MonthNum variable must be an integer.

3 – Assign the values to variables. DM= Date and MonthNum returns the month number of the given date.

4 – fetch the outcome in a message box.

Hit F5 to run the Macro and it displays the result in a message box as shown in the picture below.

Result


 Example 3: VBA MONTH Gets Month in Message Box (No Variable)

You can utilize the VBA MONTH function without declaring any variable. Let’s see the procedure for that.

Use the below code in any Module in Microsoft Visual Basic window.

Sub Month_InMessageBoxNV()
MsgBox (Month("2022-1-12"))
End Sub

No variable

The code’s sections,

1 – initiate the macro procedure declaring the Sub name. You can name anything to the code.

2 – assign a date directly to the message box. The MONTH function bears the date and results in an integer in the message box.

Press F5 to run the Macro and then the month number appears in a message box as shown in the image below.

Result


Example 4: Getting Current Month Using VBA MONTH Function

In this section, we’ll get the current month using the VBA MONTH function. Let’s see the code for that.

Paste the following code in a Module of Microsoft Visual Basic window.  

Sub Month_Current()
Dim sCurrentMonth As Integer
sCurrentMonth = Month(Now)
MsgBox sCurrentMonth, vbInformation, "Current Month"
End Sub

current month-VBA MONTH

In the above screenshot, the code’s sections,

1 – begin the macro code stating the Sub name. You can assign any name to the code.

2 – define the variable sCurrentMonth as Integer. Because we assign sCurrentMonth to the resultant value of MONTH function.

3 – state sCurrentMonth equals to the MONTH formula. Here’s the NOW function inserting today’s date as a date.

4 – display the current month in a message box.

Hit F5 to run the Macro and then the month number appears in a message box as shown in the image below.

Result


Example 5: Getting Full Current Month Name Using DATE Function

Similar to the previous method, we can use the DATE function to display the full name of the month of any date.

Type the following code in a Module of Microsoft Visual Basic window.  

Sub Month_CurrentFN()
Dim sCurrentMonth As Date
sCurrentMonth = Date
MsgBox Format(sCurrentMonth, "MMMM"), vbInformation, "Current Month"
End Sub

current month with full name

From the above image, the code’s sections,

1 – start the macro by setting the Sub name.

2 – declare the variable sCurrentMonth as Date.

3 – set sCurrentMonth equals to the DATE formula. The DATE function results in today’s date.

4 – display the current month with full name in a message box.

Press F5 to run the code, after that the current month appears in a message box as shown in the following picture.

Result


Example 6: Use Cell Reference to Get the Month

Now, we have a date in the worksheet (i.e., C3) cell and we want to return the month of the date in a certain cell (i.e., C4). We can use the VBA MONTH function to do the job.

➤ Write the following macro in a Module of Microsoft Visual Basic window.  

Sub Month_InCell()
Range("C4").Value = Month(Range("C3"))
End Sub

month in a cell -VBA MONTH

From the above image, the code’s sections,

1 – begin the macro procedure by defining the Sub name.

2 – declare the month value is in cell C3 and the resultant value will be in cell C4. MONTH function takes the date from cell C3 then returns the month number in cell C4.

HIT F5 to run the code, afterward, the month number of cell C3 appears in cell C4 similar to the image below.

Result


Example 7: Get Months for a Range of Dates

For this example, let’s say we have a range of dates (shown in the below picture) of which we want the month numbers. And we assign row numbers as Long Integer (i.e., M).

months in range-VBA MONTH

Paste the following code in a Module.  

Sub Month_InRange()
Dim M As Long
For M = 5 To 12
Cells(M,4).Value = Month(Cells(M,3).Value)
Next M
End Sub

months in range

The code is marked in parts,

1 – begin the macro by setting the Sub name.

2 – Assign the variable M to the Long Integer.

3 – fix variable M value (i.e., row number) from 5 to 12. And the dates are in column 3 and the month will be in column 4. Cells. Value gets the month number for every cell entry.

4 – repeat steps 1 to 3 as loops until it fetches 5 to 12-row date’s months.

Hit F5 to run the code. Then back to the worksheet, you’ll see all the months appear in cells.

Result


⧭ Things to Keep in Mind

🔄 The MONTH function works in both Excel Worksheet and VBA Macros.

🔄 Date reference in the MONTH function should be in any valid Date Format otherwise, it results in an error message.

🔄 In case of month value < 1 or >12, MONTH returns an error message. We can enter the date in any format.

🔄 While inputting direct date entries in Macros always use inverted commas on both sides.

🔄 To preserve the Macro Codes, always save the file in Macro Enable Excel format.

🔄 MONTH function has numerous uses, we just demonstrate some typical ones.


Conclusion

I hope the above-described uses of the VBA MONTH function intrigue you to use the function more efficiently. If you have further queries or feedback, please let me know in the comment section. You can check out my other articles on the Exceldemy website

Maruf Islam

My self, Maruf Islam, an engineer and Excel & VBA Content developer on Exceldemy. I enjoy solving problems, finding workable solutions, and most of the part I really like to take on challenges. On Exceldemy I write articles discussing various way outs of Microsoft Excel's stuck ons.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo