エクセルVBAで時給の計算をするその4です。
今回は勤務時間計算の方法と計算表への表示の方法についてです。
単なる数値計算とは違って、幾つか計算のポイントがありますので、それを紹介して行きます。
エクセルVBAで作る時給計算表の作成記事の4回目になります。
今回は、勤務時間計算を行い計算表に表示するVBAコードを組み立てていきます。
こんにちは、じゅんぱ店長(@junpa33)です。
時給計算に関連する記事の一覧
無料DL!時給計算表エクセルソフト 個人別ブックで管理出来ます。
の記事で行うことが出来ます。
コンテンツ
勤務時間計算の方法と計算表への表示の方法
今回の記事で紹介する内容は3.の部分です。
- 先回までの記事で、「(対象者)時給計算表」を作成しました。
・・・・・
・・・・・ - その表に、勤務時間を入力します。
・・・・・
・・・・・ - その入力データから実労時間と時間外時間(残業時間)を計算して表示します。
・・・・・
・・・・・
普通にエクセルは、時間についても四則演算で適切に計算を行ってくれます。
ただし、時間は単なる数値とは違いますので、入力データによっては計算時にエラーが発生します。
そのエラーに対しての対応コードをセットしておく必要があります。
勤務時間計算のVBAコードを組み立てる
これからのエクセルVBAコードは「Module2」に記述していきます。
パブリック変数と変数代入プロシージャーの確認
第1回目の記事で紹介した、宣言セクションでの変数宣言と、
変数に代入する値についてのプロシージャーを今回も確認しておきます。
Option Explicit
Public EmpName As String, 年月日 As String
Public OriBName As String
Public TCos, TwCos, TMal, KMal
Public SD As Date, ED As Date
Dim SaName As String, SaNamea As String
Sub 設定値代入() Dim MB As Long Worksheets("初期設定項目").Select 'Format,Year,Month関数を利用します 年月日 = Format(Date, "yymmdd") '会計期間をセット SD = Worksheets("初期設定項目").Range("D5").Value ED = Worksheets("初期設定項目").Range("F5").Value '対象者名を変数に代入します EmpName = Worksheets("初期設定項目").Range("C6").Value If EmpName = "" Then EmpName = 年月日 Range("C6") = 年月日 End If '基本時給をセット TCos = Worksheets("初期設定項目").Range("C7").Value '時間外割増率をセット TwCos = Worksheets("初期設定項目").Range("F7").Value / 100 '時間マルメをセット TMal = Worksheets("初期設定項目").Range("C8").Value '金額マルメをセット KMal = Worksheets("初期設定項目").Range("C9").Value End Sub
これはすでにModule1で記述しているコードです。
今回も再度記述する必要はありません。
時間計算の全VBAコード
VBAコード組み立てのポイントです。
- 計算するシートが存在するかを調べる
- 記入データ行数を調べます
- 24時以上の表記を出来るようにします
- 入力されたセル値が時刻ではない時エラー処理を行います
- 出社時刻と退社時刻を比較します
- 出社時刻と退社時刻の入力があるものだけ計算します
- 基準時間と実労時間が表示されているものだけ計算します
- エラー時のジャンプ先
Sub 時間計算() Dim Sh As Worksheet Dim res As Boolean Dim myRange As Range, r As Range Dim rV As Date Dim rR As Long, rC As Long Dim i As Long Dim B32V As String Call Module1.設定値代入 '計算するシートが存在するかを調べるーーーー① res = False For Each Sh In Worksheets If Sh.Name = EmpName & "時給計算表" Then res = True Worksheets(EmpName & "時給計算表").Select Exit For End If Next 'ない場合はプロシージャーを終了する If res = False Then MsgBox "まだ何も設定されていません。" Exit Sub End If '記入データ行数を調べますーーーー② B32V = Range("B32") Range("B32") = "" BRow = Cells(Rows.Count, 2).End(xlUp).Row '記入データが無い場合はプロシージャーを終了します If BRow <= 2 Then MsgBox "データが入力されていません。" Exit Sub End If Range("B32") = B32V Set myRange = Range("C3:H" & BRow) '24時以上の表記を出来るようにします ーーーー③ myRange.NumberFormatLocal = "[h]:mm;@" For Each r In myRange r.Select '入力されたセル値が時刻ではない時エラー処理を行います ーーーー④ On Error GoTo step_err rV = r.Value rR = r.Row rC = r.Column Next For i = 3 To BRow '出社時刻と退社時刻を比較します ーーーー⑤ If Cells(i, 4) > Cells(i, 5) Then Cells(i, 5).Select rR = Selection.Row rC = Selection.Column MsgBox "Cell( " & rR & " , " & rC & " )" & " で出社時刻前に退社しています。" Exit Sub End If '出社時刻と退社時刻の入力があるものだけ計算しますーーーー⑥ If Cells(i, 4) <> "" And Cells(i, 5) <> "" Then Cells(i, 7) = Cells(i, 5) - Cells(i, 4) - Cells(i, 6) End If '基準時間と実労時間が表示されているものだけ計算しますーーーー⑦ If Cells(i, 3) <> "" And Cells(i, 7) <> "" Then Cells(i, 8) = Cells(i, 7) - Cells(i, 3) Cells(i, 8).Select '時間外時間がマイナスの場合は空欄にします If Cells(i, 8) < 0 Then Cells(i, 8) = "" End If End If Next i With Range("C32:H32") .NumberFormatLocal = "[h]:mm;@" .Font.Bold = True End With Exit Sub 'エラー時のジャンプ先ーーーー⑧ step_err: rR = r.Row rC = r.Column MsgBox "Cell( " & rR & " , " & rC & " )" & " で時刻(分)表示が違います" End Sub
計算するシートが存在するかを調べる
時給計算表シートがある場合はコードをスルーします。
無い場合は時給計算表テンプレートが必要ですので、メッセージを表示してプロシージャーを終了します。
シートがあるか無いかはBoolean(ブール)型変数を使っています。
デフォルトの値は「False」です。
特に、このブール型変数を同じプロシージャー内で複数回使いたい時、安全のため変数をデフォルト値に戻してから使うようにしています。
記入データ行数を調べます
B列を使って、時給計算表のデータ記入行数を計ります。
計算表の32行目が合計欄になっているので、その行のセルに文字列(合計)が入っている場合がありますので、行数の計測が間違わないように、そのセル値を一旦クリアしています。
24時以上の表記を出来るようにします
時刻のセル表示形式を24時以上に表示されるように設定変更をしています。
入力されたセル値が時刻ではない時エラー処理を行います
時刻でないデータが入力されていた時は、エラーストップを回避するコードを設定します。
「On Error GoTo step_err」
出社時刻と退社時刻を比較します
退社時刻が出社時刻よりも早くなっている場合はメッセージを表示して、プロシージャーを終了します。
出社時刻と退社時刻の入力があるものだけ計算します
出社時刻と退社時刻が両方表示されている場合だけ時刻計算されます。
基準時間と実労時間が表示されているものだけ計算します
基準時間と実労時間が両方表示されている場合だけ時刻計算されます。
時間外時間がマイナスの場合は空欄にします。
エラー時のジャンプ先
エラー発生時に読み込まれるVBAコードです。
エラーのセル位置を表示して、プロシージャーを終了します。
勤務時間計算のVBAコード作成のまとめ
まず1点目
時給計算表にデータを入力する場合に、時間データとはエクセルが判別できないデータはエラーとなります。
エラーストップしないために回避コードを記述しておかないといけません。
2点目として
セルの表示形式を、「時刻」でセットしていると、24時以上は表示されません。(1日と何時何分になります。)
24時以上も表示できる表示形式に変更する必要があります。
時給計算をする時に都合が良いからです。
この二つの点は特に注意しておくことが吉になります。
次回は、日々の時給計算について紹介をしていきます。
短期間でエクセルVBAの独学習得を目指したいなら
エクセルVBAを独学する独習方法は、学習者それぞれ十人十色、多種多様と思われます。
けれども、
出来るだけ効率よく学習するためには、いくつかの大切なポイントがあります。
独学でもVBA習得の中級クラスに達するのはそんなに難しいことではありません。
先人が行った勉強方法をあなたがそのまま利用すればよいということです。
独習のための大切な7つのポイントは、上記記事にて解説しています。
独習によるVBA習得のキーワードは、
出来るだけ多くの実例に触れること!
です。
正直、VBAの学習について自分の周りの仕事(業務)からだけ実例を得るのでは効率良い習熟は無理です。
ハッキリ言って、
本当に短い期間でVBA習得を成功させたいなら、今使っている参考書が良書かどうかを判断し、新ツールとしてオンライン学習も取り入れて行うことが、
手っ取り早く短期間習得できるというのは間違いないでしょう。
このサイトでよく使うVBAのコードのトピックを纏めています。
今回の記事はここまでです。 最後までご覧いただき有難うございました。









