みなさん、こんにちは！
フジタニ(@libartweb)です。
エクセルVBAとエクセル関数を組み合わせ、営業日を考慮したスケジュール表自動作成の方法をシリーズでお伝えしております。
前回の記事はこちら
エクセルVBAで土日祝日を判定し、カレンダーの土日祝日行に色を付ける処理を紹介しました。
土曜日の行が水色、日曜日の行がオレンジ色となり、デザイン的にメリハリのあるカレンダーが完成しました。
今回はそのカレンダーに対して、タスクを自動的に表示させる方法をお伝えします。
前回までのおさらい
前回は③の「土日祝日を判定し、VBAでその行に色をつけたい。」を実現する方法を2記事に分けて説明しました。
今回はいよいよラスト！④の「各日付ごとのタスクをVBAで「日付計算」シートから取得して反映させたい。」を紹介します。
① シート名が「テンプレート」のままなのでVBAでシートをコピーして対象月の名称にしたい。例：「201811」
② A1セルに対象月の「開始日」をVBAで自動入力し、B列全体を対象月の日付として反映させたい。
③ 土日祝日を判定し、VBAでその行に色をつけたい。
④ 各日付ごとのタスクをVBAで「日付計算」シートから取得して反映させたい。 ⇒今回紹介します。
カレンダーにタスクを自動表示させる流れ
連載の前半で「日付計算」という名前の、月間スケジュールリストシートを作成しました。
この「日付計算」シートから予定を取得し、カレンダーにタスクとして反映します。
シート構成をおさらいすると以下の通りです。
| ワークシート | オブジェクト名 |
|---|---|
| テンプレート | wsTemplate |
| 日付計算 | wsDateList |
処理の流れは、
①日付計算シートのD列の日付とカレンダーシートのB列の日付で比較処理を行う
②カレンダーシートの該当の日付に「処理名」を転記する
という流れです。
カレンダーにタスクを反映させるサンプルコード
今回のサンプルコードは以下の通りです。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
'--------------------------------------------------------------------------------------- '機能：カレンダーシートにタスクを反映する '引数：カレンダーシートのオブジェクト変数 '返り値：なし '--------------------------------------------------------------------------------------- Private Sub taskOut(ByVal wsSchedule As Worksheet) Dim dateList_CntRow As Long '日付計算シートのループカウント用変数 Dim dateList_MaxRow As Long '日付計算シートの最終行 Dim calendar_CntRow As Long 'カレンダーシートのループカウント用変数 Dim calendar_MaxCol As Long 'カレンダーシートの一番右の列 Const CALENDAR_MAXROW As Long = 31 'カレンダーの最終行定数 dateList_MaxRow = wsDateList.Cells(wsDateList.Rows.Count, 4).End(xlUp).Row '日付計算シートのD列の最終行取得 For dateList_CntRow = 2 To dateList_MaxRow '日付計算シートを読み込む For calendar_CntRow = 1 To CALENDAR_MAXROW 'カレンダーシートを読み込む '日付計算シートの日付とカレンダーシートの日付を比較 If wsDateList.Range("D" & dateList_CntRow) = wsSchedule.Range("B" & calendar_CntRow) Then 'カレンダーシートの一番右の列を取得 calendar_MaxCol = wsSchedule.Cells(calendar_CntRow, wsSchedule.Columns.Count).End(xlToLeft).Column 'カレンダシートの該当行にタスクを表示 wsSchedule.Cells(calendar_CntRow, calendar_MaxCol + 1) = wsDateList.Range("A" & dateList_CntRow) End If Next calendar_CntRow Next dateList_CntRow End Sub |
では、1つずつ説明していきます！
|
1 2 |
For dateList_CntRow = 2 To dateList_MaxRow '日付計算シートを読み込む For calendar_CntRow = 1 To CALENDAR_MAXROW 'カレンダーシートを読み込む |
日付計算シートの、D列の日付を読み込んでいきます。D列の日付を、カレンダーシートのB列にあるか？をループで一行ずつ探していきます。
|
1 2 |
'日付計算シートの日付とカレンダーシートの日付を比較 If wsDateList.Range("D" & dateList_CntRow) = wsSchedule.Range("B" & calendar_CntRow) Then |
日付計算シートのD列、カレンダーシートのB列の日付が一致するか？を確認しています。
|
1 2 |
'カレンダーシートの一番右の列を取得 calendar_MaxCol = wsSchedule.Cells(calendar_CntRow, wsSchedule.Columns.Count).End(xlToLeft).Column |
カレンダーシートのD～I列にタスクを表示させますので、最終列を取得します。
例えば、1日に複数のタスクがある場合、D列に既にタスクが埋まっていたらその次の列である、E列を取得する必要があるからです。
最終列の取得方法の詳細は以下の記事をご覧ください。
タスクの表示箇所は黄色の枠線の部分ですね。ここの最終列を取得しています。
|
1 2 |
'カレンダシートの該当行にタスクを表示 wsSchedule.Cells(calendar_CntRow, calendar_MaxCol + 1) = wsDateList.Range("A" & dateList_CntRow) |
最終列を取得したら、そのセルに、カレンダーシートのA列の「処理名」を転記します。
実行結果
カレンダーに事前に登録しておいたタスクが反映されました！
エクセルVBAで最終列を取得しましたので、同じ日に複数のタスクがある場合も、しっかり表示されています。(10日の英会話教室、月例会議)
これまで紹介したソースコードの全体
では、これまでの記事で紹介したソースコード全体を見てみましょう。今回紹介したtaskOutプロシージャは、template_Copyプロシージャから呼び出すようにしました。
カレンダーの最終行を表す定数CALENDAR_MAXROWは、calendarSetプロシージャとtaskOutプロシージャの2つのプロシージャで使用しますので、
今回モジュールレベル定数としました。（モジュール内のすべてのプロシージャで使えるレベルの定数です）
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 |
Const CALENDAR_MAXROW As Long = 31 'カレンダーの最終行定数 '--------------------------------------------------------------------------------------- '機能：テンプレートシートをコピーし開始日を設定する '引数：なし '返り値：なし '--------------------------------------------------------------------------------------- Public Sub template_Copy() 'テンプレートシートをコピー wsTemplate.Copy after:=wsTemplate Dim strStartDate As String '開始日 Dim strSheetName As String 'シート名 strStartDate = wsDateList.Range("F5") '基準日の翌月(VBAを実行した日の翌月となる） strSheetName = Format(strStartDate, "yyyymm") '基準日の翌月をFormat関数で変換 ActiveSheet.Name = strSheetName 'シートの名前を変更 Dim wsSchedule As Worksheet Set wsSchedule = ThisWorkbook.Worksheets(strSheetName) 'コピーしたシートをオブジェクト変数に格納 'コピーしたシートの保護を解除 wsSchedule.Unprotect 'カレンダーの「開始日」に日付計算シートの「基準日の翌月」を反映 wsSchedule.Range("A1") = strStartDate Call calendarSet(wsSchedule) '==================================== '今回の記事で追加 '==================================== Call taskOut(wsSchedule) End Sub '--------------------------------------------------------------------------------------- '機能：カレンダーの土日祝日行に背景色をつける '引数：カレンダーシートのオブジェクト変数 '返り値：なし '--------------------------------------------------------------------------------------- Private Sub calendarSet(ByVal wsSchedule As Worksheet) Dim cntRow As Long 'カレンダー行カウント用変数 With wsSchedule For cntRow = 1 To CALENDAR_MAXROW '土曜日を判定しその行を水色にする '土曜日=vbSaturday If Weekday(.Range("B" & cntRow)) = vbSaturday Then .Range("B" & cntRow & ":I" & cntRow).Interior.ColorIndex = 8 '背景色に水色設定 End If '日曜日と祝日を判定しその行をオレンジ色にする '日曜日=vbSunday 祝日:isholiday=true If Weekday(.Range("B" & cntRow)) = vbSunday Or isHoliday(.Range("B" & cntRow)) = True Then .Range("B" & cntRow & ":I" & cntRow).Interior.ColorIndex = 46 '背景色にオレンジ色設定 End If Next cntRow End With End Sub '--------------------------------------------------------------------------------------- '機能：カレンダーシートにタスクを反映する '引数：カレンダーシートのオブジェクト変数 '返り値：なし '--------------------------------------------------------------------------------------- Private Sub taskOut(ByVal wsSchedule As Worksheet) Dim dateList_CntRow As Long '日付計算シートのループカウント用変数 Dim dateList_MaxRow As Long '日付計算シートの最終行 Dim calendar_CntRow As Long 'カレンダーシートのループカウント用変数 Dim calendar_MaxCol As Long 'カレンダーシートの一番右の列 dateList_MaxRow = wsDateList.Cells(wsDateList.Rows.Count, 4).End(xlUp).Row '日付計算シートのD列の最終行取得 For dateList_CntRow = 2 To dateList_MaxRow '日付計算シートを読み込む For calendar_CntRow = 1 To CALENDAR_MAXROW 'カレンダーシートを読み込む '日付計算シートの日付とカレンダーシートの日付を比較 If wsDateList.Range("D" & dateList_CntRow) = wsSchedule.Range("B" & calendar_CntRow) Then 'カレンダーシートの一番右の列を取得 calendar_MaxCol = wsSchedule.Cells(calendar_CntRow, wsSchedule.Columns.Count).End(xlToLeft).Column 'カレンダシートの該当行にタスクを表示 wsSchedule.Cells(calendar_CntRow, calendar_MaxCol + 1) = wsDateList.Range("A" & dateList_CntRow) End If Next calendar_CntRow Next dateList_CntRow End Sub '--------------------------------------------------------------------------------------- '機能：取得した引数と、祝日一覧を比較して一致した場合にTrueを返す '引数：祝日かどうか判定したい日付(文字列) '返り値：真偽値 '--------------------------------------------------------------------------------------- Private Function isHoliday(ByVal strDate As String) As Boolean Dim maxRow As Long '祝日一覧の最終行 Dim cntRow As Long 'ループカウント用変数 isHoliday = False '初期化 With wsDateList '日付計算シート maxRow = .Cells(.Rows.Count, 9).End(xlUp).Row '祝日一覧の最終行を取得する For cntRow = 3 To maxRow '祝日一覧を一行ずつ読み込むループ If Format(.Range("I" & cntRow), "yyyy/m/d") = Format(strDate, "yyyy/m/d") Then isHoliday = True '祝日一覧に引数strDateに一致する日付があればTrue Exit For '一致する日付があればループを抜ける End If Next cntRow End With End Function |
日付計算シートに定例タスクをどんどん追加しよう
今回は、説明のために、英会話教室、月例会議･･･など５パターンしか日付計算シートに登録しませんでしたが、
日付計算シートにタスクをたくさん追加していくことで、このツールの真価を発揮します。
ボタンを押すだけでカレンダーに翌月のスケジュールがドーンと表示される瞬間に感動するでしょう。
定例のタスクを抱えている方は日付計算シートを充実させましょう！
まとめ
以上、日付計算シートに登録されているタスク一覧を取得し、そのタスクをカレンダーシートに反映させる方法を紹介しました。
自動で翌月のタスクがカレンダーに表示されますので、日付計算シートを充実させることで、このツールを便利に活用できるはずです！
連載はこれで終了です。これまで紹介してきた内容は最もシンプルな内容ですので、使いやすいようにカスタマイズしながらご活用ください。
連載をご覧いただき、ありがとうございました！
ちなみに、紹介したツールは以下よりダウンロードいただけます。
学習の参考にご利用ください！
連載目次：エクセルVBAで営業日を考慮したスケジュール表を自動で作成する
WORKDAY関数とエクセルVBAを組み合わせ、営業日を考慮したスケジュール表を自動作成する方法をお伝えします。 実務で使いこなすことができればとても便利なツールとして活用できるはずです！- エクセルで営業日判定を実現するWORKDAY関数の使い方
- エクセルVBAとWORKDAY関数の組み合わせでスケジュール表を自動作成する最初の一歩
- エクセル関数だけで営業日を考慮した月間スケジュールを作成する方法
- エクセル関数でスケジュール自動表示のためのカレンダーを作成する方法
- エクセルVBAでテンプレートをコピーし翌月のカレンダーシートを生成する方法
- エクセルVBAでカレンダーシートに翌月の日付を反映させる方法
- エクセルVBAのFunctionプロシージャで祝日判定関数を自作する方法
- エクセルVBAでカレンダーシートの土日祝日の行に背景色をつける方法
- エクセルVBAでカレンダーシートに自動でタスクを表示する方法
投稿者プロフィール
- LibArt代表
-
手作業でやってるものは何でも自動化したくなる性格。毎日VBAで何でもかんでも自動化しています。
電話が大嫌いなのでビジネスチャットを布教したい！
海外へよく行きます。自転車が大好き。走れるエンジニアです！
コメント