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

vbajikyukeisaneyecatch004

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

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

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

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

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

 

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

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

 

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

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

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

 

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

 

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

  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

 

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

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

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

シートがあるか無いかは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習得の中級クラスに達するのはそんなに難しいことではありません。

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

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

重要ワード

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

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

です。

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

ハッキリ言って、

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

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

 

 

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

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

 

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

 

 

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