VBA Month Function

Updated January 3, 2023

Excel VBA Month

Month function is available in both VBA and MS Excel. VBA Month function an inbuilt function which is used to find the sequence of the month from the selected date. As it is very easy to apply Month function in Excel, is so easy too to apply that in VBA as well. If we check the syntax of Month function in VBA, we will see it only requires the Date from which it returns the sequence of the month falling in January (1) to December (12). Below we can find the syntax of Month function.

Syntax of VBA Month Function:

Where, Date = any format of date which actually exists can be used. There is no constraint on the format of date we put.

How to Use Month Function in Excel VBA?

We will learn how to use the Month Function in Excel by using the VBA Code.

Example #1

In this example, we will see how to use the MONTH function by declaring the variable way. For this, follow the below steps:

Step 1: Insert a new module inside Visual Basic Editor (VBE). Click on Insert tab > select Module.

Step 2: Write the sub procedure of VBA Month or we can choose any other name to define it.

Code:

Sub VBA_Month()

End Sub

Step 3: Now define a variable using DIM for data type Date.

Code:

Sub VBA_Month()

Dim DD As Date

End Sub

Step 4: Again define another variable using DIM for data type Integer. This is required to hold the month sequence which we will be getting from the input date.

Code:

Sub VBA_Month()

Dim DD As Date
Dim MM As Integer

End Sub

Step 5: Now input a date in any format we want. Here, we will feed the date in text-number combination as shown below in inverted quotes.

Code:

Sub VBA_Month()

Dim DD As Date
Dim MM As Integer
DD = "2 Sep 1990"

End Sub

Step 6: Now use the defined variable for Month which is MM as insert the MONTH function with the date variable as shown below. By this, we will be calling the value stored in DD as Date.

Code:

Sub VBA_Month()

Dim DD As Date
Dim MM As Integer
DD = "2 Sep 1990"
MM = Month(DD)

End Sub

Step 7: To print the value stored in Month function, we will use message box tool.

Code:

Sub VBA_Month()

Dim DD As Date
Dim MM As Integer
DD = "2 Sep 1990"
MM = Month(DD)
MsgBox MM

End Sub

Step 8: Now we will compile the code by pressing F8 function to identify the error, if there is any. And run the code by pressing Play button located below the menu bar.

We will see, the MONTH function has returned the sequence of month as 9 which is allotted to September.

Example #2

There is another simpler way to find the month sequence using MONTH function in VBA. For this, follow the below steps:

Step 1: We will work in the same module, Write the sub procedure for VBA Month as.

Code:

Sub VBA_Month2()

End Sub

Step 2: Define the same type of variable for carrying month value as Integer.

Code:

Sub VBA_Month2()

Dim MM As Integer

End Sub

Step 3: Now put the any date value in MONTH function against variable MM.

Code:

Sub VBA_Month2()

Dim MM As Integer
MM = Month("09/02/1990")

End Sub

Step 4: To print the value stored in Month function, we will use the message box tool.

Code:

Sub VBA_Month2()

Dim MM As Integer
MM = Month("09/02/1990")
MsgBox MM

End Sub

Step 5: Now we will run this code. We will again get the month sequence as 2 because the date format is in MMDDYYYY format.

We check test different dates here to confirm that format of the date is correct or not.

Example #3

There is another simpler way to apply VBA Month. For this, follow the below steps:

Step 1: We will work in the same module, Write the sub procedure for VBA Month as. Now directly open the MsgBox and there only we will use MONTH function along with date.

Code:

Sub VBA_Month3()

End Sub

Step 2: Now use the MONTH function followed by the date from which we will want to extract the month numbers, quote that in inverted commas.

Code:

Sub VBA_Month3()

MsgBox (Month("1990-02-09"))

End Sub

Step 3: Now again run the code. We will see, the MONTH function has return the month sequence as 2, even if we have entered the Date in a different format.

Step 4: What if we enter the time along with date in Month function? Let try to feed a time in any format and see what comes up. As shown below, we have entered the time in HHMMSS AM/PM format.

Code:

Sub VBA_Month4()

MsgBox (Month("1990-02-09 15:16:30 PM"))

End Sub

Now if we run this code, month function again return the same sequence as 2 which stands for FEBRUARY.

Example #4

There is also, another and one of the simplest way to apply VBA Month function and get the month sequence as per current date.

Step 1:  For this again we will be using Msgbox along with MONTH function and in that choose NOW function which is used to get the today’s date and current time.

Code:

Sub VBA_Month4()

MsgBox (Month(Now))

End Sub

Step 2: Run this code by hitting F5 or the Run button. we will get the month sequence as 4 which stands for April.

Pros of VBA Month

• VBA Month function is one of the easiest function to apply.
• The syntax of the function requires only Date and that to in any possible format.
• We can use Date along with Time stamp as well.

Things to Remember

• We can enter the date in any format.
• Entered can also be in Text format.
• Always quote the entered date in inverted commas.
• VBA Month is not limited to above-shown examples
• Always save such excel file which has VBA Code in Macro Enable Excel format, so that code will be persevered.

Recommended Articles

This is a guide to the VBA Month. Here we discuss how to use the Month Function to get Month Number from date in Excel VBA along with practical examples and downloadable excel template. You can also go through our other suggested articles –

1. VBA LBound
2. Excel Add Months to Dates
3. Excel Month Function
4. VBA IsError