時給の計算その6です。月締めで作成した時給計算表シートのシート保存方法についてです。
会計月を判断して自動で保存します。
当月利用した勤務時間計算表のデータを保存し、翌月使用のための準備を行います。
エクセルVBAで作る時給計算表の作成の第7回目です。
今回は、月の締めを行った時給計算表を保存するVBAコードの組み立て方です。
こんにちは、じゅんぱ店長(@junpa33)です。
時給計算に関連する記事の一覧
無料DL!時給計算表エクセルソフト 個人別ブックで管理出来ます。
の記事で行うことが出来ます。
コンテンツ
時給計算表シートを月別に自動保存する
時給計算表を月締めした後は、別に年月名でシートを作りそちらに保存します。
年月名シートで保存した後は、元の時給計算表は、データをクリアして翌月の使用の準備をします。
月別に自動保存するVBAコードを組み立てる
それでは自動保存のVBAコードを組み立てて行きます。
パブリック変数と代入のプロシージャー
その前に、いつものパブリック変数と代入のプロシージャーを確認しておきます。
変数宣言の方法についてはこちらを参考にしてください。
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で記述しているコードです。
今回も再度記述する必要はありません。
自動保存するVBAコード
これからのVBAコードは、「Module3」に記述していきます。
年月を付けたシートで保存しますが、その年月シートが既に存在していた場合は、エラーストップしてしまいます。
そのエラーストップを回避するために、先に同一シート名が既に存在するかどうかをチェックします。
存在する場合は、プロシージャーを終了してしまうのではなくて、再保存しようとした日付(今日)の年月日を付加した名づけで一旦保存するようにします。
その後に、重複保存になった原因チェック(保存データ比較)が出来るようにしています。
このモジュールから使用する変数で、このモジュールで有効なものがあります。
宣言セクションで変数を宣言
変数MoShNは、保存する年月名に対応する変数です。
Option Explicit
Dim MoShN As String
プロシージャーで使用する変数宣言
このプロシージャーで使用する変数を宣言しています。
変数StD 、StM、StYはセルB3の日付データから取り出した日、月、年です。
変数SMHは、月の会計期間から修正された会計月です。
「For Each~Next」の使い方についてはこちらの記事を参考にしてください。
VBA 回数不定のループ処理はDo LoopとFor Each
「Callステートメント」の使い方についてはこちらの記事を参考にしてください。
Sub シート保存() Dim StD As Date, StM As Date, StY As Date, SMH As Date Dim MoSh As Worksheet Dim Sh As Worksheet Dim res As Boolean 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
会計期間に合わせた会計月を設定する
チェックポイント
セルB3が空欄になっているとプロシージャーが終了します。日付のデータが入力されていることが必須です。
初期設定項目で入力されている会計期間情報から、時給計算表の記入日付(セルB3)が会計月の何月になるかを判断します。
パターン分類
- パターン1
会計期間1日から30日の場合は、セルB3の記入月がそのまま会計月になります。 - パターン2
会計期間21日から20日の場合は、セルB3の記入日が5日であれば、記入月が会計月になります。 - パターン3
会計期間21日から20日の場合は、セルB3の記入日が25日であれば、記入月+1 が会計月になります。 - パターン4
その他の何かの場合です。
チェックポイント
変数SMHが「13月」になった場合は「1月」に変更し変数StYを+1 します。
'セルB3にデータが無い場合はデータ記述なしとして終了します If Range("B3") = "" Then Exit Sub '時給計算表の書き出し日付を調べます StD = Day(Worksheets(EmpName & "時給計算表").Range("B3")) StM = Month(Worksheets(EmpName & "時給計算表").Range("B3")) StY = Year(Worksheets(EmpName & "時給計算表").Range("B3")) '書き出し日付と会計期間(日)の対比をします '---------パターン1 If StD >= SD And StD <= ED Then SMH = StM '---------パターン2 ElseIf StD < SD And StD <= ED Then SMH = StM '---------パターン3 ElseIf StD >= SD And StD > ED Then SMH = StM + 1 '---------パターン4 Else MsgBox "会計期間(日)設定をチェックしてください。" End If Worksheets(EmpName & "時給計算表").Select '13月を1月とします If SMH = 13 Then SMH = 1 StY = StY + 1 End If
年月をシート名として保存する
変数StYとSMHのデータ型を変更してシート名を作ります。
既に同一シート名があるかどうかを調べ、
ある場合は、(今日)の日付を名前に付けて再保存
無い場合は、そのまま保存します。
'データ型を変更します(整数型に変更) MoShN = CInt(StY) & "-" & CInt(SMH) Worksheets(EmpName & "時給計算表").Copy After:=Worksheets(Worksheets.Count) 'シート名を付けます '----既にあるシート名かチェックします '---------変数を初期値にセットします res = False For Each MoSh In Worksheets If MoSh.Name = MoShN Then '---------既に保存したシートがある場合は再保存日(今日)の日付を付けて保存します res = True MsgBox "既に同月の集計表が存在します。" & vbCrLf & "仮保存しますので集計表を再チェックしてください。" ActiveSheet.Name = MoShN & "@" & Format(Date, "yyMMDD") Exit For End If Next '---------同名のシートが無い場合はその名前でシート保存します If res = False Then ActiveSheet.Name = MoShN End If End Sub
時給計算表シートを自動保存するまとめ
VBAコード全体はこのようになります。
会計月別に保存することで、給与受給者を個人別で、月別年別にデータ保存をすることが出来るようになりました。
重複保存の場合にも対応できるようにしましたが、1会計月1シートにして管理することが望ましいです。
重複した場合は内容をチェックして、1シートになるようにしておきましょう。
なお、当月保存後、翌月に勤務期間計算表を使用する時は、
「テンプレート作成」ボタンをクリックしデータの「消去確認」メッセージで「はい」をクリックすることで、
前月データをクリアしてください。
次回は、保存シートを印刷する場合のためのVBAコードを設定します。
このサイトでよく使うVBAのコードのトピックを纏めています。
今回の記事はここまでです。 最後までご覧いただき有難うございました。









