Creating a calendar in Excel using VBA

VBA Code to Create a Calendar

Sub CreateCalendar()
    Dim ws As Worksheet
    Dim month As Integer
    Dim year As Integer
    Dim firstDay As Date
    Dim lastDay As Date
    Dim day As Integer
    Dim cell As Range
    Dim i As Integer, j As Integer
    ' Ask the user for the month and year
    month = InputBox("Enter the month number (1-12):")
    year = InputBox("Enter the year:")
    ' Check if the month and year are valid
    If month < 1 Or month > 12 Then
        MsgBox "Invalid month. Please enter a month between 1 and 12.", vbCritical
        Exit Sub
    End If   
    If year < 1900 Or year > 9999 Then
        MsgBox "Invalid year. Please enter a valid year.", vbCritical
        Exit Sub
    End If
    ' Create a new worksheet for the calendar
    Set ws = ThisWorkbook.Sheets.Add
    ws.Name = "Calendar " & month & "-" & year
    ' Calculate the first and last days of the month
    firstDay = DateSerial(year, month, 1)
    lastDay = DateSerial(year, month + 1, 0)   
    ' Calendar title (month and year)
    ws.Cells(1, 1).Value = "Calendar of " & MonthName(month) & " " & year
    ws.Cells(1, 1).Font.Size = 16
    ws.Cells(1, 1).Font.Bold = True
    ws.Cells(1, 1).HorizontalAlignment = xlCenter
    ws.Range("A1:G1").Merge
    ' Weekday headers
    ws.Cells(2, 1).Value = "Sun"
    ws.Cells(2, 2).Value = "Mon"
    ws.Cells(2, 3).Value = "Tue"
    ws.Cells(2, 4).Value = "Wed"
    ws.Cells(2, 5).Value = "Thu"
    ws.Cells(2, 6).Value = "Fri"
    ws.Cells(2, 7).Value = "Sat"
    ws.Rows(2).Font.Bold = True
    ' Fill the calendar with days
    day = 1
    For i = 3 To 8 ' Rows of the calendar
        For j = 1 To 7 ' Columns (days of the week)
            ' If it's the first day of the month, start in the correct column
            If i = 3 And j = Weekday(firstDay, vbSunday) Then
                ws.Cells(i, j).Value = day
                day = day + 1
            ' Fill the remaining days
            ElseIf day <= Day(lastDay) Then
                ws.Cells(i, j).Value = day
                day = day + 1
            End If
        Next j
    Next i
    ' Adjust column widths and row heights
    ws.Columns("A:G").ColumnWidth = 4
    ws.Rows("2:8").RowHeight = 25
    ' Format the cells for the days
    For i = 3 To 8
        For j = 1 To 7
            Set cell = ws.Cells(i, j)
            cell.HorizontalAlignment = xlCenter
            cell.VerticalAlignment = xlCenter
        Next j
    Next i
    MsgBox "Calendar created for " & MonthName(month) & " " & year, vbInformation
End Sub

Explanation of the Code:

1. Ask for the month and year:
   The code starts by asking the user to input the month (between 1 and 12) and the year using InputBox. If the values entered are invalid (for example, a month outside the range 1-12), an alert is displayed, and the process is stopped.
2. Create a new worksheet:
   A new worksheet is created to display the calendar. The worksheet is named with the format « Calendar M-YYYY » (for example, « Calendar 12-2024 »).
3. Calculate the first and last day of the month:
   The first day of the month is calculated using DateSerial(year, month, 1), and the last day of the month is found using DateSerial(year, month + 1, 0), which returns the last day of the previous month (thus the month we want).
4. Calendar title:
   The title « Calendar of month year » is inserted in cell A1, and this cell is merged with the others in the row to span the width of the calendar.
5. Weekday headers:
   The headers for the days of the week (Sunday, Monday, etc.) are added in row 2. These cells are bold to make them stand out.
6. Fill the calendar with days:
   The calendar is filled row by row. The code uses a loop to place the days in the correct cells, considering the weekday of the 1st day of the month (Weekday(firstDay, vbSunday)). It continues filling the days until the last day of the month.
7. Adjust column width and row height:
   The columns are adjusted to a fixed width, and the row heights are modified to make the calendar more readable. The cells are also centered horizontally and vertically.
8. Confirmation message:
   A MsgBox pops up at the end to inform the user that the calendar has been successfully created.

How to Use the Code:

1. Open the VBA editor:
   Open Excel, then press Alt + F11 to open the VBA editor.
2. Add a module:
   In the VBA editor, go to Insert > Module to create a new module.
3. Copy the code:
   Copy the code above and paste it into the new module.
4. Run the code:
   To run the code, press F5 or go to Run > Run Sub/UserForm.

The calendar will be generated in a new worksheet with the specified month and year.

Customization:

• You can add events or color-code specific days by modifying the logic that fills the cells.
• You can also customize the font size, style, and other visual aspects of the calendar for better appearance.