如何在Excel中创建每月或每年的日历

Excel是一个有效的程序，可以用于许多事情，包括创建日历。无论您是想管理个人日程安排、跟踪重要事件还是安排业务活动，Excel都提供了一个灵活且可调整的平台来构建适合您独特需求的日历。本文将逐步引导您使用Excel创建每月和每年的日历。我们将介绍创建既实用又美观的日历所需的核心方法和组件。无需任何Excel经验，我们将用简单的术语逐步指导您完成每个步骤。

在本教程结束时，您将能够创建您自己的自定义日历，包括必要的细节，如日期、星期几和节假日。此外，您还将了解如何格式化和个性化日历的外观，使您可以赋予它们独特的风格并提高其美观性。因此，让我们开始探索Excel创建动态、交互式和实用日历的能力，这些日历将使您全年保持井然有序并了解您的重要活动。

在Excel中创建每月/每年的日历

在这里，我们将首先创建一个VBA模块，然后运行它并指定月份和年份以完成任务。让我们来看一个简单的过程，了解如何在Excel中创建每月或每年的日历。

步骤1

考虑任何Excel表格。

首先，右键单击工作表名称，然后选择“查看代码”以打开VBA应用程序。

步骤2

然后单击“插入”，选择“模块”，并将下面的代码复制到文本框中。

插入 > 模块 > 复制。

示例

Sub CalendarMaker()

   ActiveSheet.Protect DrawingObjects:=False, Contents:=False, _
   Scenarios:=False
   Application.ScreenUpdating = False
   On Error GoTo MyErrorTrap
   Range("a1:g14").Clear
   MyInput = InputBox("Type in Month and year for Calendar ")
   If MyInput = "" Then Exit Sub
   StartDay = DateValue(MyInput)
   If Day(StartDay) <> 1 Then
      StartDay = DateValue(Month(StartDay) & "/1/" & _
         Year(StartDay))
      End If
      Range("a1").NumberFormat = "mmmm yyyy"
      With Range("a1:g1")
         .HorizontalAlignment = xlCenterAcrossSelection
         .VerticalAlignment = xlCenter
         .Font.Size = 18
         .Font.Bold = True
         .RowHeight = 35
      End With
      With Range("a2:g2")
         .ColumnWidth = 11
         .VerticalAlignment = xlCenter
         .HorizontalAlignment = xlCenter
         .VerticalAlignment = xlCenter
         .Orientation = xlHorizontal
         .Font.Size = 12
         .Font.Bold = True
         .RowHeight = 20
      End With
      Range("a2") = "Sunday"
      Range("b2") = "Monday"
      Range("c2") = "Tuesday"
      Range("d2") = "Wednesday"
      Range("e2") = "Thursday"
      Range("f2") = "Friday"
      Range("g2") = "Saturday"
      With Range("a3:g8")
         .HorizontalAlignment = xlRight
         .VerticalAlignment = xlTop
         .Font.Size = 18
         .Font.Bold = True
         .RowHeight = 21
      End With
      Range("a1").Value = Application.Text(MyInput, "mmmm yyyy")
      DayofWeek = WeekDay(StartDay)
      CurYear = Year(StartDay)
      CurMonth = Month(StartDay)
      FinalDay = DateSerial(CurYear, CurMonth + 1, 1)
      Select Case DayofWeek
         Case 1
            Range("a3").Value = 1
         Case 2
            Range("b3").Value = 1
         Case 3
            Range("c3").Value = 1
         Case 4
            Range("d3").Value = 1
         Case 5
            Range("e3").Value = 1
         Case 6
            Range("f3").Value = 1
         Case 7
            Range("g3").Value = 1
      End Select
      For Each cell In Range("a3:g8")
         RowCell = cell.Row
         ColCell = cell.Column
         If cell.Column = 1 And cell.Row = 3 Then
         ElseIf cell.Column <> 1 Then
            If cell.Offset(0, -1).Value >= 1 Then
               cell.Value = cell.Offset(0, -1).Value + 1
               If cell.Value > (FinalDay - StartDay) Then
                  cell.Value = ""
                  Exit For
                  End If
               End If
         ElseIf cell.Row > 3 And cell.Column = 1 Then
            cell.Value = cell.Offset(-1, 6).Value + 1
            ' Stop when the last day of the month has been entered.
            If cell.Value > (FinalDay - StartDay) Then
               cell.Value = ""
               Exit For
               End If
         End If
      Next

      For x = 0 To 5
         Range("A4").Offset(x * 2, 0).EntireRow.Insert
         With Range("A4:G4").Offset(x * 2, 0)
            .RowHeight = 65
            .HorizontalAlignment = xlCenter
            .VerticalAlignment = xlTop
            .WrapText = True
            .Font.Size = 10
            .Font.Bold = False
            .Locked = False
         End With
         ' Put border around the block of dates.
         With Range("A3").Offset(x * 2, 0).Resize(2, _
         7).Borders(xlLeft)
            .Weight = xlThick
            .ColorIndex = xlAutomatic
         End With

         With Range("A3").Offset(x * 2, 0).Resize(2, _
         7).Borders(xlRight)
            .Weight = xlThick
            .ColorIndex = xlAutomatic
         End With
         Range("A3").Offset(x * 2, 0).Resize(2, 7).BorderAround _
            Weight:=xlThick, ColorIndex:=xlAutomatic
      Next
      If Range("A13").Value = "" Then Range("A13").Offset(0, 0) _
         .Resize(2, 8).EntireRow.Delete
      ActiveWindow.DisplayGridlines = False
      ActiveSheet.Protect DrawingObjects:=True, Contents:=True, _
          Scenarios:=True

      ActiveWindow.WindowState = xlMaximized
      ActiveWindow.ScrollRow = 1
      Application.ScreenUpdating = True
      Exit Sub
   MyErrorTrap:
      MsgBox "You may not have entered your Month and Year correctly." _
      & Chr(13) & "Spell the Month correctly" _
      & " (or use 3 letter abbreviation)" _
      & Chr(13) & "and 4 digits for the Year"
      MyInput = InputBox("Type in Month and year for Calendar")
      If MyInput = "" Then Exit Sub
      Resume
   End Sub