時給計算表シートを月別に自動保存するVBAコード。時給の計算その7

vbajikyukeisaneyecatch007

時給の計算その6です。月締めで作成した時給計算表シートのシート保存方法についてです。

会計月を判断して自動で保存します。
当月利用した勤務時間計算表のデータを保存し、翌月使用のための準備を行います。

エクセルVBAで作る時給計算表の作成の第7回目です。

今回は、月の締めを行った時給計算表を保存するVBAコードの組み立て方です。

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

 

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

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

 

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

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

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

 

時給計算表シートを月別に自動保存する

 

時給計算表を月締めした後は、別に年月名でシートを作りそちらに保存します。

年月名シートで保存した後は、元の時給計算表は、データをクリアして翌月の使用の準備をします。

vbajikyuhyoupho041

 

月別に自動保存するVBAコードを組み立てる

 

それでは自動保存のVBAコードを組み立てて行きます。

 

パブリック変数と代入のプロシージャー

 

その前に、いつものパブリック変数と代入のプロシージャーを確認しておきます。

MEMO

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

変数の宣言方法で適用範囲をコントロールするVBA記述

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

Format関数を書式設定のテッパン関数にする!実務での書式と重要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で記述しているコードです。

今回も再度記述する必要はありません。

自動保存するVBAコード

 

これからのVBAコードは、「Module3」に記述していきます。

年月を付けたシートで保存しますが、その年月シートが既に存在していた場合は、エラーストップしてしまいます。

そのエラーストップを回避するために、先に同一シート名が既に存在するかどうかをチェックします。

存在する場合は、プロシージャーを終了してしまうのではなくて、再保存しようとした日付(今日)の年月日を付加した名づけで一旦保存するようにします。

その後に、重複保存になった原因チェック(保存データ比較)が出来るようにしています。

自動保存VBAコード

このモジュールから使用する変数で、このモジュールで有効なものがあります。

宣言セクションで変数を宣言

変数MoShNは、保存する年月名に対応する変数です。

宣言セクションコード
Option Explicit
Dim MoShN As String

プロシージャーで使用する変数宣言

このプロシージャーで使用する変数を宣言しています。

変数StD 、StM、StYはセルB3の日付データから取り出した日、月、年です。

変数SMHは、月の会計期間から修正された会計月です。

 

MEMO

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

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

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

Callステートメントはプロシージャーの部品化に必須項目 エクセルVBA最速理解

 

変数宣言コード部分
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
    その他の何かの場合です。

 

MEMO

If条件文についてはこの記事を参考にしてください。

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

 

チェックポイント

変数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コード全体はこのようになります。

vbajikyuhyoupho042a

 

 

会計月別に保存することで、給与受給者を個人別で、月別年別にデータ保存をすることが出来るようになりました。

重複保存の場合にも対応できるようにしましたが、1会計月1シートにして管理することが望ましいです。

重複した場合は内容をチェックして、1シートになるようにしておきましょう。

 

なお、当月保存後、翌月に勤務期間計算表を使用する時は、

「テンプレート作成」ボタンをクリックしデータの「消去確認」メッセージで「はい」をクリックすることで、

前月データをクリアしてください。

 

次回は、保存シートを印刷する場合のためのVBAコードを設定します。

 

 

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

「VBA最速理解」の記事一覧を開く

 

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

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