－ちょっと使えるネタを紹介－
Excel VBAで、横型の月間カレンダーを作成する方法について、ご紹介します。祝日のデータがあれば、祝日も反映することができます。データベースと組み合わせると、作成したカレンダーを使って、予定表の管理ができます。カレンダーの作成とデータベースの取得と書き込みについて、マスターしていきましょう。
この記事では、横型の月間カレンダーを作成する方法について、ご紹介します。
祝日のデータがあれば、祝日も反映することができます。
カレンダーとデータベースを組み合わせると、作成したカレンダーを使って、予定表の管理ができます。
カレンダーの作成と、データベースの取得と書き込みについて、マスターしていきましょう。
では、横型の月間カレンダーを作成する方法について、解説していきます。
この記事を読むメリット
目次から見たい項目へ移動すると便利ですよ。
目次
やりたい内容は、横型の月間カレンダーを作成したい、ということになります。
次のような横型の月間カレンダーを作成してみます。
列方向に月末までを記載したカレンダーになります。
長期の計画で、複数の作業を管理する際に使えます。
祝日も反映していきます。
祝日は、別シートにあらかじめ入力しておきます。
「国民の祝日」のデータは、内閣府のサイトにありますので、コピペして使うといいです。
カレンダーの更新をやりやすくするために、「翌月」「先月」「今月」のボタンで更新できるようにします。
「翌月」ボタンで、翌月を表示することができます。
「先月」ボタンで、先月を表示することができます。
「今月」ボタンで、今月を表示することができます。
というような感じで、ボタンで月の更新ができます。
では、横型の月間カレンダーを作成していきます。
まずは、1か月分の日付を入力していきます。
指定した年と月から、1か月分の日付を入力するVBAコードです。
Sub MakeCal()
Range("4:6").Clear
Range("A4") = "日付"
Range("A5") = "曜日"
Dim A, B, C
A = DateSerial(Range("A2"), Range("A3"), 1) '指定月の1日
B = DateSerial(Range("A2"), Range("A3") + 1, 0) '指定月の月末
ReDim C(1 To 2, 1 To 31)
k = 0
For i = A To B
k = k + 1
C(1, k) = i '日付を入力
C(2, k) = i '日付を入力
Next
Range("B4").Resize(2, 31) = C 'セルに入力
End Sub
年と月は、入力しておきます。
実行すると、日付と曜日が入力されます。
さらに実行すると、日付と曜日用の2行分の日付が入力されます。
日付が2行分入力されます。
列幅を調整するとこんな感じです。
日付と曜日用で、2行分の日付を入力できました。
次は、書式を入力していきます。
書式を入力するVBAコードです。
Sub SetFormat()
Range("B4").Resize(, 31).NumberFormatLocal = "d" '「日」を表示
Range("B5").Resize(, 31).NumberFormatLocal = "aaa" '「曜日」を表示
Range("A4").Resize(3, 32).Borders.LineStyle = xlContinuous '罫線
Dim A
For Each A In Range("B4").Resize(, 31)
If Format(A, "aaa") = "日" Then
'日曜日を塗りつぶし
A.Resize(3).Interior.Color = RGB(252, 228, 214)
ElseIf Format(A, "aaa") = "土" Then
'土曜日を塗りつぶし
A.Resize(3).Interior.Color = RGB(221, 235, 247)
End If
Next
End Sub
実行すると、日付を「"d"」形式の表示形式が設定されます。
次に、曜日を「"aaa"」形式の表示形式が設定されます。
次は、罫線を引きます。
日付をループして、日曜日と土曜日を塗りつぶししていきます。
日付を1か月分ループしていきます。
日曜日を塗りつぶしします。
土曜日を塗りつぶしします。
日曜日を塗りつぶしします。
土曜日を塗りつぶしします。
日曜日を塗りつぶしします。
土曜日を塗りつぶしします。
日曜日を塗りつぶしします。
土曜日を塗りつぶしします。
日曜日を塗りつぶしします。
という感じで、書式を設定していきます。
これで、祝日以外の2022年5月のカレンダーを作成できます。
2022年5月のカレンダーを作成できました。
次は、カレンダーに祝日を反映していきます。
祝日のデータをあらかじめ、別シートに入力しておきます。
こちらの祝日をカレンダーに反映していきます。
祝日をカレンダーに反映するVBAコードです。
流れは、
という感じです。
VBAコードは、次のようになります。
Sub GetHoliday()
Dim A, B
With Sheets("月")
A = DateSerial(.Range("A2"), .Range("A3"), 1) '指定月の1日
B = DateSerial(.Range("A2"), .Range("A3") + 1, 0) '指定月の月末
End With
With Sheets("祝日")
'指定月でフィルタ
.Range("A1").AutoFilter 3, ">=" & A, xlAnd, "<=" & B
.Range("A1").CurrentRegion.Copy .Range("E1") 'フィルタ結果をコピー
.Range("A1").AutoFilter 'フィルタを解除
End With
For i = 2 To Sheets("祝日").Cells(Rows.Count, "G").End(xlUp).Row
With Sheets("月").Range("A4").Offset(0, Day(Sheets("祝日").Cells(i, "G")))
'祝日を塗りつぶし
.Resize(3).Interior.Color = RGB(252, 228, 214)
End With
Next
'コピーした値をクリア
Sheets("祝日").Range("E1").CurrentRegion.ClearContents
End Sub
では、VBAコードを実行してみます。
「2022年5月」の祝日を抽出していきます。
祝日を「2022年5月」でフィルタします。
フィルタ結果を別セルにコピーします。
フィルタを解除します。
抽出した日付をループして、塗りつぶししていきます。
コピーした日付をループしていきます。
日付でセル位置を取得して、塗りつぶしをします。
次の祝日に移動して、日付でセル位置を取得して、塗りつぶしをします。
同じように、次の祝日に移動して、日付でセル位置を取得して、塗りつぶしをします。
祝日をカレンダーに反映できました。
コピーした値は不要なので、クリアします。
これで、祝日を反映して、横型の月間カレンダーを作成できます。
祝日を反映して、月間の横型カレンダーを作成できました。
簡単にカレンダーを更新できるように、年と月の更新で実行するイベントを作成していきます。
年と月を更新で、カレンダーを作成するVBAコードを実行するVBAコードです。
「月」のシートモジュールに記載します。
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Range("A2:A3"), Target) Is Nothing Then Exit Sub
Call MakeCal 'カレンダーを作成
Call SetFormat '書式を設定
Call GetHoliday '祝日を反映
End Sub
「A2」もしくは「A3」の値が変更された場合に実行する、という感じです。
年を変更して、カレンダーを更新できます。
月を変更して、カレンダーを更新できます。
Changeイベントを使えば、年もしくは月の変更で、カレンダーを変更できるようになります。
カレンダーの更新をさらに簡単にするために、「翌月」「先月」「今月」のボタンで更新できるようにします。
「翌月」「先月」「今月」のボタンを作成します。
登録するVBAコードは、次のようになります。
↓「翌月」に更新するVBAコードです。
Sub NextMonth() Dim A '1か月進める A = DateAdd("m", 1, DateSerial(Range("A2"), Range("A3"), 1)) Range("A2") = Year(A) '年を取得 Range("A3") = Month(A) '月を取得 End Sub
↓「先月」に更新するVBAコードです。
Sub PreMonth() Dim A '1か月戻す A = DateAdd("m", -1, DateSerial(Range("A2"), Range("A3"), 1)) Range("A2") = Year(A) '年を取得 Range("A3") = Month(A) '月を取得 End Sub
↓「今月」に更新するVBAコードです。
Sub ThisMonth()
Range("A2") = Year(Now()) '今年を取得
Range("A3") = Month(Now()) '今月を取得
End Sub
これで、ボタンを使ってカレンダーを更新することができます。
ボタンでカレンダーを更新してみます。
「翌月」ボタンで、翌月にカレンダーを更新できます。
「先月」ボタンで、先月にカレンダーを更新できます。
「今月」ボタンで、今月にカレンダーを更新できます。
ボタンでカレンダーを更新できると便利です。
カレンダーに、データを取得する機能を追加すると便利になります。
データベースに登録した値を、カレンダーに反映する機能を追加してみます。
こんな感じで、データベースからカレンダーに値を取得できます。
データベースからカレンダーに値を取得できるようになります。
データベースからカレンダーに、データを取得するVBAコードになります。
手順は、
という感じです。
データベースからカレンダーにデータを取得するVBAコードです。
Sub GetData()
Dim A, B
With Sheets("月")
A = DateSerial(.Range("A2"), .Range("A3"), 1) '指定月の1日
B = DateSerial(.Range("A2"), .Range("A3") + 1, 0) '指定月の月末
End With
With Sheets("DB")
'指定月をフィルタ
.Range("A1").AutoFilter 1, ">=" & A, xlAnd, "<=" & B
.Range("A1").CurrentRegion.Copy .Range("D1") 'フィルタ結果をコピー
.Range("A1").AutoFilter 'フィルタ解除
End With
For i = 2 To Sheets("DB").Cells(Rows.Count, "D").End(xlUp).Row
With Sheets("月").Range("A4")
'データを取得
.Offset(2, Day(Sheets("DB").Cells(i, "D"))) = Sheets("DB").Cells(i, "E")
End With
Next
'コピー結果をクリア
Sheets("DB").Range("E1").CurrentRegion.ClearContents
End Sub
最初に、データベースのシートを作成して、データを入力しておきます。
データベースのシートを作成します。
データを入力しておきます。
では、VBAコードを実行してみます。
実行すると、指定した月でフィルタされます。
フィルタ結果を別セルにコピーします。
フィルタを解除します。
これで、指定した年と月のデータを抽出できます。
次は、抽出した日付をループして値を取得していきます。
抽出した日付をループしていきます。
日付からセル位置を取得して、登録データを取得します。
カレンダーに登録データを取得できました。
コピーした値は不要なので、クリアされます。
これで、データベースからカレンダーに値を取得できます。
データベースからカレンダーに値を取得できました。
データベースから値を取得するVBAコードを、年と月を変更で実行するできるようにします。
先ほど作成したChangeイベントに「Call GetData」を追加します。
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Range("A2:A3"), Target) Is Nothing Then Exit Sub
Call MakeCal 'カレンダーを作成
Call SetFormat '書式を設定
Call GetHoliday '祝日を反映
Call GetData 'データを取得
End Sub
カレンダーを更新すると、データベースから自動で値を取得できます。
カレンダーの更新で、データベースから自動で値を取得できました。
さらに、データベースに「書き込み」する機能を追加します。
データベースから取得と、データベースに書き込みができると、カレンダーで予定表の管理ができるようになります。
データベースに登録されている値の変更や、データベースに新規で書き込みができる機能なります。
カレンダーの値を変更してみます。
書き込みボタンをクリックします。
データベースの値を変更できます。
こんな感じで、データベースの値を変更できます。
カレンダーに値を入力してみます。
書き込みボタンをクリックします。
データベースに新規で値を書き込みできます。
こんな感じで、新規でデータベースに値を書き込むこともできます。
書き込みする手順は、次のようになります。
という感じで、実行します。
VBAコードは、こんな感じになります。
Sub WriteData()
Dim A
For Each A In Sheets("月").Range("B4").Resize(, 31)
'日付をフィルタ
Sheets("DB").Range("A1").AutoFilter 1, Format(A, "yyyy/m/d")
'登録データあり
If Application.Subtotal(103, Sheets("DB").Range("A:A")) > 1 Then
'値を書き込み
Sheets("DB").Cells(Rows.Count, "A").End(xlUp).Offset(0, 1) = A.Offset(2, 0)
'登録データなし
Else
If A.Offset(2, 0) <> "" Then '値がある場合
'フィルタ解除
Sheets("DB").Range("A1").AutoFilter
With Sheets("DB").Cells(Rows.Count, "A").End(xlUp)
.Offset(1, 0) = A '日付を書き込み
.Offset(1, 1) = A.Offset(2, 0) '値を書き込み
End With
End If
End If
Next
'フィルタを解除
Sheets("DB").Range("A1").AutoFilter
End Sub
カレンダーの値を変更しておきます。
では、VBAコードを実行してみます。
カレンダーの日付をループしていきます。
データ登録ありの場合は、データベースに値を書き込みします。
カレンダーの日付で、データベースをフィルタします。
日付のデータが登録されている場合は、データを書き込みます。
データベースに書き込みができました。
データ未登録で入力なしは、なにもしないで、次の日付へ移動します。
次の日付でデータベースをフィルタします。
データベースが未登録で、カレンダーが入力なしの場合は、次の日付に移動します。
データベースは変更しないで、そのまま次のセルへ移動します。
データ未登録で入力ありの場合は、新規で書き込みをします。
カレンダーの日付でフィルタします。
データベースに未登録で、カレンダーに入力がある場合は、データベースに書き込みしていきます。
データベースのフィルタを解除します。
データベースに日付を登録します。
データベースにカレンダーの値を登録します。
データベースにカレンダーの値を、新規で登録できました。
すべて書き込んだら、データベースのフィルタを解除します。
これで、データベースに値を書き込みできます。
データベースに値を書き込みできました。
データベースに値を書き込む際は、ボタンを使うと便利です。
書き込みのボタンを作成します。
ボタンに登録するVBAコードは、先ほどご紹介した書き込みのVBAコードになります。
Sub WriteData()
Dim A
For Each A In Sheets("月").Range("B4").Resize(, 31)
'日付をフィルタ
Sheets("DB").Range("A1").AutoFilter 1, Format(A, "yyyy/m/d")
'登録データあり
If Application.Subtotal(103, Sheets("DB").Range("A:A")) > 1 Then
'値を書き込み
Sheets("DB").Cells(Rows.Count, "A").End(xlUp).Offset(0, 1) = A.Offset(2, 0)
'登録データなし
Else
If A.Offset(2, 0) <> "" Then '値がある場合
'フィルタ解除
Sheets("DB").Range("A1").AutoFilter
With Sheets("DB").Cells(Rows.Count, "A").End(xlUp)
.Offset(1, 0) = A '日付を書き込み
.Offset(1, 1) = A.Offset(2, 0) '値を書き込み
End With
End If
End If
Next
'フィルタを解除
Sheets("DB").Range("A1").AutoFilter
End Sub
では、ボタンでデータベースに書き込みをしてみます。
カレンダーに値を入力しておきます。
書き込みボタンをクリックします。
データベースに値を書き込みできます。
データベースに値を書き込みできました。
こんな感じで、ボタンを使うとデータの書き込みが簡単になります。
この記事では、横型の月間カレンダーを作成する方法について、ご紹介しました。
祝日のデータがあれば、祝日も反映することができます。
カレンダーとデータベースを組み合わせると、作成したカレンダーを使って、予定表の管理ができます。
カレンダーの作成と、データベースの取得と書き込みについて、マスターしていきましょう。
参考になればと思います。最後までご覧くださいまして、ありがとうございました。
関連する記事から探す
カテゴリから見たい項目を探すと便利ですよ。
↓キーワードを入力する