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

vbajikyukeisaneyecatch003

エクセルVBAを使った時給の計算その3です。

生成した個人別の時給計算表エクセルに計算表テンプレートシートを作成します。
今回はテンプレート作成中心のVBAコードですので、サクッと説明していきます。

エクセルVBAで作る時給計算表の作成記事の3回目です。

今回は、個人別の時給計算表エクセルブックに金額計算表テンプレートを作成するVBAコードを組み立てます。

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

 

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

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

 

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

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

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

 

各個人名時給計算表.xlsmごとに金額計算表テンプレートを作る

 

先回に作成した個人別の時給計算表の「Sheet1」をリネームしてそこに金額計算表のテンプレートを作成します。

「Sheet1」をリネームした名前は「(対象者)時給計算表」となります。

vbajikyuhyoupho007

 

今回はテンプレート作成ということで、セルサイズや罫線引きに関するコードが中心になります。

が、最初に、シート名を設定する部分と、すでにあるテンプレートをリセットするコードは書き加えておきます。

 

金額計算表テンプレートを作成するコード

 

ここからは、VBEで「Module2」に記述していきます。

パブリック変数を再確認しておきます

先ず最初に先回の記事で宣言セクションで宣言した変数と、

変数に代入する値を規定するプロシージャーを確認しておきます。

 

MEMO

変数宣言の方法についてはこちらを参考にしてください。

vbasengeneyecatch宣言方法で変数の適用範囲を変える エクセルVBA

Format関数についてはこの記事を参考にしてください。

VBAFormateyecatchFormat関数を書式設定のテッパン関数にする!実務での書式と重要5例

 

宣言セクションコード

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で記述しているコードです。

再度Module2に記述する必要はありません。

テンプレートコード前半部分

 

コード組み立ての前半は、

  1. Module2で共通して使う変数の宣言
  2. このプロシージャーで使う変数の宣言
  3. 対象者が個人別時給計算表作成時のものと、同じかどうかの確認とその処理

の部分です。

コード前半部分

変数「BRow」はB列で何行目までデータが記述されているかを計るための変数です。

宣言セクションのコード

Option Explicit
Dim BRow As Long

 

マーカーとして保存してある個人別時給計算表作成時の「対象者」を「OldEmpName」という変数に代入します。

今現在の「対象者」と一致しない場合は、メッセージボックスでアラートを出し設定を戻すように促しプロシージャーを終了します。

VBAコード参考記事

ここの説明で出てくるVBAコードの参考にしていただける記事です。

前半コード

Sub テンプレート()

Dim Sh As Worksheet
Dim res As Boolean
Dim OldEmpName As String
Dim MGB As Long

'Module1で作成したマーカーを変数に代入します
OldEmpName = Worksheets("初期設定項目").Range("H5")

Call Module1.設定値代入

'今現在の対象者がマーカーと一致するかどうかで条件分岐します
If EmpName <> OldEmpName Then
    MsgBox "対象者1人に対して1ブックです。" & vbCrLf & "Bookを閉じて設定をやり直してください。" _
  & vbCrLf & "まだの場合は対象者計算Bookを作成してください。"
    Worksheets("初期設定項目").Range("C6") = OldEmpName
    Exit Sub
End If

 

 

テンプレートコード中盤部分

 

コード組み立ての中盤は、

  1. シート名が既に存在する場合とない場合の処理
  2. データだ既に記述されている場合の処理

の部分になります。

コード中盤部分

既に時給計算表シートが存在していればコードをスルー、無ければ「(対象者)時給計算表」シートを作成します。

 

MEMO

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

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

 

中盤1コード

'シート名を付けます
res = False

For Each Sh In Worksheets

'既にシート名が存在している場合
    If Sh.Name = EmpName & "時給計算表" Then
        res = True
        Exit For
    End If
Next

'シート名が存在しない場合
    If res = False Then
        Worksheets("Sheet1").Name = EmpName & "時給計算表"
    End If  

B3セルを基準としてデータが記述されていなければコードをスルー、

記述されている場合、メッセージボックスでデータを消去するかどうかを選択できるようにします。

 

MEMO

メッセージボックスについてはこの記事を参考にできます。

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

 

中盤2コード

Worksheets(EmpName & "時給計算表").Select

'すでにデータが記述されている場合
If Range("B3") <> "" Then

'メッセージボックスを表示します
    MGB = MsgBox("計算表にデータが存在します。" & vbCrLf & "クリアしますか?", _
            vbYesNo + vbExclamation, "消去確認")

'「はい」ボタンを選択
        If MGB = vbYes Then
            Range("A1:K34").ClearContents

'「いいえ」ボタンを選択
        ElseIf MGB = vbNo Then
            MsgBox "消去は行いません。"
        End If
End If

テンプレートシートが作成され、すでにデータが存在する場合

「テンプレート作成」ボタンを押すと、

vbajikyuhyoupho031

矢印下001

メッセージボックスで、データをクリアするかどうかの確認が表示されます。

vbajikyuhyoupho032

メッセージボックスで「はい」をクリックするとクリアされます。

メッセージボックスで「いいえ」をクリックすると、次のメッセージボックスで「消去は行いません。」の確認表示がありデータはそのまま残ります。

vbajikyuhyoupho033

 

 

テンプレートコード後半部分

 

コード組み立ての後半は、

テンプレートの表の枠組みに関するコードになります。

コード後半部分

セルサイズと罫線設定、見出し項目の表示設定など

 

MEMO

With~End Withの使い方については、この記事を参考にしてください。

vbawithstateeyecatchWith~End Withの使い方。VBAコードを簡潔に記述する

セルの罫線設定についてはこちらを参考にしてください。

vbacellsbordereyecatch「罫線」のVBAを最速理解

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

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

セルの文字配置についてはこちらを参考にしてください。

vbacellsplacementeyecatch「配置」を最速理解する エクセルVBA

セルのフォントについてはこちらを参考にしてください。

vbacellsfonteyecatch「フォント」の操作を最速理解する エクセルVBA

 

後半コード

'表のタイトルを記入します。
Worksheets(EmpName & "時給計算表").Select
    With Range("A1")
        .Value = EmpName & "時給計算表"
        .Font.Size = 18
        .HorizontalAlignment = xlCenter
    End With
    Range("A1:D1").Merge

With Worksheets(EmpName & "時給計算表")

'行列のセルサイズを設定します
    .Rows("1:2").RowHeight = 27
    .Rows("3:34").RowHeight = 16.7
    .Rows("33").RowHeight = 27
    .Columns("A").ColumnWidth = 5
    .Columns("B:K").ColumnWidth = 13
    
'それぞれの罫線を設定します
    With Range("B2:K32")
        .Borders.LineStyle = xlDot
        .Borders.Weight = xlHairline
        .BorderAround LineStyle:=xlContinuous
    End With
    
    .Range("B2:K2").Borders(xlEdgeBottom).LineStyle = xlContinuous
    .Range("B2:K2").Borders(xlEdgeBottom).Weight = xlThin
    .Range("B32:K32").Borders(xlEdgeTop).LineStyle = xlContinuous
    .Range("B32:K32").Borders(xlEdgeTop).Weight = xlThin
    .Range("B2:B32").Borders(xlEdgeRight).LineStyle = xlContinuous
    .Range("B2:B32").Borders(xlEdgeRight).Weight = xlThin
    .Range("I2:I32").BorderAround LineStyle:=xlContinuous
    .Range("F33:I34").BorderAround LineStyle:=xlContinuous
    .Range("F34:I34").Borders(xlEdgeTop).LineStyle = xlContinuous
    .Range("F34:I34").Borders(xlEdgeTop).Weight = xlHairline
    .Range("F33:H34").Borders(xlInsideVertical).LineStyle = xlContinuous
    .Range("F33:H34").Borders(xlInsideVertical).Weight = xlHairline
    .Range("H33:H34").Borders(xlEdgeRight).LineStyle = xlContinuous
    .Range("H33:H34").Borders(xlEdgeRight).Weight = xlThin

'見出し項目を記入します
    .Range("B2").Value = "日時"
    .Range("C2").Value = "基準時間"
    .Range("D2").Value = "出社時間"
    .Range("E2").Value = "退社時間"
    .Range("F2").Value = "休憩時間"
    .Range("G2").Value = "実労時間"
    .Range("H2").Value = "(内)時間外時間"
    .Range("I2").Value = "支給金額"
    .Range("J2").Value = "(内)規定時間金額"
    .Range("K2").Value = "(内)時間外金額"
    .Range("B32").Value = "合計"
    .Range("F33").Value = "金額マルメ"
    .Range("G33").Value = "実労時間マルメ"
    .Range("H33").Value = "時間外時間マルメ"
    .Range("I33").Value = "マルメ支給額"

    .Range("B2:K2").ShrinkToFit = True
    .Range("B33:K33").ShrinkToFit = True
    
    .Range("B3:B31").NumberFormatLocal = "yy/mm/dd"
    .Range("C3:H32").NumberFormatLocal = "[h]:mm;@"
    .Range("G34:H34").NumberFormatLocal = "[h]:mm;@"
    .Range("I3:K32").NumberFormatLocal = "\#,##0;[赤]\-#,##0"
    .Range("I34").NumberFormatLocal = "\#,##0;[赤]\-#,##0"
    .Range("B32:K32").Font.Bold = True
    With Range("F33:I34")
        .Font.Bold = True
        .Font.ColorIndex = 5
    End With

End With

End Sub

 

 

計算表テンプレートを作るのまとめ

 

基本、テンプレート表の作成のコードですので、そんなに難しく無かったと思います。

ただ、シート名を付けるところで、同名のシート名は付けれませんので、エラーストップ防止のための処理コードは必ず作成しておかないといけません。

 

この時給計算表テンプレートシートは、会計期間1か月間の使用を前提としています。

また後の記事で説明しますが、時給計算表テンプレートシートのデータ移動について、

1か月たてば、その内容を「月」シートにコピー移動させ、次月のためにテンプレートシートは一旦オールクリアさせることになります。

 

テンプレートシートのデータを一旦オールクリアさせるにはこの「テンプレート作成」ボタンを押して、

メッセージボックスの「計算表のデータ消去確認」を「はい」として行う事になります。

後の記事でまた説明させていただきます。

 

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

 

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

けれども、

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

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

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

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

重要ワード

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

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

です。

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

ハッキリ言って、

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

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

 

 

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

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

 

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

 

 

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