エクセルVBAでカレンダーシートに自動でタスクを表示する方法

みなさん、こんにちは！
フジタニ(@libartweb)です。

エクセルVBAとエクセル関数を組み合わせ、営業日を考慮したスケジュール表自動作成の方法をシリーズでお伝えしております。

前回の記事はこちら

エクセルVBAでカレンダーシートの土日祝日の行に背景色をつける方法

エクセルVBAで土日祝日を判定し、その行に背景色をつける方法をお伝えします。背景色をつけることでカレンダーに見た目のメリハリがつき見やすくなります。土日祝日を判定する関数とともに紹介します。

エクセルVBAで土日祝日を判定し、カレンダーの土日祝日行に色を付ける処理を紹介しました。

土曜日の行が水色、日曜日の行がオレンジ色となり、デザイン的にメリハリのあるカレンダーが完成しました。

今回はそのカレンダーに対して、タスクを自動的に表示させる方法をお伝えします。

前回までのおさらい
カレンダーにタスクを自動表示させる流れ
カレンダーにタスクを反映させるサンプルコード
1. 実行結果
これまで紹介したソースコードの全体
日付計算シートに定例タスクをどんどん追加しよう
まとめ
連載目次：エクセルVBAで営業日を考慮したスケジュール表を自動で作成する

前回までのおさらい

前回は③の「土日祝日を判定し、VBAでその行に色をつけたい。」を実現する方法を2記事に分けて説明しました。

今回はいよいよラスト！④の「各日付ごとのタスクをVBAで「日付計算」シートから取得して反映させたい。」を紹介します。

① シート名が「テンプレート」のままなのでVBAでシートをコピーして対象月の名称にしたい。例：「201811」
② A1セルに対象月の「開始日」をVBAで自動入力し、B列全体を対象月の日付として反映させたい。
③ 土日祝日を判定し、VBAでその行に色をつけたい。
④ 各日付ごとのタスクをVBAで「日付計算」シートから取得して反映させたい。　⇒今回紹介します。

カレンダーにタスクを自動表示させる流れ

連載の前半で「日付計算」という名前の、月間スケジュールリストシートを作成しました。

エクセル関数だけで営業日を考慮した月間スケジュールを作成する方法

エクセル関数だけで営業日を考慮し、月間スケジュールを作成する方法をお伝えします。想定されるよくある日付パターンを、VBAを使用せずエクセル関数だけで計算。スケジュールリストを自動で作成します。

この「日付計算」シートから予定を取得し、カレンダーにタスクとして反映します。

シート構成をおさらいすると以下の通りです。

ワークシート	オブジェクト名
テンプレート	wsTemplate
日付計算	wsDateList

処理の流れは、

①日付計算シートのD列の日付とカレンダーシートのB列の日付で比較処理を行う
②カレンダーシートの該当の日付に「処理名」を転記する

という流れです。

カレンダーにタスクを反映させるサンプルコード

今回のサンプルコードは以下の通りです。

'---------------------------------------------------------------------------------------
'機能：カレンダーシートにタスクを反映する
'引数：カレンダーシートのオブジェクト変数
'返り値：なし
'---------------------------------------------------------------------------------------
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

'---------------------------------------------------------------------------------------

'機能：カレンダーシートにタスクを反映する

'引数：カレンダーシートのオブジェクト変数

'返り値：なし

'---------------------------------------------------------------------------------------

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つずつ説明していきます！

For dateList_CntRow = 2 To dateList_MaxRow '日付計算シートを読み込む
    For calendar_CntRow = 1 To CALENDAR_MAXROW 'カレンダーシートを読み込む

1 2	For dateList_CntRow = 2 To dateList_MaxRow '日付計算シートを読み込む For calendar_CntRow = 1 To CALENDAR_MAXROW 'カレンダーシートを読み込む

日付計算シートの、D列の日付を読み込んでいきます。D列の日付を、カレンダーシートのB列にあるか？をループで一行ずつ探していきます。

            '日付計算シートの日付とカレンダーシートの日付を比較
            If wsDateList.Range("D" & dateList_CntRow) = wsSchedule.Range("B" & calendar_CntRow) Then

1 2	'日付計算シートの日付とカレンダーシートの日付を比較 If wsDateList.Range("D" & dateList_CntRow) = wsSchedule.Range("B" & calendar_CntRow) Then

日付計算シートのD列、カレンダーシートのB列の日付が一致するか？を確認しています。

                'カレンダーシートの一番右の列を取得
                calendar_MaxCol = wsSchedule.Cells(calendar_CntRow, wsSchedule.Columns.Count).End(xlToLeft).Column

1 2	'カレンダーシートの一番右の列を取得 calendar_MaxCol = wsSchedule.Cells(calendar_CntRow, wsSchedule.Columns.Count).End(xlToLeft).Column

カレンダーシートのD～I列にタスクを表示させますので、最終列を取得します。

例えば、1日に複数のタスクがある場合、D列に既にタスクが埋まっていたらその次の列である、E列を取得する必要があるからです。

最終列の取得方法の詳細は以下の記事をご覧ください。

【エクセルVBA】表の最終行・最終列を取得する方法のまとめ

今回は、表の最終行・最終列を取得する方法を紹介していきます。表の最終行・最終列を取得する方法は、表の範囲を取得する方法と同じように複数あります。それぞれの方法に特徴があり、表によって使い分ける必要があります。ここでは３つの方法を紹介します。

タスクの表示箇所は黄色の枠線の部分ですね。ここの最終列を取得しています。

                'カレンダシートの該当行にタスクを表示
                wsSchedule.Cells(calendar_CntRow, calendar_MaxCol + 1) = wsDateList.Range("A" & dateList_CntRow)

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つのプロシージャで使用しますので、

今回モジュールレベル定数としました。（モジュール内のすべてのプロシージャで使えるレベルの定数です）

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

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を組み合わせ、営業日を考慮したスケジュール表を自動作成する方法をお伝えします。実務で使いこなすことができればとても便利なツールとして活用できるはずです！