EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 120+ Courses All in One Bundle
  • Login

VBA Month

By Ashwani JaiswalAshwani Jaiswal

Home » VBA » Blog » VBA Date & Time Functions » VBA Month

VBA Month

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:

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

VBA Month Syntax

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.

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

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.

Insert 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

VBA Month Example 1-2

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

Code:

Sub VBA_Month()

Dim DD As Date

End Sub

VBA Month Example 1-3

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

Integer Example 1-4

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

VBA Month Example 1-5

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

MONTH function Example 1-6

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

Message Box Example 1-7

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.

VBA Month Example 1-8

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

VBA Month Example 2-1

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

Code:

Sub VBA_Month2()

Dim MM As Integer

End Sub

Integer Example 2-2

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

VBA Month Example 2-3

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

MsgBox Example 2-4

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.

VBA Month Example 2-5

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

VBA Month Example 3-1

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

MsgBox Example 3-2

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.

VBA Month Example 2-5

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

VBA Month Example 3-4

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

MONTH function Example 4-1

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

VBA Month Example 4-2

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
0 Shares
Share
Tweet
Share
Primary Sidebar
VBA Date & Time Functions
  • VBA Date and Time
    • VBA Month
    • VBA DateSerial
    • VBA Date Format
    • VBA Time
    • VBA TIMER
    • VBA Date
    • VBA Format Number
    • VBA DateAdd
    • VBA DateValue
    • VBA DatePart
    • VBA DateDiff
    • VBA Format Date
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more

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

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

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

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

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

By signing up, you agree to our Terms of Use and Privacy Policy.

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

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

Special Offer - Become a VBA Developer Learn More