How to Use VBA MONTH Function in Excel (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.

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)

⦽ 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 a Module in Microsoft Visual Basic

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

Using Keyboard Shortcuts

Press Alt + F11.

Using Developer Tab

Go to the Developer Tab and select Visual Basic.

Using Worksheet Tab

Right-click on a sheet name and choose View Code (from the Menu List).

Inserting a Module in Microsoft Visual Basic

After opening the Microsoft Visual Basic window, select a Worksheet.
Right-Click on a sheet.
Select Insert, then choose Module.

Alternatively, select Insert (from the Toolbar), then choose Module.

Example 1 – Using the VBA Month Function to Get the Month in Message a Box (for a Single Variable)

Use the following macro code in a Module.

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

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

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

3 – assigns the date value to the variable.

4 – shows the outcome in a message box.

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

Read more: How to Use EoMonth in Excel VBA

Example 2 – Applying the VBA Month Function to Get the Month in a Message Box (with a Double Variable)

Paste the below code in a Module.

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

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

2 – declares 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 – assigns the values to variables. DM= Date and MonthNum returns the month number of the given date.

4 – puts 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.

Example 3 – Utilizing the VBA Month Function to Get a Month in a Message Box (No Variables)

Use the below code in the Module.

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

1 – initiates the macro procedure declaring the Sub name.

2 – assigns 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.

Example 4 – Getting the Current Month Number Using the VBA Month Function

Paste the following code in a Module.

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

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

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

3 – states sCurrentMonth equaling to the MONTH formula. The NOW function inserts today’s date as a date.

4 – displays the current month in a message box.

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

Similar Readings

Example 5 – Getting the Full Current Month Name Using the Date Function

Use the following code in a Module.

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

1 – starts the macro by setting the Sub name.

2 – declares the variable sCurrentMonth as Date.

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

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

Press F5 to run the code.

Example 6 – Using the Cell Reference to Get the Month Number

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

Use the following macro in a Module.

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

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

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

Hit F5 to run the code.

Example 7 – Get Months for a Range of Dates Using the VBA Month Function

We have a range of dates (shown in the below picture) for which we want the month numbers. We assigned row numbers as Long Integer (i.e., M).

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

1 – begins the macro by setting the Sub name.

2 – assigns the variable M to the Long Integer.

3 – fixes the variable M value (i.e., row number) from 5 to 12. 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 – repeats steps 1 to 3 as loops until it fetches all months.

Hit F5 to run the code.

⧭ Things to Keep in Mind

The MONTH function exists in both Excel Worksheet and VBA Macros.
The date reference in the MONTH function should be in any valid Date Format.
In case of month values < 1 or >12, MONTH returns an error message.
While inputting direct date entries in Macros, use quotes on both sides.
To preserve the macro code, save the file in the Macro-Enabled Excel Workbook format.

Download the Practice Workbook

Uses of Excel VBA MONTH Function.xlsm