`MonthName` Function

Returns a String indicating the specified month.

Syntax

MonthName(month, [abbreviate])

Parameters

month (Required) - Numeric designation of the month. For example, January is 1, February is 2, and so on.
abbreviate (Optional) - Boolean value that indicates if the month name is to be abbreviated. If omitted, the default is False, which means the month name is not abbreviated.

Return Value

Returns a String representing the month name: - If abbreviate is False or omitted: Full month name (e.g., "January", "February") - If abbreviate is True: Abbreviated month name (e.g., "Jan", "Feb")

Remarks

The MonthName function returns the localized name of the month based on the system's regional settings. It is commonly used for displaying month names in user interfaces, reports, and formatted output.

Key Characteristics:

Returns localized month names based on system regional settings
Month parameter must be between 1 and 12
Error 5 (Invalid procedure call) if month is less than 1 or greater than 12
Abbreviate parameter is optional; defaults to False (full name)
Abbreviated names are typically 3 characters but may vary by locale
Much simpler than maintaining arrays of month names
Can be combined with Month() function for date formatting
Respects system locale for internationalization

Common Use Cases:

Display month names in user interfaces
Format dates for reports and output
Create drop-down lists of months
Generate calendar displays
Internationalized date formatting
Chart and graph labels
File naming with month names
Data export with readable month names

Typical Uses

Display Month Names - Convert month numbers to readable names
Report Headers - Format report titles with month names
Calendar Controls - Populate month selectors
Data Export - Include readable month names in exports
User Interface - Display current or selected month
Chart Labels - Label chart axes with month names
File Naming - Create month-based file names
Internationalization - Automatic locale-based month names

Basic Examples

' Example 1: Get full month name
Dim monthName As String
monthName = MonthName(3)
' Returns "March"

' Example 2: Get abbreviated month name
Dim shortMonth As String
shortMonth = MonthName(11, True)
' Returns "Nov"

' Example 3: Display current month
Dim currentMonthName As String
currentMonthName = MonthName(Month(Date))
MsgBox "Current month is " & currentMonthName

' Example 4: Format date with month name
Dim formattedDate As String
Dim d As Date
d = #5/15/2025#
formattedDate = MonthName(Month(d)) & " " & Day(d) & ", " & Year(d)
' Returns "May 15, 2025"

Common Patterns

' Pattern 1: Safe month name with validation
Function SafeMonthName(monthNumber As Integer, _
Optional abbreviate As Boolean = False) As String
If monthNumber < 1 Or monthNumber > 12 Then
SafeMonthName = ""
Else
SafeMonthName = MonthName(monthNumber, abbreviate)
End If
End Function

' Pattern 2: Populate month combo box
Sub PopulateMonthCombo(cbo As ComboBox, Optional abbreviate As Boolean = False)
Dim i As Integer
cbo.Clear
For i = 1 To 12
cbo.AddItem MonthName(i, abbreviate)
Next i
End Sub

' Pattern 3: Get month name from date
Function GetMonthNameFromDate(dateValue As Date, _
Optional abbreviate As Boolean = False) As String
GetMonthNameFromDate = MonthName(Month(dateValue), abbreviate)
End Function

' Pattern 4: Format date range with month names
Function FormatDateRange(startDate As Date, endDate As Date) As String
Dim startMonth As String
Dim endMonth As String

startMonth = MonthName(Month(startDate), True)
endMonth = MonthName(Month(endDate), True)

FormatDateRange = startMonth & " " & Day(startDate) & " - " & _
endMonth & " " & Day(endDate) & ", " & Year(endDate)
End Function

' Pattern 5: Create month-based filename
Function GetMonthlyFileName(fileDate As Date, fileType As String) As String
Dim monthAbbrev As String
monthAbbrev = MonthName(Month(fileDate), True)
GetMonthlyFileName = fileType & "_" & Year(fileDate) & "_" & monthAbbrev & ".dat"
End Function

' Pattern 6: Build month selection array
Function GetMonthArray(fullNames As Boolean) As String()
Dim months() As String
Dim i As Integer

ReDim months(1 To 12)
For i = 1 To 12
months(i) = MonthName(i, Not fullNames)
Next i

GetMonthArray = months
End Function

' Pattern 7: Format month/year string
Function FormatMonthYear(dateValue As Date, abbreviate As Boolean) As String
FormatMonthYear = MonthName(Month(dateValue), abbreviate) & " " & Year(dateValue)
End Function

' Pattern 8: Get fiscal month name
Function GetFiscalMonthName(fiscalMonth As Integer, abbreviate As Boolean) As String
' Fiscal month 1 might be October (calendar month 10)
Dim calendarMonth As Integer
Dim fiscalStartMonth As Integer

fiscalStartMonth = 10 ' October
calendarMonth = fiscalMonth + fiscalStartMonth - 1

If calendarMonth > 12 Then
calendarMonth = calendarMonth - 12
End If

GetFiscalMonthName = MonthName(calendarMonth, abbreviate)
End Function

' Pattern 9: Create report header
Function CreateMonthlyReportHeader(reportDate As Date) As String
Dim header As String
header = "Monthly Report - " & MonthName(Month(reportDate)) & " " & Year(reportDate)
CreateMonthlyReportHeader = header
End Function

' Pattern 10: Compare month names
Function IsSameMonth(date1 As Date, date2 As Date) As Boolean
IsSameMonth = (MonthName(Month(date1)) = MonthName(Month(date2))) And _
(Year(date1) = Year(date2))
End Function

Advanced Usage

Example 1: Calendar Generator

' Class: CalendarGenerator
' Generates formatted calendar displays

Option Explicit

Private m_year As Integer
Private m_month As Integer
Private m_firstDayOfWeek As VbDayOfWeek

Public Sub Initialize(calYear As Integer, calMonth As Integer)
m_year = calYear
m_month = calMonth
m_firstDayOfWeek = vbSunday
End Sub

Public Function GetMonthHeader(abbreviate As Boolean) As String
GetMonthHeader = MonthName(m_month, abbreviate) & " " & m_year
End Function

Public Function GenerateCalendar() As String
Dim calendar As String
Dim firstDay As Date
Dim lastDay As Date
Dim currentDay As Date
Dim dayOfWeek As Integer
Dim weekRow As String
Dim dayCount As Integer

' Create header
calendar = String(28, " ") & GetMonthHeader(False) & vbCrLf
calendar = calendar & "Su  Mo  Tu  We  Th  Fr  Sa" & vbCrLf

' Get first and last day of month
firstDay = DateSerial(m_year, m_month, 1)
lastDay = DateSerial(m_year, m_month + 1, 0)

' Start with padding for first week
dayOfWeek = Weekday(firstDay, m_firstDayOfWeek)
weekRow = String((dayOfWeek - 1) * 4, " ")

' Fill in days
currentDay = firstDay
Do While currentDay <= lastDay
weekRow = weekRow & Format(Day(currentDay), "00") & "  "
dayOfWeek = dayOfWeek + 1

If dayOfWeek > 7 Then
calendar = calendar & weekRow & vbCrLf
weekRow = ""
dayOfWeek = 1
End If

currentDay = DateAdd("d", 1, currentDay)
Loop

If Len(weekRow) > 0 Then
calendar = calendar & weekRow & vbCrLf
End If

GenerateCalendar = calendar
End Function

Public Function GetAllMonthsForYear(abbreviate As Boolean) As String()
Dim months() As String
Dim i As Integer

ReDim months(1 To 12)
For i = 1 To 12
months(i) = MonthName(i, abbreviate)
Next i

GetAllMonthsForYear = months
End Function

Example 2: Report Generator with Month Names

' Class: MonthlyReportFormatter
' Formats reports with localized month names

Option Explicit

Private m_useAbbreviations As Boolean
Private m_reportData As Collection

Public Sub Initialize(useAbbrev As Boolean)
m_useAbbreviations = useAbbrev
Set m_reportData = New Collection
End Sub

Public Sub AddMonthData(monthNum As Integer, dataValue As Double)
Dim entry As String
entry = MonthName(monthNum, m_useAbbreviations) & ":" & dataValue
m_reportData.Add entry
End Sub

Public Function GenerateReport() As String
Dim report As String
Dim item As Variant
Dim i As Integer

report = "Monthly Summary Report" & vbCrLf
report = report & String(50, "-") & vbCrLf

For Each item In m_reportData
report = report & item & vbCrLf
Next item

GenerateReport = report
End Function

Public Function FormatDateWithMonth(dateValue As Date) As String
FormatDateWithMonth = MonthName(Month(dateValue), m_useAbbreviations) & _
" " & Day(dateValue) & ", " & Year(dateValue)
End Function

Public Function GetQuarterMonths(quarter As Integer) As String
Dim months As String
Dim startMonth As Integer
Dim i As Integer

startMonth = ((quarter - 1) * 3) + 1

For i = 0 To 2
If i > 0 Then months = months & ", "
months = months & MonthName(startMonth + i, m_useAbbreviations)
Next i

GetQuarterMonths = months
End Function

Example 3: Internationalized Date Formatter

' Module: InternationalDateFormatter
' Provides locale-aware date formatting using MonthName

Option Explicit

Public Enum DateFormat
dfLong = 0          ' "January 15, 2025"
dfMedium = 1        ' "Jan 15, 2025"
dfShort = 2         ' "01/15/2025"
dfMonthYear = 3     ' "January 2025"
dfMonthYearShort = 4 ' "Jan 2025"
End Enum

Public Function FormatDate(dateValue As Date, formatType As DateFormat) As String
Select Case formatType
Case dfLong
FormatDate = MonthName(Month(dateValue)) & " " & _
Day(dateValue) & ", " & Year(dateValue)

Case dfMedium
FormatDate = MonthName(Month(dateValue), True) & " " & _
Day(dateValue) & ", " & Year(dateValue)

Case dfShort
FormatDate = Format(dateValue, "mm/dd/yyyy")

Case dfMonthYear
FormatDate = MonthName(Month(dateValue)) & " " & Year(dateValue)

Case dfMonthYearShort
FormatDate = MonthName(Month(dateValue), True) & " " & Year(dateValue)
End Select
End Function

Public Function FormatDateRange(startDate As Date, endDate As Date, _
abbreviate As Boolean) As String
Dim result As String

If Year(startDate) = Year(endDate) Then
If Month(startDate) = Month(endDate) Then
' Same month and year
result = MonthName(Month(startDate), abbreviate) & " " & _
Day(startDate) & "-" & Day(endDate) & ", " & Year(startDate)
Else
' Different months, same year
result = MonthName(Month(startDate), abbreviate) & " " & Day(startDate) & " - " & _
MonthName(Month(endDate), abbreviate) & " " & Day(endDate) & ", " & _
Year(endDate)
End If
Else
' Different years
result = MonthName(Month(startDate), abbreviate) & " " & Day(startDate) & ", " & _
Year(startDate) & " - " & _
MonthName(Month(endDate), abbreviate) & " " & Day(endDate) & ", " & _
Year(endDate)
End If

FormatDateRange = result
End Function

Public Function GetMonthNames(abbreviate As Boolean) As String()
Dim months(1 To 12) As String
Dim i As Integer

For i = 1 To 12
months(i) = MonthName(i, abbreviate)
Next i

GetMonthNames = months
End Function

Public Function ParseMonthName(monthString As String) As Integer
Dim i As Integer

' Try full names first
For i = 1 To 12
If UCase(MonthName(i)) = UCase(monthString) Then
ParseMonthName = i
Exit Function
End If
Next i

' Try abbreviated names
For i = 1 To 12
If UCase(MonthName(i, True)) = UCase(monthString) Then
ParseMonthName = i
Exit Function
End If
Next i

ParseMonthName = 0 ' Not found
End Function

Example 4: Chart Label Generator

' Class: ChartLabelGenerator
' Generates chart labels with month names

Option Explicit

Private m_startMonth As Integer
Private m_monthCount As Integer
Private m_abbreviate As Boolean

Public Sub Initialize(startMonth As Integer, monthCount As Integer, _
abbreviate As Boolean)
If startMonth < 1 Or startMonth > 12 Then
Err.Raise 5, "ChartLabelGenerator", "Invalid start month"
End If

m_startMonth = startMonth
m_monthCount = monthCount
m_abbreviate = abbreviate
End Sub

Public Function GetLabels() As String()
Dim labels() As String
Dim i As Integer
Dim currentMonth As Integer

ReDim labels(0 To m_monthCount - 1)

For i = 0 To m_monthCount - 1
currentMonth = m_startMonth + i

' Wrap around if exceeds 12
If currentMonth > 12 Then
currentMonth = currentMonth - 12
End If

labels(i) = MonthName(currentMonth, m_abbreviate)
Next i

GetLabels = labels
End Function

Public Function GetLabelWithYear(monthNum As Integer, yearNum As Integer) As String
GetLabelWithYear = MonthName(monthNum, m_abbreviate) & " '" & _
Right(CStr(yearNum), 2)
End Function

Public Function GetQuarterLabel(quarter As Integer, yearNum As Integer) As String
Dim firstMonth As Integer
firstMonth = ((quarter - 1) * 3) + 1

GetQuarterLabel = "Q" & quarter & " (" & _
MonthName(firstMonth, True) & "-" & _
MonthName(firstMonth + 2, True) & " " & _
yearNum & ")"
End Function

Public Function GetYearLabels(abbreviate As Boolean) As String()
Dim labels(1 To 12) As String
Dim i As Integer

For i = 1 To 12
labels(i) = MonthName(i, abbreviate)
Next i

GetYearLabels = labels
End Function

Public Function FormatMultiYearLabel(monthNum As Integer, _
startYear As Integer, _
endYear As Integer) As String
FormatMultiYearLabel = MonthName(monthNum, m_abbreviate) & " " & _
startYear & "-" & endYear
End Function

Error Handling

On Error Resume Next
monthName = MonthName(monthNum)
If Err.Number = 5 Then
MsgBox "Invalid month number. Must be between 1 and 12."
ElseIf Err.Number <> 0 Then
MsgBox "Error getting month name: " & Err.Description
End If
On Error GoTo 0

Performance Considerations

MonthName() is very fast - simple lookup operation
For repeated calls, consider caching month name arrays
No performance difference between full and abbreviated names
Locale lookup may add minimal overhead but is negligible
More efficient than maintaining hardcoded month name arrays
Can be called millions of times without performance concerns

Best Practices

Validate month numbers - Always check that month is 1-12 before calling
Use for localization - Leverages system locale for international support
Combine with Month() - Extract month number from dates, then get name
Consider abbreviations - Use abbreviated names for space-constrained displays
Cache for UI - Populate drop-downs once, not on every refresh
Document format - Clearly state whether full or abbreviated names are expected
Test locales - Test with different regional settings for international apps
Handle errors - Wrap in error handling for robust code
Use constants - Define month number constants for readability
Prefer over arrays - Use MonthName() instead of hardcoded name arrays

Comparison with Other Approaches

Approach	Pros	Cons
`MonthName()`	Automatic localization, simple, no maintenance	Requires VBA/VB6, slightly slower than array
Hardcoded array	Fast, full control	No localization, maintenance burden
`Format()`	Flexible formatting	Returns different formats, not just name
`DatePart()`	Returns number, not name	Need to convert to name separately

Localization Notes

The MonthName function returns month names based on the system's regional settings: - English (US): "January", "February", etc. / "Jan", "Feb", etc. - Spanish: "enero", "febrero", etc. / "ene", "feb", etc. - French: "janvier", "février", etc. / "janv", "févr", etc. - German: "Januar", "Februar", etc. / "Jan", "Feb", etc. - Japanese: "1月", "2月", etc. - And many more locales...

Platform Notes

Available in VBA (Excel, Access, Word, etc.)
Available in VB6
Not available in VBScript (VBScript lacks this function)
Returns String type
Consistent across all VBA platforms
Uses Control Panel regional settings
First introduced in VB6 and VBA 6.0

Limitations

Month parameter must be 1-12 (no 0 or 13+)
Error 5 if month is out of valid range
Returns names based on system locale only (cannot specify locale)
Abbreviated length varies by locale (usually 3 chars, but not always)
Not available in VBScript
Cannot customize the returned names

Month - Returns the month number (1-12) from a date
WeekdayName - Returns the name of the weekday (similar function for days)
Format - Can format dates with month names using format strings
DatePart - Returns various parts of a date (including month number)
Year - Returns the year component of a date
Day - Returns the day component of a date

VB6 Parser Notes

MonthName is parsed as a regular function call (CallExpression). This module exists primarily for documentation purposes to provide comprehensive reference material for VB6 developers working with date formatting and display operations requiring localized month names.

MonthName Function