2019/12/21

ワークシート上で永久カレンダーを作る




Excelのワークシートで永久カレンダーを作ります。

１．年月手動切り替えのカレンダー

まず最初は、左上セル（B2及びC2）の年月を手動で変えることで、好きな年月のカレンダーを表示できるものを作ります。
（一番下に添付したExcelファイルのSheet1）

図１

B2及びC2セルの年・月の数値をもとに、それぞれのセルには図２のような数式・数値が入っています。同じ種類の式が入っているのは赤枠で示しており、赤吹き出し内の数式は黄色セルのものです。他セルの式は相対セル位置を考慮してズラして下さい。

図２

このカレンダーは１か月用で、その初日をB2及びC2セルの数値を使ってF2セルで計算しています。その曜日をF3セル、最終日をF4セルで計算します。D5～F5で結合（マージ）してあるセルは、表示用です。
曜日の上の６行目には、その曜日に対応した数値が記入してあります。その曜日数値とF3セルの初日曜日数値を比較して、カレンダーの１週目（Excelで８行目）を作ります。
尚、このカレンダー１週目の作り方については「IF文を重ねて書く」の中で、また最終日の計算方法については「月の最終日の計算方法」の中で詳しく説明しています。

次に、カレンダーの２～４週目には、必ず数値（日付）が入ります。ですので「１つ前のセルに対して＋１」する式を入れます。

５～６週目はどこかで最終日が入り、そこから先は空欄になります。数式としては「前日が空欄だったら空欄」＋「前日が最終日だったら空欄」＋「前日が最終日以前だったら＋１」を入れます。
尚、このカレンダー５～６週目の作り方についても「IF文を重ねて書く」の中で詳しく説明しています。

２．スクロールバーで年月を切り替えるカレンダー（マクロ無し）

「手動で年月を書き換えるのはちょっと・・・」という方もいるでしょうから、右上（B5の位置）と左上（H5の位置）のボタンを操作することで、年・月を変更できるカレンダーを考えます。
（一番下に添付したExcelファイルのSheet2）


図３

シート内の数式は、上記の「年月手動切り替えカレンダー」と全く同じです。異なっているのは「年移動用のスクロールバー（B5セルの位置）」と「月移動用のスクロールバー（H5セルの位置）」のみです。
尚、スクロールバーには「フォームコントロール」と「ActiveXコントロール」の２種類がありますが、今回は「年月手動切り替えカレンダーの年月値をスクロールバーから変更する」事で効率化？を目指しているので、セル値変更の機能を持っている「フォームコントロール」で作成します。

そして、各スクロールバーの書式設定のコントロールの「リンクするセル」を、年のスクロールバーはB2セルへ、月のスクロールバーはC2セルへリンクさせます。
　　
図４

尚、B2セルC2セルの年月ですが「1978年502月」というのは変ですよね。でも計算上は有り得るのです。月で12を超えた分は年に加算されていき、「1978年502月」を計算してみると「2019年10月」（＝D5～F5セルの計算結果）になるのです。
このようにした理由ですが、H5セル部に置いた月を変更するスクロールバーは、最小値がゼロ以下には設定できないので、月を減らしていくとゼロでストップしてしまうからです。ストップが掛からない様にするためには、「初期値を500月くらいにしておき、西暦年をその分減らす」方法としました。（どんどん減らしたり増やしたりていけば当然どこかでストップはしますが、実用には耐えられそうです）

ですので、月を変更するスクロールバーの最大値は大きめに設定し、現在値をその中央くらいにしておいて下さい。尚、表示しているカレンダーの年月はD5～F5セルにちゃんと表されているので、B2セルC2セルは「行の非表示」や「文字色を背景と同じにする」などの手段で見えなくすると勘違いされずに済みます。

ここまでは、マクロを使用していませんので「.xlsx」で保存できます。

３．スクロールバーで年月を切り替えるカレンダー（マクロ有り）

「1978年502月　は許されないよね」という方のために少し改造しましょう。見かけも計算式もは２番目と全く同じですが、「B2セルC2セルの値　＝ D5～F5セルの値」になっています。
（一番下に添付したExcelファイルのSheet3）

図５

実は、このシートには以下のマクロがついています。カレンダーのワークシートにマクロを作成して下さい。
  1. Sub S2_Change()
  2. 　Select Case Cells(2, 3).Value
  3. 　　Case 0
  4. 　　　Cells(2, 3).Value = 12
  5. 　　　Cells(2, 2).Value = Cells(2, 2).Value - 1
  6. 　　Case 13
  7. 　　　Cells(2, 3).Value = 1
  8. 　　　Cells(2, 2).Value = Cells(2, 2).Value + 1
  9. 　End Select
  10. End Sub
図６

そして、月を変更するスクロールバーの「マクロ登録」に作成したマクロ（ここでは、S2_Change）を登録して下さい。
また、コントロールの最大値は13に、最小値を0（ゼロ）に設定して下さい。
尚、年を変更するスクロールバーへのマクロ登録は不要です。最小値を1900にしておいて下さい。（Excelの日付は1900年1月1日を起点に始まっていますので、それ以前は日付計算が出来ません）
　　
図７

動きを説明します。
月の「スクロールバー」を押すたびにマクロが動きます。但しC2セル（マクロでは Cells(2,3））が0（ゼロ）か13の時以外はスルーしています。
「スクロールバー」を下げて行き、C2セルが0（ゼロ）になった時には、C2セルを12（＝12月）に変更し同時に年の値を入れているB2セルの値を１つ減らしています。
「スクロールバー」を上げて行き、C2セルが13になった時には、C2セルを1（＝1月）に変更し同時に年の値を入れているB2セルの値を１つ増やしています。

つまり、13月は次の年の1月、0月は前の年の12月　というExcelの年月日計算の特徴を使用したものです。

４．マクロでカレンダーを表示する

上の３つの方法はワークシート関数でカレンダーを表示していましたが、全てマクロで行うこともできます。黒塗りの部分は、今回のマクロでは使用しないデータのため、塗りつぶしてあります。
また、ワークシート上に配置したボタン「＜＜」「＜」「＞」「＞＞」「今月のカレンダー」は、図１０のマクロの内、「minus_year」「minus_month」「plus_month」「plus_year」「kaisi」プロシージャにマクロ実行割り当てをします。
（一番下に添付したExcelファイルのSheet4）

図８

またマクロ内でデータ取得・出力する範囲を設定する為、現在表示されているカレンダーのエリアに名前（今回は、Cal_Area）を、カレンダーの年月に名前（今回は、Year_Month）を付けておきます。（各赤枠部分）
　　
図９

以下のマクロをワークシートのマクロとして記載します。
  1. Sub kaisi()　　　　　　　　　　　　　　　　　　　 '←カレンダーがカラの時の初期化プロシージャ
  2. 　Call Calendar(Year(Now()), Month(Now()))
  3. End Sub
  5. Sub Calendar(yyyy, mm)　　　　　 '←カレンダーを計算・表示させるプロシージャ
  6. 　Dim cal_first_day As Date　　　　 '←表示カレンダーの初日
  7. 　Dim first_week,last_day As Integer　　　 '←初日の曜日、最終日
  8. 　Dim i,j,k As Integer　　　　　　 '←カウンター変数
  9. 　Dim cal_array(1 To 6, 1 To 7) As Variant　　　 '←カレンダー表示部の6行7列の配列
  11. 　cal_first_day = DateSerial(yyyy, mm, 1)　　　 '←表示カレンダーの初日
  12. 　first_week = Weekday(DateSerial(yyyy, mm, 1), 1)　　 '←初日の曜日
  13. 　last_day = Day(DateSerial(yyyy, mm + 1, 1) - 1)　　　 '←最終日
  15. 　k = 1
  16. 　For i = 1 To 6
  17. 　　For j = first_week To 7　　　　　 '←1週目は初日曜日から、その他は日曜日から記入
  18. 　　　cal_array(i, j) = k
  19. 　　　k = k + 1
  20. 　　　If k > last_day Then GoTo cont　　　 '←最終日になったら2つのForを抜ける
  21. 　　Next j
  22. 　　first_week = 1　　　　 '←2週目からは日曜日(数値1)から記入
  23. 　Next i
  24. 　cont:
  25. 　Range("Cal_Area") = cal_array　　　　 '←カレンダーエリアに配列を書き込む
  26. 　Range("Year_Month") = Year(cal_first_day) & "/" & Month(cal_first_day)　　　 '←表示部に年/月を記入
  27. End Sub
  29. Sub plus_month()　　　　　 '←月を増加
  30. 　Dim yyyy,mm As Integer
  31. 　yyyy = Split(Range("Year_Month").Value, "/")(0)　　　　　　　 '←年月エリアの年を取り出す
  32. 　mm = Split(Range("Year_Month").Value, "/")(1) + 1　　　　　　　 '←年月エリアの月を取り出し＋１する
  33. 　Call Calendar(yyyy, mm)　　　　 '←Calendarプロシージャの呼び出し
  34. End Sub
  36. Sub minus_month()　　　　　 '←月を減少
  37. 　Dim yyyy,mm As Integer
  38. 　yyyy = Split(Range("Year_Month").Value, "/")(0)
  39. 　mm = Split(Range("Year_Month").Value, "/")(1) - 1
  40. 　Call Calendar(yyyy, mm)
  41. End Sub
  43. Sub plus_year()　　　　　 '←年を増加
  44. 　Dim yyyy,mm As Integer
  45. 　yyyy = Split(Range("Year_Month").Value, "/")(0) + 1
  46. 　mm = Split(Range("Year_Month").Value, "/")(1)
  47. 　Call Calendar(yyyy, mm)
  48. End Sub
  50. Sub minus_year()　　　　 '←年を減少
  51. 　Dim yyyy,mm As Integer
  52. 　yyyy = Split(Range("Year_Month").Value, "/")(0) - 1
  53. 　mm = Split(Range("Year_Month").Value, "/")(1)
  54. 　Call Calendar(yyyy, mm)
  55. End Sub
図１０

図１０の１行目のkaisiプロシージャは、ワークシートのカレンダーがカラの時に今月のカレンダーを表示させるものです。これは、カラの状態で年・月の変更ボタンを押されるとエラーが発生してしまうのを防止するためです。

５行目のCalendarプロシージャは、引数としてyyyy（年）とmm（月）を受け取り、カレンダーの計算をしワークシートに書き込むものです。
１１～１３行目は、変数「cal_first_day」「first_week」「last_day」を計算します。これは上記３種のカレンダーのＦ２セル，Ｆ３セル，Ｆ４セルの計算式と同じです。
その値を使い、１６行目～２３行目でForを二重に回して、カレンダー配列に数字を埋めていきます。まず「１」を初日曜日（first_week）に合った曜日に記入し週ごとに数字を埋めていきます。そして最終日（last_day）が来たところ（２０行目）で二重のForを抜け出し、２５行目で配列をかき出します。最後に年月表示部に「年/月」の表示形式で書き込みます。

一方、年及び月を増減させるボタンを押すと「plus_month」「minus_month」「plus_year」「minus_year」プロシージャが動きます。
各プロシージャの中で「Split関数」を使っています。これは文字列をある文字・記号で切り分ける関数で、例えば「2019/10/1」という文字列があった場合に「Split( "2019/10/1" , "/" )」とすると、「/」で挟まれた文字列を配列にして返します（最初と最後の部分は「/」の前・後まで）。
Split( "2019/10/1" , "/" )　⇒　
　　0　　　　1　　　　2　　
2019101

Split関数で返ってきたのは配列ですから、その後ろにカッコ付きで要素番号を指定すれば配列の中身が取り出せます。

例：Split( "2019/10/1" , "/" )(1)　→　10

このSplit関数で年月に分割し増減させたのちCalendarプロシージャを呼び出せば、カレンダーが書き換わるわけです。


尚、カレンダーのエリア・年月エリアに名前をつけず、マクロの中で設定する方法もあります。
  1. Dim Year_Month , Cal_Area As Range
  3. Sub kaisi()
  4. 　Set Year_Month = Cells(2, 4)
  5. 　Set Cal_Area = Range(Cells(5, 2), Cells(10, 8))
  6. 　Call Calendar(Year(Now()), Month(Now()))
  7. End Sub
図１１

しかし、この方法だと、ブックを立ち上げた直後の状態では変数Year_Month と 変数Cal_Areaにデータがセットされておらず、年・月変更ボタンを押すとエラーが発生します。「開始ボタン」を押させたくないのであれば、Workbook_openイベント等でデータセットを自動で実行させる方法も考えられます。
但しカレンダーのデザインが固定しているのであれば、エリアへの名前付けの方が簡単・確実と思います。



永久カレンダー（it-010.xlsm）
セキュリティ向上を目的として「インターネット経由でダウンロードしたOfficeファイル（Excel等）のマクロは、既定でブロック」されるようにOfficeアプリケーションの既定動作が変更になりました。（2022年4月より切替開始）
解除の方法については「ダウンロードファイルのブロック解除方法」を参照下さい。