How to use MonthName function in VBA?

The MonthName function in VBA is used to get the name of a specific month. The function takes two arguments: 1. Month: A number from 1 to 12, corresponding to the months of the year (January to December). 2. Abbreviate (optional): A Boolean value where True indicates you want the abbreviated name of the month (e.g., “Jan” for January) and False indicates you want the full name of the month (e.g., “January”). If omitted, it defaults to False, and you get the full month name. Here’s an example of how to use the MonthName function in VBA:

Sub ShowMonthName()
    Dim monthNumber As Integer
    Dim fullMonthName As String
    Dim abbreviatedMonthName As String

    ' Example month number
    monthNumber = 1

    ' Get the full name of the month
    fullMonthName = MonthName(monthNumber)
    ' Output: January
    MsgBox fullMonthName

    ' Get the abbreviated name of the month
    abbreviatedMonthName = MonthName(monthNumber, True)
    ' Output: Jan
    MsgBox abbreviatedMonthName
End Sub
You can call this sub to display message boxes with the full and abbreviated names of the month corresponding to the number specified in the monthNumber variable. This function can be particularly useful when you have a date and want to extract and display the month’s name. Here is an example:

Sub ShowMonthNameFromDate()
    Dim currentDate As Date
    Dim monthName As String

    ' Example date
    currentDate = DateSerial(2023, 3, 15) ' March 15, 2023

    ' Get the name of the month from the date
    monthName = MonthName(Month(currentDate))
    ' Output: March
    MsgBox "The month name of the date " & Format(currentDate, "mmmm dd, yyyy") & " is " & monthName
End Sub
In this example, DateSerial creates a date, and Month extracts the month number from that date, which is then used by MonthName to get the name of the month.

Unlock Your Potential


Basic - Advanced


Access Basic - Advanced

Power BI

Power BI Basic - Advanced

Help us grow the project

Leave a Reply

Your email address will not be published. Required fields are marked *