エクセルマクロ・VBA達人養成塾 小川です。
キューバ旅行記、その89です。
バラデロから、ハバナに戻りました。
そして、ハバナに着くと、外は嵐の気配でした。
無事、雨が降り出す前に、「Hotel Sevilla」にチェックインしました。外を見ると、もう嵐の気配…。
ホテル裏庭のプールにあるデッキから、隣の建物を撮ってみた。
忙しく、何かをしている人がいる…。
洗濯物を取り込んでいました。
風に揺れる洗濯物と、向こうのあやしい天気を思わせられる天気。
そして、ますます暗雲立ちこめます。
受講生村瀬正昭さんからいただいた添削依頼の内容を、ご許可をもらったのでシェアしています。
今日は、何をしようとしているのか、ということを確認。
こんな出勤表があったときに↓
こんな集計をしよう、というものです。
社会保険労務士の資格を取ったばかりということで、自分の仕事に関係しそうなマクロを書いてみたとのことです。
どういう計算のルールになるのか等、要件については、前回の記事で紹介しました。
もし、まだ読まれていないようでしたら、読んでおいてください。
前回の記事「給与計算ソフトは結構市販されていますが、融通が利かない場合も – Excelマクロ・VBA」
で、いただいたプログラムの内容はこんな感じ↓でした。
Sub Monthly_total_salary() ‘
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
※便宜上、インデント幅を、4ポイント→2ポイントに変更しました。あとはもらった状態そのまま。
エクセルシート上の「今月の給与」ボタンを押すと、サブプロシージャ「Monthly_total_salary」が実行され、無事、目的を果たすことができます。
それだけでも十分すごいんですが。
あえて言うなら、どう直していこうかな、ということです。
このマクロ、いろいろサブプロシージャが入っていますが、終了する順番に並べていくと、以下の順序です。
そして、その他に、独立して以下があります。
sun_sut_red
今日は、まず、この「sun_sut_red」の解説から。
↑このマクロでおもしろいのは、For Each構文でコレクションを指定しているところ、「Selection」にしているところ。
エクセル上でセルA4～A33を選択して、それからこのマクロを実行しよう、ということでしょう。
Weekday関数は、引数として数値または文字列を受け取ると、その引数を日付として解釈できたときには、それが何曜日になるのかを、数値として教えてくれます。
特殊な指定をしなければ、日曜日が1, 月曜日が2, … という感じ。
で。
僕なら、このマクロ、まずはこう直したい↓
引数は、「r」ではなく、「r.Value」です。
理由は、毎度毎度紹介しますが、この記事で書いたとおり。
「セルの値」を指定するとき、.Value は省略してよいものか？ – Excelマクロ・VBA
日本語に鋭い人なら、
「セルが日曜日か調べる」
と言うのと
「セルの値が日曜日か調べる」
と言うのとでは、
厳密なところでニュアンスが違うのが分かるかと思います。
プログラムを書くということは手順書を書くということです。こういうところは、極力具体的な表現のほうが好ましいです。
あと、こういうところは、 Select Case を使うのもなかなかスマート。
以下の要領。
時として、If文よりもSelect Caseを使ったほうが、条件分岐を鮮やかに仕上げることができます。
さらに言えば、名前付き引数を使うと、もっとエレガント。
最後に、ちょっと遊びを。
いただいた例では、土日とも赤になっていましたが、もうちょい工夫して、土曜日なら、青色にしてみたいと思います。
最後に、上記のマクロを、If文に戻してみます。
見通し悪い、冴えない感じを味わって、上記のものと比較してみてください ヾ(´ー｀)ﾉ
お知らせ:
人気のセミナー2つを、久しぶりに開催します。受講受付開始しました。ふるってご参加ください☆
「親指シフト達人養成塾」 （あと3名）
「エクセルデータ分析7つの上級技」 （あと1名）
「エクセルデータ分析」は、ほとんどリピータの方だけで埋まってしまいまして、早くも、残席1つだけとなりました。
親指シフトも、あと3名くらいです。
