WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Free Tutorials
  • Certification Courses
  • Excel VBA All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Learn VBA » VBA Month Function

VBA Month Function

Excel VBA Month

VBA Month Function is a inbuilt function used to get month from a date and the output returned by this function is integer ranging from 1 to 12. This function only extracts 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 MONTH function.

VBA Month

How to Use Month Function in VBA?

Below is the syntax of the MONTH function.

VBA Month Syntax

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

You can download this VBA Month Excel Template here – VBA Month Excel Template

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

 Example 1

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.

Code:

Sub Month_Example1()

Dim DDate As Date

End Sub

VBA Month Example 1.1

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

Code:

Sub Month_Example1()

Dim DDate As Date

DDate = "10 Oct 2019"

End Sub

Example 1.2

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

VBA Month Example 1.3

Note: The reason why we have declared the variable as Integer because our month count ends at 12 only. So integer data type can hold this number.

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

Popular Course in this category
Sale
All in One Excel VBA Bundle (35 Courses with Projects)
4.9 (1,353 ratings)
35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
View Course

Code:

Sub Month_Example1()

Dim DDate As Date

Dim MonthNum As Integer

DDate = "10 Oct 2019"

MonthNum = Month(

End Sub

 Example 1.4

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

VBA Month Example 1.5

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

 Example 1.6

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

Output:

VBA Month Example 1.7

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

Example #2

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

 Example 2.0

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

VBA Month Example 2

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

Code:

Sub Month_Example2()

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

End Sub

 Example 2.1

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.

VBA Month Example 2.3.0

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 rows of data like the below one.

 Example 3

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

VBA Month Example 3.1

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.

 Example 3.2

Things to Remember here

  • MONTH is a worksheet function, as well as a 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 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 –

  • VBA Randomize
  • CreateObject Function in VBA
  • VBA DateDiff Function Examples
  • VBA DateAdd
  • VBA ByRef Argument Type Mismatch
9 Shares
Share
Tweet
Share
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download VBA Month Excel Template

New Year Offer - All in One Financial Analyst Bundle (250+ Courses, 40+ Projects) View More