如何在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