こんにちは、技術部のKです。
作業開始日や見積工数を入れると作業開始予定日、作業終了予定日終了日を算出してくれるマクロを作成してみたので、軽く解説していきます。
入出力フォーム
コードを書く前に、入出力のフォームを決めました。
作業開始日と工数だけではなく、1日当たりの作業工数、祝日を入力出来るようにしています。
コードの内容
おまじない
Option Explicit
まずは、VBAでマクロを作成する時は必ず入力しているおまじないです。
変数の宣言を強制し、変数名に関するエラーを防ぐことが出来るようになります。
（学生の頃におまじないを書くようにと習ったのでそうしていましたが、理由は知らなかったです。）
変数宣言
Dim index, line As Long
'// スケジュールを設定する
Sub setSchedule()
Dim startDate As Date
Dim tempStartDate As Date
Dim tempEndDate As Date
Dim manPower, tempIndex, tempIndex2, tempLine, holidayCount As Long
Dim workPower, reviewPower, dividePower, remainderPower As Long
Dim startIndex, startLine, mergeLine As Long
Dim publicHoliday() As Date
Dim duplicateFlag As Boolean
スケジュールを設定するSubプロシージャ（setSchedule）を書いていきますが、 その前後に変数を沢山宣言しています。
setSchedule()の前には、どこでも使えるグローバル変数を、 setSchedule()の後には、どこでも使えるローカル変数を宣言しています。
複数まとめて宣言するのか1つずつ宣言するのかが適当だったり、名称もふさわしくないものもあるので、いつか改良したいです。
入力内容の取得、出力準備
Application.ScreenUpdating = False
'// 入力した内容を取得
startDate = WS算出シート.Cells(4, 4).Value
manPower = WS算出シート.Cells(5, 4).Value * 2
holidayCount = WS算出シート.Cells(7, 4).Value
'// 配列のサイズを決定
ReDim publicHoliday(holidayCount)
'// 祝日の配列を作成
For tempIndex = 1 To holidayCount
publicHoliday(tempIndex) = WS算出シート.Cells(7 + tempIndex, 4).Value
Next
'// 作業内容表の場所を見つける
index = tempIndex + 8
Do While WS算出シート.Cells(index, 3).Value <> "作業内容"
index = index + 1
Loop
'// 見つけたら行列の場所を指定、また表を作成する場所をキレイにする
line = 10
Range(Cells(index, line), Cells(100, 1000)).clear
startIndex = index + 1
index = index + 2
// 値初期化
tempLine = 10
startLine = 10
mergeLine = 10
remainderPower = 0
tempStartDate = startDate
duplicateFlag = False
冒頭の「Application.ScreenUpdating = False」は、画面更新を停止することを表しています。
今回のマクロではFor文を使用して、少しずつセルに値を入れていくものになっているので、画面更新を停止しないと処理が遅くなってしまいます。画面に何か出力するマクロを作成するのであれば、画面更新の停止は行ったほうが良いです。
（最後に画面更新を再開するのを忘れずに。）
その他の内容に関してはコメントに書いている通りです。 祝日に関して、現在の入力フォームには4つまでしか入力することはできませんが、 セルの挿入を行えば、10でも20でも祝日を設定することが出来ます。
（入力フォームで説明しているところはないので、改善が必要なところです。）
作業工数の取得
'// 作業内容欄が空白になるまでループ処理
Do While WS算出シート.Cells(index, 3).Value <> ""
WS算出シート.Cells(index, 6).Value = ""
WS算出シート.Cells(index, 7).Value = ""
'// 工数取得
workPower = WS算出シート.Cells(index, 4).Value * 2
reviewPower = WS算出シート.Cells(index, 5).Value * 2
'// 工数が0の場合は次の作業に移る
If workPower = 0 And reviewPower = 0 Then
GoTo continue
End If
ここからは作業内容欄に入力された工数から、作業開始、終了日時を算出するためにループ処理を行います。
まずは入力された工数を取得します。
2を掛けているのは、0.5単位で工数が入力されているかもしれないので、整数に変換するためです。
朱徳した工数が0（または空）の場合はループ処理をスキップさせます。スキップさせる場合には、GoToステートメントを使用しましょう。ここでは「GoTo continue」としているので、「continue」と書かれている場所にジャンプすることが出来ます。
（ループ処理の最後の方に出てきます。）
表の作成
'// 作業工数分セルを黄色に塗りつぶす
If workPower <> 0 Then
Range(Cells(index, line), Cells(index, line + workPower - 1)).Interior.Color = RGB(255, 255, 0)
End If
'// レビュー工数分セルを黄緑色に塗りつぶす
If reviewPower <> 0 Then
Range(Cells(index, line + workPower), Cells(index, line + (workPower + reviewPower) - 1)).Interior.Color = RGB(146, 208, 80)
End If
line = line + (workPower + reviewPower)
作業内容を入力した表の横に、工数が視覚化できる表（のようなもの）を作成したいので、取得した工数分、色を指定してセルを塗りつぶします。
セルの範囲指定を行う場合はRange()、変数で指定したい場合はCells()、変数で範囲指定したい場合は、両方使用しましょう。
作業日数の計算
'// 作業にかかる日数と余りの工数を計算する
dividePower = (workPower + reviewPower + remainderPower) \ manPower
remainderPower = (workPower + reviewPower + remainderPower) Mod manPower
If remainderPower <> 0 Then
dividePower = dividePower + 1
End If
作業日数と余りの工数を計算します。
「dividePower」は作業日数、「remainderPower」は余りの工数を表しています。工数を一日当たりの作業工数で割っていますが、割り切れなかった場合はもう一日必要なので、余りの工数が「0」以外であれば日数に1追加します。
計算が完了したら、作業開始予定日を設定します。
作業開始予定日、作業終了予定日の計算
'// 作業開始予定日を設定する
WS算出シート.Cells(index, 6).Value = tempStartDate
tempIndex = 0
tempEndDate = tempStartDate
'// 作業にかかる日数分ループする
Do While tempIndex <> dividePower
'// 日付を設定し、セルを結合する
If duplicateFlag = False Then
Cells(startIndex, mergeLine).Value = tempEndDate & "(" & WeekdayName(Weekday(tempEndDate), True) & ")"
Range(Cells(startIndex, mergeLine), Cells(startIndex, mergeLine + manPower - 1)).Merge
mergeLine = mergeLine + manPower
End If
'// 最後のループ以外は作業終了予定日を翌日にする
If tempIndex + 1 <> dividePower Then
tempEndDate = returnNextDate(tempEndDate, publicHoliday)
End If
duplicateFlag = False
tempIndex = tempIndex + 1
Loop
'// 作業終了予定日を設定する
WS算出シート.Cells(index, 7).Value = tempEndDate
'// 作業終了予定日を次の作業の開始予定日に設定する
tempStartDate = tempEndDate
'// 余り工数が0だった場合は作業開始予定日を翌日にする
If remainderPower = 0 Then
tempStartDate = returnNextDate(tempStartDate, publicHoliday)
Else
duplicateFlag = True
End If
continue:
index = index + 1
Loop
長いですがまとめて解説します。
まず最初に、作業開始予定日をセルに設定しています。
作業開始予定日はループ処理の最後に更新するため、 初回ループ時には入力フォームで設定した作業開始日、以降のループでは更新された作業開始予定日が設定されます。
次に作業終了予定日を作業開始予定日で初期化し、作業日数分のループ処理を行います。
最初は「tempEndDate」に作業開始予定日が入っており、ループ処理で作業日数を1日ずつ足して、最終的に作業終了予定日を算出するわけです。
ただし、作業開始予定日に対して単純に作業日数を足してしまうと、土日や祝日も作業日として換算してしまうため、 土日や祝日を飛ばしながら、作業終了予定日を求めましょう。
（その処理は「returnNextDate()」というFunctionで行っているので、中身は後ほど見ていきます。）
ループ処理が終わったら作業終了予定日をセルに設定し、次のループのために作業開始予定日を更新します。
余りの工数が0の場合は作業終了予定日の工数を使い切っているため、作業終了予定日の翌日を作業開始予定日設定します。
余りの工数が0でなければ、まだ工数が残っているため、作業終了予定日を作業開始予定日に設定します。
最後に、一連のループ処理はDo-Whileで行っているので、自分でインデックスをインクリメントする必要があります。
「作業工数の取得」で、取得工数が0の場合はループ処理をスキップしますが、 インデックスは更新する必要があるため、その前の「continue」にジャンプすることになります。
ここまでで、作業開始予定日と作業終了予定日の算出が完了しました！
表のデザイン変更
'// 表にするために太枠で囲ったり格子状態にする
Range(Cells(startIndex, startLine), Cells(index - 1, mergeLine - 1)).Borders.LineStyle = xlContinuous
Range(Cells(startIndex, startLine), Cells(startIndex, mergeLine - 1)).Borders.Weight = xlMedium
Range(Cells(startIndex, startLine), Cells(index - 1, mergeLine - 1)).BorderAround Weight:=xlMedium
'// 日付毎に太枠で囲う
tempLine = tempLine + manPower
Do While tempLine < mergeLine
Range(Cells(startIndex, startLine), Cells(index - 1, tempLine - 1)).BorderAround Weight:=xlMedium
tempLine = tempLine + manPower
Loop
この処理はおまけみたいなものです。
「表の作成」で工数が視覚化できる表（のようなもの）を作成したので、それを見やすくするために罫線で囲っています。
Application.ScreenUpdating = True
End Sub
最後に停止していた画面更新を再開させ、スケジュールを設定するSubプロシージャは終了です！
非常に長くなってしまいましたが、あともう少しだけお付き合いお願いします。
作業日に1日加算するFunction
'// 休日、祝日を除いた次の日を返す
Function returnNextDate(checkDate As Date, publicHoliday() As Date) As Date
Dim tempIndex, holidayCount As Long
Dim resultDate As Date
resultDate = checkDate
'// 1日加算する
resultDate = DateAdd("d", 1, resultDate)
'// 加算結果が土曜日だった場合は、月曜日にするために2日加算する
If Weekday(resultDate) = 7 Then
resultDate = DateAdd("d", 2, resultDate)
End If
tempIndex = 0
holidayCount = UBound(publicHoliday)
'// 祝日の数だけループする
Do While tempIndex <> holidayCount
tempIndex = tempIndex + 1
'// 加算結果が祝日だった場合は1日加算する
If resultDate = publicHoliday(tempIndex) Then
resultDate = DateAdd("d", 1, resultDate)
'// 加算結果が土曜日だった場合は、月曜日にするために2日加算する
If Weekday(resultDate) = 7 Then
resultDate = DateAdd("d", 2, resultDate)
End If
'// また最初から祝日か否かを判定するためにインデックスは初期化する
tempIndex = 0
End If
Loop
'// 加算結果を返却する
returnNextDate = resultDate
End Function
「作業開始予定日、作業終了予定日の計算」で使用した、土日、祝日を飛ばして1日加算するFunctionです。
内容としては単純で、下記のことを行っています。
① 1日加算する
② 加算結果が土曜日の場合は月曜日にするために、追加で2日加算する
③ 加算した結果が祝日の場合は、追加で1日加算する
④ ②に戻る
祝日は複数持つことが出来るので、ループ処理で確認し、加算結果が全祝日に当てはまらなくなったら加算結果を返却します。
これにて、コードの解説終了です！
実行
それでは実際に動かしてみます。
適当に作業内容と工数を設定してみましょう。
算出ボタンを押せばマクロが動くようにしたので、ボタンを押します。
作業開始予定日と終了予定日が算出されました。
おわりに
今回は工数から作業開始予定日と作業終了予定日を算出するマクロについて、簡単に解説をさせて頂きましたが、 入力フォームもコードもまだまだ改善の余地があるので、また時間がある時に改善・改良やリファクタリングを行っていきたいです。
非常に長くなってしまいましたが、最後までお付き合い頂きありがとうございました。
関連記事
- 2023-12-07
- テクノロジー