エクセルVBAを使った時給の計算その3です。
生成した個人別の時給計算表エクセルに計算表テンプレートシートを作成します。
今回はテンプレート作成中心のVBAコードですので、サクッと説明していきます。
エクセルVBAで作る時給計算表の作成記事の3回目です。
今回は、個人別の時給計算表エクセルブックに金額計算表テンプレートを作成するVBAコードを組み立てます。
こんにちは、じゅんぱ店長(@junpa33)です。
時給計算に関連する記事の一覧
無料DL!時給計算表エクセルソフト 個人別ブックで管理出来ます。
の記事で行うことが出来ます。
コンテンツ
各個人名時給計算表.xlsmごとに金額計算表テンプレートを作る
先回に作成した個人別の時給計算表の「Sheet1」をリネームしてそこに金額計算表のテンプレートを作成します。
「Sheet1」をリネームした名前は「(対象者)時給計算表」となります。
今回はテンプレート作成ということで、セルサイズや罫線引きに関するコードが中心になります。
が、最初に、シート名を設定する部分と、すでにあるテンプレートをリセットするコードは書き加えておきます。
金額計算表テンプレートを作成するコード
ここからは、VBEで「Module2」に記述していきます。
パブリック変数を再確認しておきます
先ず最初に先回の記事で宣言セクションで宣言した変数と、
変数に代入する値を規定するプロシージャーを確認しておきます。
変数宣言の方法についてはこちらを参考にしてください。
Format関数についてはこの記事を参考にしてください。
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に記述する必要はありません。
テンプレートコード前半部分
コード組み立ての前半は、
- Module2で共通して使う変数の宣言
- このプロシージャーで使う変数の宣言
- 対象者が個人別時給計算表作成時のものと、同じかどうかの確認とその処理
の部分です。
変数「BRow」はB列で何行目までデータが記述されているかを計るための変数です。
Option Explicit
Dim BRow As Long
マーカーとして保存してある個人別時給計算表作成時の「対象者」を「OldEmpName」という変数に代入します。
今現在の「対象者」と一致しない場合は、メッセージボックスでアラートを出し設定を戻すように促しプロシージャーを終了します。
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
テンプレートコード中盤部分
コード組み立ての中盤は、
- シート名が既に存在する場合とない場合の処理
- データだ既に記述されている場合の処理
の部分になります。
既に時給計算表シートが存在していればコードをスルー、無ければ「(対象者)時給計算表」シートを作成します。
'シート名を付けます 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セルを基準としてデータが記述されていなければコードをスルー、
記述されている場合、メッセージボックスでデータを消去するかどうかを選択できるようにします。
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
テンプレートシートが作成され、すでにデータが存在する場合
「テンプレート作成」ボタンを押すと、
メッセージボックスで、データをクリアするかどうかの確認が表示されます。
メッセージボックスで「はい」をクリックするとクリアされます。
メッセージボックスで「いいえ」をクリックすると、次のメッセージボックスで「消去は行いません。」の確認表示がありデータはそのまま残ります。
テンプレートコード後半部分
コード組み立ての後半は、
テンプレートの表の枠組みに関するコードになります。
セルサイズと罫線設定、見出し項目の表示設定など
With~End Withの使い方については、この記事を参考にしてください。
With~End Withの使い方。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のコードのトピックを纏めています。
今回の記事はここまでです。 最後までご覧いただき有難うございました。









