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. In this article, we demonstrate the basics and uses of the VBA MONTH function.

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

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

🔼 Using Worksheet Tab

Go to any worksheet, Right-Click on it > 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 it > Select Insert (from the Menu List) > then Choose Module.

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

Example 1: Using VBA MONTH Function to Get 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

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

Read more: How to Use EoMonth in Excel VBA

Example 2: Applying VBA MONTH Function to Get 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

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

Example 3: Utilizing VBA MONTH Function to Get 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

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

Example 4: Getting Current Month Number 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

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

Similar Readings

Example 5: Getting Full Current Month Name Using DATE Function of VBA

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

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

Example 6: Using Cell Reference to Get the Month Number

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

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

Example 7: Get Months for a Range of Dates Using VBA MONTH Function

For this example, let’s say we have a range of dates (shown in the below picture) for which we want the month numbers. And we assign 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

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

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

Download Practice Workbook

Uses of Excel VBA MONTH Function.xlsm

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