勤務時間計算の方法と計算表への表示の方法。時給の計算その4

vbajikyukeisaneyecatch004

エクセルVBAで時給の計算をするその4です。

今回は勤務時間計算の方法と計算表への表示の方法についてです。
単なる数値計算とは違って、幾つか計算のポイントがありますので、それを紹介して行きます。

エクセルVBAで作る時給計算表の作成記事の4回目になります。

今回は、勤務時間計算を行い計算表に表示するVBAコードを組み立てていきます。

こんにちは、じゅんぱ店長（@junpa33）です。

 

時給計算に関連する記事の一覧

「時給計算表作成」の記事一覧を開く

 

無料ソフトダウンロード
この時給計算表エクセルソフトの無料のダウンロードは、

vbajikyukeisaneyecatch010無料DL!時給計算表エクセルソフト 個人別ブックで管理出来ます。

の記事で行うことが出来ます。

 

勤務時間計算の方法と計算表への表示の方法

 

今回の記事で紹介する内容は３．の部分です。

  1. 先回までの記事で、「（対象者）時給計算表」を作成しました。
    ・・・・・
    vbajikyuhyoupho007
    ・・・・・
  2. その表に、勤務時間を入力します。
    ・・・・・
    vbajikyuhyoupho031
    ・・・・・
  3. その入力データから実労時間と時間外時間（残業時間）を計算して表示します。
    ・・・・・
    vbajikyuhyoupho009
    ・・・・・

普通にエクセルは、時間についても四則演算で適切に計算を行ってくれます。

ただし、時間は単なる数値とは違いますので、入力データによっては計算時にエラーが発生します。

そのエラーに対しての対応コードをセットしておく必要があります。

 

目次へ戻る

勤務時間計算の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コード

VBAコード組み立てのポイントです。

  1. 計算するシートが存在するかを調べる
  2. 記入データ行数を調べます
  3. 24時以上の表記を出来るようにします
  4. 入力されたセル値が時刻ではない時エラー処理を行います
  5. 出社時刻と退社時刻を比較します
  6. 出社時刻と退社時刻の入力があるものだけ計算します
  7. 基準時間と実労時間が表示されているものだけ計算します
  8. エラー時のジャンプ先

 

全コード

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
MEMO

vbacalleyecatch部品化プロシージャーでCallステートメントは必須

vbamsgboxeyecatchメッセージボックス MsgBox実際の使い方を最速に理解

vbaifjyokeneyecatch「If条件文」のVBAコードの組み方。条件の絞り方を最速に理解。

 

計算するシートが存在するかを調べる

時給計算表シートがある場合はコードをスルーします。

無い場合は時給計算表テンプレートが必要ですので、メッセージを表示してプロシージャーを終了します。

シートがあるか無いかはBoolean（ブール）型変数を使っています。

デフォルトの値は「False」です。

特に、このブール型変数を同じプロシージャー内で複数回使いたい時、安全のため変数をデフォルト値に戻してから使うようにしています。

 

MEMO

「For Each～Next」の使い方についてはこちらの記事を参考にしてください。

vbadoloopeyecatchVBA 回数不定のループ処理はDo LoopとFor Each

 

記入データ行数を調べます

B列を使って、時給計算表のデータ記入行数を計ります。

計算表の32行目が合計欄になっているので、その行のセルに文字列（合計）が入っている場合がありますので、行数の計測が間違わないように、そのセル値を一旦クリアしています。

 

MEMO

入力セルの最終行を取得する方法はこちらを参考にしてください。

vbalastcelleyecatchデータ入力済セルの最終行番号を取得する

 

24時以上の表記を出来るようにします

時刻のセル表示形式を24時以上に表示されるように設定変更をしています。

 

MEMO

セルの表示形式についてはこちらを参考にしてください。

vbacellssyoshikieyecatch「表示形式」をVBAコード的に最速理解する

 

入力されたセル値が時刻ではない時エラー処理を行います

時刻でないデータが入力されていた時は、エラーストップを回避するコードを設定します。

「On Error GoTo step_err」

vbajikyuhyoupho034

 

MEMO

Gotoステートメントの使い方についてはこちらを参考にしてください。

VBAGotoeyecatch001Gotoステートメントはコードをジャンプする！毒と薬との両面がある

 

出社時刻と退社時刻を比較します

退社時刻が出社時刻よりも早くなっている場合はメッセージを表示して、プロシージャーを終了します。

vbajikyuhyoupho035

 

出社時刻と退社時刻の入力があるものだけ計算します

出社時刻と退社時刻が両方表示されている場合だけ時刻計算されます。

 

基準時間と実労時間が表示されているものだけ計算します

基準時間と実労時間が両方表示されている場合だけ時刻計算されます。

時間外時間がマイナスの場合は空欄にします。

vbajikyuhyoupho036

 

エラー時のジャンプ先

エラー発生時に読み込まれるVBAコードです。

エラーのセル位置を表示して、プロシージャーを終了します。

 

目次へ戻る

勤務時間計算のVBAコード作成のまとめ

 

まず1点目

時給計算表にデータを入力する場合に、時間データとはエクセルが判別できないデータはエラーとなります。

エラーストップしないために回避コードを記述しておかないといけません。

2点目として

セルの表示形式を、「時刻」でセットしていると、24時以上は表示されません。（1日と何時何分になります。）

24時以上も表示できる表示形式に変更する必要があります。

時給計算をする時に都合が良いからです。

この二つの点は特に注意しておくことが吉になります。

 

次回は、日々の時給計算について紹介をしていきます。

 

目次へ戻る

短期間でエクセルVBAの独学習得を目指したいなら

 

エクセルVBAを独学する独習方法は、学習者それぞれ十人十色、多種多様と思われます。

けれども、

出来るだけ効率よく学習するためには、いくつかの大切なポイントがあります。

独学でもVBA習得の中級クラスに達するのはそんなに難しいことではありません。

先人が行った勉強方法をあなたがそのまま利用すればよいということです。


vbastudyeyecatch002

エクセルVBAを独学で習得する！ために大切な7つのポイントを解説します

独習のための大切な7つのポイントは、上記記事にて解説しています。

重要ワード

独習によるVBA習得のキーワードは、

出来るだけ多くの実例に触れること！

です。

正直、VBAの学習について自分の周りの仕事（業務）からだけ実例を得るのでは効率良い習熟は無理です。

ハッキリ言って、

本当に短い期間でVBA習得を成功させたいなら、今使っている参考書が良書かどうかを判断し、新ツールとしてオンライン学習も取り入れて行うことが、

手っ取り早く短期間習得できるというのは間違いないでしょう。

 

 

このサイトでよく使うVBAのコードのトピックを纏めています。

「エクセルVBA最速理解」の記事一覧を開く

 

今回の記事はここまでです。   最後までご覧いただき有難うございました。

 

vbajikyukeisaneyecatch010無料DL!時給計算表エクセルソフト 個人別ブックで管理出来ます。vbajikyukeisaneyecatch009コマンドボタンとプロシージャーとの紐づけ。時給の計算その9vbajikyukeisaneyecatch008ワークシート上のコンボボックスで印刷シートを指定する。時給の計算その8vbajikyukeisaneyecatch007時給計算表シートを月別に自動保存するVBAコード。時給の計算その７vbajikyukeisaneyecatch006合計支給金額決定のための日給合計とマルメ計算のVBAコード。時給の計算その6vbajikyukeisaneyecatch005勤務時間と時給金額で日給を計算するVBAコードを作成。時給の計算その5vbajikyukeisaneyecatch004勤務時間計算の方法と計算表への表示の方法。時給の計算その4vbajikyukeisaneyecatch003個人の時給計算表に金額計算テンプレートを作成。時給の計算その3vbajikyukeisaneyecatch002個人別時給計算表を自動で作成するVBA。時給の計算その2vbajikyukeisaneyecatch001エクセルVBAで時給計算表を作成。全工程と初回の初期設定項目入力シート

 

＜記事内容についての告知＞VBAコードの記述記事においては、その記述には細心の注意をしたつもりですが、掲載のVBAコードは動作を保証するものではりません。 あくまでVBAの情報の一例として掲載しています。 掲載のVBAコードのご使用は、自己責任でご判断ください。 万一データ破損等の損害が発生しても当方では責任は負いません。

 

目次へ戻る

前の記事

vbajikyukeisaneyecatch003
個人の時給計算表に金額計算テンプレートを作成。時給の計算その3

次の記事

勤務時間と時給金額で日給を計算するVBAコードを作成。時給の計…
vbajikyukeisaneyecatch005