エクセル関数とエクセルVBAを組み合わせてカレンダーを自作
前のページでもお話ししましたが、カレンダーをVBAのみで作る知識は無いのと、カレンダーコントロールを組み込んだエクセルファイルから自作カレンダーに差し替えることを優先しました。
その為に様々なケースに対応できるように、基本的なことはワークシート上にエクセル関数を使い、繰り返し作業の部分だけVBAコードを使っています。
最初にユーザーフォームの作成をしたいところなのですが、先ほどもお話しした通り私のような初心者レベルではユーザーフォームで全てエクセルVBAで作る事はできません。
その為に最初にやる事は、ワークシートにカレンダーをエクセル関数を使って作成します。
カレンダーといっても基本操作はユーザーフォームで行うので、ワークシートの余白でもどこでもいいので、後から付け足すことも簡単ですよ。
ワークシート上でのカレンダー設定
最初にカレンダーの日付リストを作ります。
ここでは、元々はカレンダーコントロールを使っていたエクセルファイルに組み込んだので、セルA1からではなく空いていたセルBG1から始まっています。
左図の説明をします。（クリックで拡大可）
- 【セルBG1】セルBG2とセルBG3からカレンダー表示したい年月日表示
- 【セルBG2】カレンダー表示したい年
- 【セルBG3】カレンダー表示したい月
- 【列BI】コンボボックスに表示する為の年・月
- 【列BJ】コマンドボタンの番号を間違わない為に用意（無くても大丈夫です）
- 【列BK】関数により計算された日付
- 【列BL】ユーザーフォームに表示する日付（年・月を除いた日だけにする）
- 【列BM】当月以外を半透明にするために用意したけど挫折中
- 【列BO】祝日（振替など）の為に用意したけど挫折中
エクセルVBAの知識がまだまだ🔰の頃は、このようにワークシートに事前準備することで、自作カレンダーを作ることができました。
ちょっとVBAの知識を習得できたら、ここに記載しているワークシート上に日付（年月日）を用意する方法ですが、コンボボックスを利用して西暦を現在日付から自動取得させることもできるようになりました。
話を戻して、緑色のセルに日付に関するエクセル関数が入っていますのでその説明をします。
DATE関数で日付を自動表示
日付を自動的に表示するエクセル関数が【DATE関数】で、「年・月・日」を3つの数字、言い換えれば3か所のセルの値から組み合わせて日付表示をしてくれるエクセル関数です。
その為にセルの値が変われば、自動的に日付が変更されるので便利な関数なので、先ほどの図のようにセルBG2とセルBG3の値が変わればセルBG1が変わるので、列BKも変わり、ユーザーフォームの日付も自動的に変わってくれるよいう仕組みなのです。
ここでは、年・月しか入力しないので、日は常に1日とします。そうすると・・・
「=DATE(年,月,日)」となるので、図を参考にすると「=DATE(BG2,BG3,1)」になります。
WEEKDAY関数で日付と曜日を自動修正
日付自動表示ができたら、次は日付と曜日を【WEEKDAY関数】で自動修正しましょう。
最初、何も考えずシンプルにセルBG1の日付を列BK1から順にコピーしても日付も曜日も思い通りに表示されません。
それもそのはず、2018年6月1日は日曜日ではなく、金曜日なのです。
カレンダーではその前にある「日・月・火・水・木」には5月最終週の日付表示をさせなくてはいけないからです。
このWEEKDAY関数で月が替わればカレンダーも連動して日付表示が変わってくれます。
本来WEEKDAY関数は曜日を教えてくれる関数なので、「=WEEKDAY(BG1)」とすると、2018/6/1ならば、6となります。日曜日から数えると6番目だからです。
最初のワークシートの図で説明するとBK6に2018年6月1日が表示されなくてはいけないので、BK1は2018年5月27日にすればいいのです。
その為に6月1日から引いてあげましょう。
「=BG1-WEEKDAY(BG1)」とすると2018/5/26になります。
正しく表示されませんね。
当たり前で、6月1日から6を引いたからで引き過ぎなのです。
6番目が6月1日なので、本当は5を引けばいいのですがWEEKDAY関数は先ほどもお話しした通り、あくまでも曜日を数値化して返すので無理なんです。
それならば、1引きすぎたので、単純に1を足してあげれば完了です。そこで、こうすればいいのです。
「=BG1-WEEKDAY(BG1)+1」になります。
あとは、BK2からBK42までは、上の数字に1を足すだけでいいので、セルBK2には「=BK1+1」になります。
セルBK3には「=BK2+1」・・・セルBK42には「=BK41+1」です。
ただ、このままだと表示が2018/6/1とカレンダー表示に必要のない年・月があるので、表示を日付だけにします。
セルBK1からBK42を選択して
➡右クリック
➡セルの書式設定
➡表示形式
➡分類で「ユーザー定義」を選び
➡種類を「d」にすると日付のみの表示にできます。
これでワークシートでのエクセル関数【DATE関数】と【WEEKDAY関数】を使ったカレンダー作成の準備は一段落です。
ユーザーフォームの設定
カレンダーの土台はすでに完成したので、次はユーザーフォームを使ってVBAコードを書いていきますが、ユーザーフォームについての詳細はここでは省略します。
セルの値を読み取ってフォーム上に表示させて、コマンドボタンで操作するだけです。
左図のようにカレンダーをエクセルVBAのユーザーフォームから作ります。
ユーザーフォームには「コンボボックス」と「ラベル」と「コマンドボタン」で作成していますので順に説明を書いていきます。
また、土日には色を付けていますが、私の知識では祝日には対応できていません。
年・月をコンボボックスで作成
カレンダーで日付選択する時には、年・月はある程度決まっている事が多いので、テキストボックスに数字を毎回入力するよりはリストから選んだ方が確実です。
例えばスケジュールの入力や商品仕入れであれば、過去はあまりないので2018・2019・2020ぐらいでいいでしょうし、まして月は1月～12月と決まっています。
そこでコンボボックスを配置し、データ元（プロパティRowSource）はBI2:BI4が年でBI7:BI18が月で設定するだけなので、年を変えるならばこの部分を変更するだけでいつでも変えることができます。
42個のコマンドボタンを配置
一番面倒で地味な作業ですが、カレンダーで日付表示をする為にコマンドボタンを1週間分×6週で42個並べます。
そしてちょうどいい大きさにして、綺麗に並べて、キャプションを全て消して地味な作業を繰り返します。
完成したカレンダーの画像で説明すると、CommandButton1は一番左上5月27日（日）になり、あとは日付順にCommandbutton2・・・最後一番右下7月7日（土）はCommandButton42となっています。
【日付表示】ボタンを押すことにより、この42個のコマンドボタンの日付表示が自動的に当該月の日付表示になります。
そして、各ボタンにも地味にコードを入れなくてはいけません。
Range(“A1”).Value = Range(“BK1”).Value
End Sub
CommandButton1を押すと、セルA1にセルBK1の値（日付）が転記されます。
CommandButon2には、
Range(“A1”).Value = Range(“BK2”).Value
End Sub
と入れて、これをCommandButton42まで繰り返しています。
これにより、選んだ日付は必ずA1セルに転記されてくれるので、【決定】ボタンで好きなように指示ができるので、応用範囲が増えます。
カレンダーに表示された日付を押すと指定したセルに日付が転記（表示）されるので完成しました。
この時に、転記するセルやシートによって画面がちらつくのでちらつき防止のVBAコードを書き足しておく方法もありますが、詳細はここでは省略します。
【日付表示】ボタンでカレンダー自動表示
コンボボックスで選んだ年・月をワークシートのセルBG2とBG3に転記して日付をユーザーフォーム上に表示させるコマンドボタンです。
この【日付表示】ボタン（コマンドボタン43）には下記コードが書いてあります。
If UserForm6.ComboBox1.Value = “” Or UserForm6.ComboBox2.Value = “” Then
MsgBox “日付が選択されていません”
Else
Range(“BG2”).Value = UserForm6.ComboBox1.Value
Range(“BG3”).Value = UserForm6.ComboBox2.Value
For d = 1 To 42
Cells(d, 64).Value = Day(Cells(d, 63).Value)
Next d
For i = 1 To 42
With UserForm6.Controls(“Commandbutton” & i).Caption = Cells(i, 64)
End With
Next i
End If
End Sub
- コマンドボタン（CommandButton43）が押されたら
- もしユーザーフォーム（UserForm6）のコンボボックス（ComboBox1）が空白、またはComboBox2が空白だったら
- メッセージで「日付が選択されていません」と表示する
- 入力されていたら
- セルBG2にUserForm6のComboBox1で選択された年を転記する
- セルBG3にUserForm6のComboBox2で選択された年を転記する
- 繰り返し処理で変数「d」を宣言し、繰り返し変数「d」に入る数字は1～42とする
- セル番号（行はd行目で列64列目）の値は、年月日になっているセル番号（行はd行目で列63列目）の値を日だけにして転記する
- 繰り返し1行目から42行目まで行う
- 繰り返し処理で変数「i」を宣言し、繰り返し変数「i」に入る数字は1～42とする
- UserForm6に配置されたコマンドボタンのキャプションをセル変数i行目の64列目の値を表示する（先ほどの日だけの数字が表示されます）
- Withステートメント終了
- 繰り返し1行目から42行目まで行う
- IFステートメント終了
- マクロ終了
繰り返し処理を使わずにRange(“BL1”).Value = Day(Range(“BK1”).Value)を42回繰り返しても可能ですが、面倒で大変なのでFor～Nextを使った方が簡単ですよ。
また、コマンドボタンのキャプションも一緒ですね。
もう少し知識があれば、7・8・9行目の処理が必要なくなるのですが、私の現状レベルでは年月日でWEEKDAY関数で表示だけ日付にしていても、コマンドボタンに転記すると「2018/0601」となってしまうので、やむなくこのコードを入れて、WEEKDAY関数のセルの隣に日だけ取り出した値を記載させてからコマンドボタンに表示させると「2018/6/1」が「1」となってくれました。
日付転記の【決定】ボタン
アレンジの一つです。日付を縦に続けて（例えばセルA3から順番に決定ボタンを押した分入力済み最終セルと取得して最終行の下に）転記するコードを記載してみましょう。
例えば日付を選んだらセルA1に転記されている前提で最終行に追記する方法でセルA2には「日付」と入力されています。
If Range(“A1”).Value = “” Then
MsgBox “日付が選択されていません”
Else
Range(“A3”).Select
If Range(“A4”).Value = “” Then
Range(“A4”).Value = Range(“A1”).Value
Else
ActiveCell.End(xlDown).Offset(1, 0).Value = Range(“A1”).Value
End If
End Sub
- コマンドボタン44を押したら
- もしセルA1が空欄だったら
- 「日付が選択されていません」とメッセージ表示をする
- そうでなかったら（入力されていたら）
- セルA3を選択する
- もしセルA4が空欄だったら
- セルA4の値はセルA1の値にする
- そうでなかったら（入力されていたら）
- 入力済み最終行の1行下で同じ列の値はセルA1の値にする
- Ifステートメント終了
- マクロ終了
間違った時の取消ボタン
アレンジのひとつで、連続して入力した時に間違ってボタンを押してしまったり修正したいときに1行上に戻って再入力する為のVBAコードです。
ActiveCell.End(xlDown).Offset.Value = “”
End Sub
- コマンドボタン45を押したら
- 入力済みセルの最終行の値を空欄にする
- マクロ終了
土日に色を付けて完成
本音を言うと祝日設定ができるように自分のエクセルVBAの知識があればいいのだけれど、そこまでのレベルにはまだまだ達していないので、条件におじて日付の色を変更するなんてできません。
そこで、ちょっとだけでもカレンダーっぽくしたいので土日に色を付ける事にしました。
土曜日は青色、日曜日は赤色。
条件が無いので、初歩的なラベルやコマンドボタンのプロパティで簡単に変更できます。
これで多少なりとも見栄え良くなったかな？
こんな感じでエクセルで作った自作カレンダーを利用しています。
祝日対応カレンダーフォーム
自作カレンダーフォームを作ってから約3年、コロナ禍でヒマになってしまいエクセルと向き合う時間が増えたというより、人員カットで一人当たりの作業が倍増してさらに作業時間を短縮しないと回らなくなったという状態。
そういえば、カレンダーフォームが祝日対応していないのでカレンダーをパソコンの横に置いて作業するという手間を解消しようと祝日対応カレンダーフォーム作成に挑戦してみました。
案の定、まだまだ全てをVBAで行うレベルではないので、祝日リストのみワークシートに準備することで祝日対応カレンダーフォームが完成したので、次のページでご紹介します。
