VBA Month

Excel VBA Month

If you are an advanced excel user then you must have used the “MONTH” function with a worksheet and the same function can be used in VBA as well. So, the VBA month function is used to extract only the month number from the supplied date value.

For example, if the date is 28-May-2019 then to extract the month number from this date we can use the VBA MONTH function.

Below is the syntax of the VBA MONTH function.

We just need to provide what is the “Date” from which we are trying to extract the month number.

How to Use Month Function in VBA?

Let’s discuss some examples of the VBA Month function.

VBA Month Example #1

We will see how to write a code to extract the month number from the date. We will take the date as “10^th Oct 2019” to extract the month number.

Step 1: Start the macro procedure.

Code:

Sub Month_Example1()

End Sub

Step 2: Define the variable to hold the date value. Since we are storing the date value our data type should be “Date”. So declare the variable and assign the data type as “Date” to the declared variable.

Code:

Sub Month_Example1()

Dim DDate As Date

End Sub

Step 3: For this variable assign the date value of 10^th Oct 2019.

Code:

Sub Month_Example1()

Dim DDate As Date

DDate = "10 Oct 2019"

End Sub

Step 4: Now assign the month number to declare one more variable as “Integer”.

Code:

Sub Month_Example1()

Dim DDate As Date

Dim MonthNum As Integer

DDate = "10 Oct 2019"

End Sub

Step 5: For this variable, we will open the MONTH function.

Code:

Sub Month_Example1()

Dim DDate As Date

Dim MonthNum As Integer

DDate = "10 Oct 2019"

MonthNum = Month(

End Sub

Step 6: Month function asking the “Date” that needs to be supplied to extract the month number. Since we have already stored the targeted date to the variable “DDate” supply this variable as the input parameter for the month function.

Code:

Sub Month_Example1()

Dim DDate As Date

Dim MonthNum As Integer

DDate = "10 Oct 2019"

MonthNum = Month(DDate)

End Sub

Step 7: Now “Month” function will return the month number from the supplied date to the variable “MonthNum” and finally show the result in a message box in VBA.

Code:

Sub Month_Example1()

Dim DDate As Date

Dim MonthNum As Integer

DDate = "10 Oct 2019"

MonthNum = Month(DDate)

MsgBox MonthNum

End Sub

Run the code and see the month number in the message box.

Output:

So, the month number from the supplied date is 10 i.e. October month.

VBA Month Example #2

Now we will take cell references for coding. Below is the date we have in the worksheet.

So from the cell A2 date value, we need to extract the month number to the cell B2.

Code:

Sub Month_Example2()

Range("B2").Value =

End Sub

Open MONTH function and supply the date as RANGE A2 value.

Code:

Sub Month_Example2()

Range("B2").Value = Month(Range("A2"))

End Sub

The reason why we have supplied Range A2 cell because this time our date is in the cell A2, so the same will be the reference.

Now execute the code and get the month number from the date in cell B2.

Here you go we got the month number in cell B2.

VBA Month Example #3

We have extracted the month for the single-cell date but what if we have multiple rows of data like the below one.

In these cases, we need to loop through the cells and execute the task of extracting the month number from each respective date.

The below code will do the job for us.

Code:

Sub Month_Example3()

     Dim k As Long

     For k = 2 To 12

     Cells(k, 3).Value = Month(Cells(k, 2).Value)

     Next k

End Sub

What this code will do is it will loop through the rows from 2 to 12 and extract the month number from the second column and store the result in the third column.

Things to Remember here

• MONTH is a worksheet function as well as VBA function.
• MONTH requires valid date reference other-wise we will get an error message.
• If the month number is <1 and >12 then it will throw an error message.

Recommended Articles

This has been a guide to VBA Month. Here we discuss how to use month function to extract the month number from this date in excel VBA along with practical examples and a downloadable template. Below you can find some useful excel VBA articles –

• Excel VBA Randomize
• Login Form in VBA
• CreateObject Function in VBA
• VBA DatePart Examples
• VBA DateDiff Function Examples
• VBA DateAdd Examples
• VBA CDATE Function Examples