Excel VBA Month

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

You are free to use this image on you website, templates, etc., Please provide us with an attribution linkHow to Provide Attribution?Article Link to be HyperlinkedFor eg:Source: VBA Month Function (wallstreetmojo.com)

How to Use the Month Function in VBA?

Below is the syntax of the MONTH function.

We need to provide the date from which we are trying to extract the month number.

Example #1

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

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 data value, our data type should be “Date.” So declare the variable and assign the data type as “Date” to the declared variable.

Sub Month_Example1()

Dim DDate As Date

End Sub

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

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

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.

Sub Month_Example1()

Dim DDate As Date

Dim MonthNum As Integer

DDate = “10 Oct 2019”

MonthNum = Month(

End Sub

Step 6: The Month function asks for the “Date” that we must supply 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.

Sub Month_Example1()

Dim DDate As Date

Dim MonthNum As Integer

DDate = “10 Oct 2019”

MonthNum = Month(DDate)

End Sub

Step 7: Now, the “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.

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.

Example #2

Now, we will take cell referencesCell ReferencesCell reference in excel is referring the other cells to a cell to use its values or properties. For instance, if we have data in cell A2 and want to use that in cell A1, use =A2 in cell A1, and this will copy the A2 value in A1.read more for coding. Below is the date we have on the worksheet.

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

Sub Month_Example2()

Range(“B2”).Value =

End Sub

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

Sub Month_Example2()

Range(“B2”).Value = Month(Range(“A2”))

End Sub

We have supplied a Range A2 cell because our date is in cell A2 this time, 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.

Example #3

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

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.

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

  • The MONTH function is a worksheet function and a VBA functionVBA FunctionVBA functions serve the primary purpose to carry out specific calculations and to return a value. Therefore, in VBA, we use syntax to specify the parameters and data type while defining the function. Such functions are called user-defined functions.read more.The MONTH function requires a valid date reference. Otherwise, we will get an error message.If the month number is <1 and >12, it will throw an error message.

This article has been a guide to VBA Month. Here, we discuss using the VBA month function to extract the month number from the given date, along with examples and a downloadable Excel template. Below you can find some useful Excel VBA articles: –