エクセルマクロ・VBA達人養成塾 小川です。
受講生村瀬正昭さんからいただいた添削依頼の内容を、ブログ上で添削しています。
テーマは、就業時間内、時間外、深夜割増での賃金を計算するマクロです。
この記事は、以下のシリーズの第7回（最終回）です。
第1回: 給与計算ソフトは結構市販されていますが、融通が利かない場合も – Excelマクロ・VBA
第2回: 添削事例 – 給与計算をするマクロ（その２） – Excelマクロ・VBA
第3回: 範囲を指定してデータを一括クリアする – 給与計算をするマクロ（その３） – Excelマクロ・VBA
第4回: 就業時間内、時間外、深夜割増での賃金を計算する（その４） – Excelマクロ・VBA
第5回: 就業時間内、時間外、深夜割増での合計労働時間を一発で計算する（その５） – Excelマクロ・VBA
第6回: 労働時間と時給の情報を使って支払い額を計算したい – Excelマクロ・VBA
今日は、最終回。
すべてのサブプロシージャを書き換えたので、あとは、全体の流れを整理したい。
とりあえず、もともといただいたものをまずは再掲。
Monthly_Totaltm
Dim d1 As Long
Dim h1 As Long
Dim m1 As Long
Dim d2 As Long
Dim h2 As Long
Dim m2 As Long
Dim d3 As Long
Dim h3 As Long
Dim m3 As Long
Dim Tt As Double
Dim Tt2 As Double
Dim Tt3 As Double
Tt = Range(“E35”).Value
Tt2 = Range(“F35”).Value
Tt3 = Range(“G35”).Value
d1 = Int(Tt) ‘就業時間合計が24時間超になるごとに1が加算される。
h1 = Hour(Tt) ‘就業時間合計を24で割った余りを示す。
m1 = Minute(Tt) ‘就業時間合計のうち、分単位のものを示す。
d2 = Int(Tt2) ‘就業時間合計が24時間超になるごとに1が加算される。
h2 = Hour(Tt2) ‘就業時間合計を24で割った余りを示す。
m2 = Minute(Tt2) ‘就業時間合計のうち、分単位のものを示す。
d3 = Int(Tt3) ‘就業時間合計が24時間超になるごとに1が加算される。
h3 = Hour(Tt3) ‘就業時間合計を24で割った余りを示す。
m3 = Minute(Tt3) ‘就業時間合計のうち、分単位のものを示す。
Range(“E36”).Value = Range(“E3”).Value * d1 * 24 + Range(“E3”).Value * h1 + Range(“E3”).Value * m1 / 60
Range(“F36”).Value = Range(“F3”).Value * d2 * 24 + Range(“F3”).Value * h2 + Range(“F3”).Value * m2 / 60
Range(“G36”).Value = Range(“G3”).Value * d3 * 24 + Range(“G3”).Value * h3 + Range(“G3”).Value * m3 / 60
With Range(“E37”)
.Value = Range(“E35”).Value + Range(“F35”).Value
.NumberFormatLocal = “[h]:mm”
End With
With Range(“E38”)
.Value = Range(“E36”).Value + Range(“F36”).Value + Range(“G36”).Value
.NumberFormatLocal = “#,##0_);[赤](#,##0)”
End With
End Sub
Sub Monthly_Totaltm()
Tm_enter
Dim t As Date
Dim t2 As Date
Dim t3 As Date
Dim cnt As Long
Dim cMax As Long
cMax = Range(“B65536”).End(xlUp).Row
For cnt = 4 To cMax
t = t + Range(“E” & cnt).Value
t2 = t2 + Range(“F” & cnt).Value
t3 = t3 + Range(“G” & cnt).Value
Next
With Range(“E35”)
.Value = t
.NumberFormatLocal = “[h]:mm”
End With
With Range(“F35”)
.Value = t2
.NumberFormatLocal = “[h]:mm”
End With
With Range(“G35”)
.Value = t3
.NumberFormatLocal = “[h]:mm”
End With
End Sub
Sub Tm_enter()
Time_Clearcontents
Dim cnt As Long
Dim cMax As Long
cMax = Range(“B65536”).End(xlUp).Row
Dim zikann As Date
For cnt = 0 To cMax – 3
If Range(“B4”).Offset(cnt).Value <> “” Then
zikann = Range(“C4”).Offset(cnt).Value – Range(“B4”).Offset(cnt).Value – Range(“D4”).Offset(cnt).Value
If Range(“C4”).Offset(cnt).Value >= #10:00:00 PM# Then
With Range(“E4”).Offset(cnt)
.Value = #8:00:00 AM#
.NumberFormatLocal = “[h]:mm”
End With
With Range(“F4”).Offset(cnt)
.Value = zikann – #8:00:00 AM#
.NumberFormatLocal = “[h]:mm”
End With
With Range(“G4”).Offset(cnt)
.Value = Range(“C4”).Offset(cnt).Value – #10:00:00 PM#
.NumberFormatLocal = “[h]:mm”
End With
Else
If zikann >= #8:00:00 AM# Then
With Range(“E4”).Offset(cnt)
.Value = #8:00:00 AM#
.NumberFormatLocal = “[h]:mm”
End With
With Range(“F4”).Offset(cnt)
.Value = zikann – #8:00:00 AM#
.NumberFormatLocal = “[h]:mm”
End With
Else
With Range(“E4”).Offset(cnt)
.Value = zikann
.NumberFormatLocal = “[h]:mm”
End With
End If
End If
End If
Next
End Sub
Sub Time_Clearcontents()
Range(“E4:G37”).Clearcontents
End Sub
Sub sun_sut_red()
Dim r As Range
For Each r In Selection
If Weekday(r) = 1 Or Weekday(r) = 7 Then
r.Interior.ColorIndex = 3
End If
Next
End Sub
各プロシージャについて、僕がリライトしたものは、以下。
ここで、僕がまずやりたいことは何かというと。
全体の流れに対する可読性を高めたいので、そこから。
今の状態だと、プログラム全体の流れは、
Aがはじまるや否や、Bがはじまる
Bがはじまるや否や、Cがはじまる
Cがはじまるや否や、Dがはじまる
Dが終わると、Cの続きがはじまる
Cが終わると、Bの続きがはじまる
Bが終わると、Bの続きがはじまる
Aが終わる
という流れになっていて分かりにくい。
全体を、以下のように修正する。
これで、格段にメンテナンスしやすくなった。
あとは、こまごま。
[1-1], [1-2] で同じプログラムで最終行を求めている。
[2]では、Activesheet.Rows.Countを使っているので、表記を統一しつつ、モジュールレベル変数にこの値を格納することにしよう。
（↑好みの範囲という気もするが）
Time_Clearcontentsは、実際に行っている処理の内容からして、もうこうなると名前が適切でない。
「Time_Shokika」とでもしてみる。
そして、そのTime_Shokikaについて言うと、そういえば、マクロでのセルの書式設定は一般に処理が重たいのだった。
すでに目的の状態になっているときには、このステップを飛ばすようにする。
つまり、以下。
詳しい説明はしないが、結論から言うと、こういうことをしたければ、実際にやりたい処理を、 If Not … Then の間にサンドイッチすればよい。
総じて、以下のようになった。
ただし…。
第2回: 添削事例 – 給与計算をするマクロ（その２） – Excelマクロ・VBAでも書いたが、このマクロは、「就業時間が8時間に満たず、かつ深夜割増がある場合は、時間外の表記がおかしくなる」というバグがあるので。
このままでは使わないように。
この問題の修正については、村瀬さんからの再度の添削依頼を待ちます ヾ(´ー｀)ﾉ
重たいマクロでのセルの書式設定を回避する方法をどうするか、というのが、今日の目新しいトピックだったかも。
この記事は、以下のシリーズの第7回（最終回）です。
第1回: 給与計算ソフトは結構市販されていますが、融通が利かない場合も – Excelマクロ・VBA
第2回: 添削事例 – 給与計算をするマクロ（その２） – Excelマクロ・VBA
第3回: 範囲を指定してデータを一括クリアする – 給与計算をするマクロ（その３） – Excelマクロ・VBA
第4回: 就業時間内、時間外、深夜割増での賃金を計算する（その４） – Excelマクロ・VBA
第5回: 就業時間内、時間外、深夜割増での合計労働時間を一発で計算する（その５） – Excelマクロ・VBA
第6回: 労働時間と時給の情報を使って支払い額を計算したい – Excelマクロ・VBA
お知らせ:
昨日まで、以下での「親指シフト達人養成塾」へのリンクが間違っていたようです..。すいません。(´・ω・`)
「親指シフト達人養成塾」 （あと1名）
「エクセルデータ分析7つの上級技」 （キャンセル待ち）
「エクセルデータ分析7つの上級技」、うかうかしていたら、定員オーバーになっていました。
現状、キャンセル待ちです。最新の情報はリンク先のページで記載していますので、ご確認ください。
●無料PDFレポート「誰もが知っているWindowsの、誰も知らない12の技」
http://www.exvba.com/freereport/index.php
●法人研修のお問い合わせはこちら
https://sv86.wadax.ne.jp/~exvba-com/closed/toiawase_houjin.php
●ジーザス小川の個人サイト「こねこねのさいと」へはこちらから
http://www.exvba.com/
