サイト内検索

出力する 別ブック

VBAで各メンバーの勤怠表を作成し各メンバー用フォルダへ配布

業務の勤怠表をExcelシートで入力している場合、新しい月がくると勤怠表の日付や曜日などをずらす作業が必要になります。その際、前月と新しい月の曜日や勤務時間などのデータ入力箇所がズレるため、その修正作業が手作業だとちょっと煩わしいですよね。

確かに大した作業時間ではないかもしれませんが、なぜかズレた土日の文字色を赤字にしたり、勤務時間などのデータを消したり、入力したりするのが煩わしく感じます。

本記事では、そんな煩わしい作業をVBAで自動作成し、さらにメンバー分の新しい勤務表も、メンバー別のフォルダへ自動配布するVBAサンプルコードをご紹介します。

まずは、本記事の内容が簡単に分かる約2分の動画を用意しましたのでよろしければ先にご覧ください。

VBAで勤怠表の自動更新・自動配布を実演しています

勤怠表ワークブックサンプルシート

毎月の勤務時間を集計する勤怠表のサンプルファイルと、今回は三人のメンバー分の勤怠表の配布先になるフォルダ構成をご紹介します。

勤怠表テンプレート

勤怠表のテンプレートです。表題、作業開始日、作業終了日、氏名の入力箇所がありますが、すべてVBAで自動入力します。またリスト形式の表の項目には、日付、曜日、作業の開始時間と終了時間、休憩時間、作業時間、作業内容、および備考があります。

勤怠表サンプルシート
勤怠表サンプルシート

これらの項目のうち、VBAで自動更新される情報は、日付、曜日、作業の開始時間と終了時間、休憩時間と作業時間(計算式)になります。

勤怠表設定シート

VBAコードを実行する前に、条件設定をするための設定シートになります。設定項目は、下図のとおり開始日、土曜以外休日、勤務開始時間、勤務終了時間、休憩時間です。

VBA実行前の設定シート
VBA実行前の設定シート

開始日

開始日は、テンプレートに転記する作業開始日と同じ日になります。(下図)

設定シートの開始日欄は、VBA実行後にテンプレートへ自動転記される
設定シートの開始日欄は、VBA実行後にテンプレートへ自動転記される

ちなみに、作業終了日については、設定された作業開始日からVBAで計算され自動入力されます。このアルゴリズムについては、のちほど詳しく解説します。

土日以外休日

祝日や会社の設立記念日による休業日など、土日以外の休暇日をリストアップしておく場所になります。下図には土日以外休日欄にリストアップされた2020/4/29の情報が、テンプレート側で表示された例も載せています。

祝日や会社の設立記念日による休業日などを設定。図の例は、2020/4/29(木)の祝日データがテンプレートに転記されたときの表示例
祝日や会社の設立記念日による休業日などを設定。図の例は、2020/4/29(木)の祝日データがテンプレートに転記されたときの表示例

VBAでは、曜日を判定するアルゴリズムはありますが、祝日をはじめ会社都合による独自の休業日などの休日を勤怠表へ反映するためには、これらの特別な休日を外からの情報としてVBA実行時に与える必要があります。

そこで、今回はこの設定シート上の土日以外休日欄へリストアップした日付を、VBA実行時の参照情報として与えています。なおVBAでどのように処理されるかのアルゴリズムについては、のちほど詳しく解説します。

勤務開始時間、勤務終了時間、休憩時間

勤怠表へ転記する時間関連の項目を設定する場所になります。ここで設定されたデータは、VBA実行時にテンプレート側の作業時間欄と休憩欄へ自動転記されます。

勤務時間と休憩時間を設定すると、VBA実行時にテンプレートへ自動転記される
勤務時間と休憩時間を設定すると、VBA実行時にテンプレートへ自動転記される

ちなみに、図の例のように土日祝日などの休日にはこれらの時間は自動転記されないアルゴリズムにしていますが、急遽休日出勤した場合でもこれらの時間を入力すれば、休業時間欄に入力されているワークシート関数により自動計算が行われ、作業時間が表示されます。

ダウンロード用勤怠表サンプルファイル

本記事でご紹介する内容のサンプルファイルをダウンロードできます。ダウンロード後に、のちほど紹介するVBAサンプルコードをコピペしてから事前準備を行い、VBAを実行するとメンバー別の勤務表があっという間に出来上がります。

宜しければぜひ試してみてくださいね。

勤怠表ワークブック格納フォルダ構成

VBAサンプルコードを実行する前に、以下のフォルダを準備しておく必要があります。VBA実行後、最終的には各メンバーの名前フォルダー(サブフォルダ)の中にVBAで自動作成された勤怠表が自動保管されます。

VBA実行前に事前準備しておくフォルダ構成
VBA実行前に事前準備しておくフォルダ構成

VBAで勤怠表の自動作成と配布をするサンプルコード

勤怠表を自動更新してから、各メンバーのフォルダへ自動配布するVBAサンプルコードのご紹介です。

 

のちほど詳しく解説しますが、VBAコード96行目のところで、親フォルダのパスを指定する箇所があります。ここはVBAが実行される環境に応じて変える必要がありますので、上のサンプルコードをそのままサンプルファイルにコピペしてもエラーになりますので注意してください。

VBAサンプルコード実行前準備と実行結果の確認

今回はVBA実行前に、それぞれの実行環境に応じてVBAコードを書き替える箇所が1つありますので、その準備方法を解説してから、実行結果を確認していきたいと思います。

VBAサンプルコードを実行する前の準備

実行前の準備として、下図のとおりVBAサンプルコードの96行目の親フォルダのフルパス情報(下線部)を実行環境に合わせて書き換える必要があります。

VBAコード96行目のフルパス情報を書き換える
VBAコード96行目のフルパス情報を書き換える

フルパス情報を調べて、書き換える方法については、下記Tipsをご参考ください。

Tips
フルパス情報を書き換える手順

VBAサンプルコードの実行結果

事前準備が終わりましたら、VBAを実行してみてください。下図のように各メンバー別のサブフォルダの中に、それぞれの名前入り勤怠表が格納されていると思います。

VBA実行後のメンバー別フォルダに、それぞれの勤怠表(Excelファイル)が自動配布された
VBA実行後のメンバー別フォルダに、それぞれの勤怠表(Excelファイル)が自動配布された

確認のため、佐藤メンバーの勤怠表ワークブックを開いてみます。

佐藤メンバーに自動配布された勤怠表
佐藤メンバーに自動配布された勤怠表

今回のサンプルは、設定シート上の開始日が2020/4/1でしたので2020/4/1~2020/4/30までの日付が転記されています。休日は朱書き表示されています。上部の作業終了日は、VBAコードの中で自動計算されて表示されています。

Excelシート3, 4列目の作業時間とExcelシート5列目の休憩時間は、設定シート上で入力した内容が転記されています。

またExcelシート6列目の作業時間(合計)については、ワークシート関数が入力されます。さらに、このワークシート関数により集計された各日の作業時間は、最下部の作業時間合計欄(Excelシート38行目)に、一か月の合計時間として集計されます。

最上部の表題は、作業開始日から月を読み取り表示させており、氏名欄はフォルダ名を読み取った結果を転記させています。

VBAで勤怠表の自動作成と配布をさせるアルゴリズム

VBAサンプルコードのアルゴリズムの詳しい解説をします。今回はメインプロシージャを含む3つのサブプロシージャと、1つのファンクションプロシージャがありますので、順番に解説していきたいと思います。

Tips
サブプロシージャやファンクションプロシージャを使う理由と詳しい使い方

具体的なVBAコードの解説の前に、サンプルファイルのワークシートをオブジェクト名で扱いますので、下図を参照してからVBAコードの解説を読み進めてください。

サンプルファイルの各シートのオブジェクト名(CodeName)
サンプルファイルの各シートのオブジェクト名(CodeName)

Tips
VBAコードの中で複数ワークシートをオブジェクト名で取り扱う方法

モジュール変数と「勤怠表配布」プロシージャ(メインプロシージャ)

今回モジュール変数を1つ設定しています。モジュール変数「intMnth」は、更新月情報を保持する変数です。このモジュール変数「intMnth」は、「勤怠表配布」プロシージャ内で呼び出されている2つのプロシージャ「新規テンプレート更新」および「メンバーフォルダへ配布」で使われますので、モジュール変数として設定しました。

モジュール変数とメインプロシージャ「勤怠表配布」
モジュール変数とメインプロシージャ「勤怠表配布」

また上図のとおり、メインプロシージャ「勤怠表配布」は、2つのプロシージャで構成されています。

Tips
モジュール変数とプロシージャ変数の使い分けのヒント

「新規テンプレート更新」プロシージャ

12行目:Dim datStrt As Date
13行目:Dim datEnd As Date

作業開始日データを保持する変数「datStrt」、および作業終了日データを保持する変数「datEnd」の変数宣言部です。ともに日付データ型になります。

16行目:Dim d As Long
処理中の日付データを一時的に保持する長整数型の変数宣言です。日付シリアル値を扱う場合、Integer型ではオーバーフローしてしまうことからLong型で変数を宣言します。

Tips
VBA集計業務で日付を扱うときのポイント

20行目:datStrt = ST.Cells(2, 1)
設定シートの開始日を変数「datStrt」にセットします。

21行目:intMnth = Month(datStrt) 
更新月データを保持するモジュール変数「intMnth」に、更新月をセットします。日付データから、月を抜き出す方法として、ここではVBA関数「Month」を使います。

「Month」関数は、引数に日付データを受取ることにより、その月を返す関数です。

VBAコード22行目では、作業終了日を算出したデータを変数「datEnd」にセットしています。ここで使われているDateSerial関数は、引数に年、月、日を渡すと日付シリアル値を返す関数です。

DateSerial関数は、年、月、日を引数として受け取り、日付シリアル値を返す関数
DateSerial関数は、年、月、日を引数として受け取り、日付シリアル値を返す関数

月末日を求める考え方ですが、まずDateSerial関数の月の引数として更新月の翌月の値を渡し、日の引数として1日を渡します。こうすることで、まず更新月の翌月1日の日付を求めます(下図青枠)。

月末日を算出する考え方
月末日を算出する考え方

つぎに、DateSerial関数で求めた翌月1日のデータから、さらに1を差し引く(図の緑枠)ことで、結果的に更新月の月末日(datEnd)が求まります。

24行目:d = datStrt
処理中の日付データを保持するための変数「d」に、処理開始日付、すなわち作業開始日データを、繰り返し処理の初期値としてセットします。

26行目:.Cells(1, 1) = Month(d) & "月 勤怠シート"
テンプレートの表題を入力しています。ここでもMonth関数を使い、更新月を求めています。VBAサンプルコードでは、Month(d)としていますが、Month(intMnth)でも同じ結果が得られます。

VBAコード29~32行目(下図)は、VBAにより自動更新されるデータ入力範囲の初期化をしています。

テンプレートシートの初期化処理
テンプレートシートの初期化処理

VBAコード30行目では、データのクリア処理。VBAコード31行目では、文字色を赤にして初期化しています。赤文字は土日祝日などの休日表示に使います。

VBAコード34, 35行目(下図)は、テンプレートの作業開始日欄と作業終了日欄に、それぞれのデータが格納された変数の値をセットしています。

テンプレートの作業開始日と作業終了日に入力
テンプレートの作業開始日と作業終了日に入力

VBAコード38~68行目は、テンプレートの日付や曜日、作業時間などの行データを順次繰り返し処理しながら入力するコードです。

まずVBAコード39行目では、その繰り返し処理をする対象日付の条件を限定しています。下図で説明しますと、If文(赤文字)の条件式を満たした時に、青網掛けの繰り返し処理内容が実行されます。

テンプレートの各日付行に対する繰り返し処理とIf文の関係
テンプレートの各日付行に対する繰り返し処理とIf文の関係

ここでIf文の条件式「d <= datEnd」は、処理中の日付データが保持されている変数「d」のシリアル値が、作業最終日を保持する変数「datEnd」のシリアル値以下の場合です。つまり、作業開始日が初期値としてセットされた変数「d」は、作業終了日のシリアル値まで繰り返し処理されるということです。

VBAコード40行目は、勤怠表テンプレートの日付欄(下図赤枠)に日付を入力する処理になります。

処理中の日付を保持する変数dの値が、勤怠表テンプレートの1列目の日付欄に入力される
処理中の日付を保持する変数dの値が、勤怠表テンプレートの1列目の日付欄に入力される

そして一連の繰り返し処理が終わったあと、VBAコード66行目で次の日付に処理を移すため変数dに +1 を加算(処理対象の日付を次の日にする)しています。

VBAコード43~51行目は、勤怠表テンプレートの2列目の曜日欄に、1列目の日付が何曜日にあたるかを計算し、入力しています。

日付の曜日を計算してから勤怠表テンプレートの2列目に結果を入力する処理
日付の曜日を計算してから勤怠表テンプレートの2列目に結果を入力する処理

ここでは日付データにMod演算子を使い曜日を求めています。この曜日を求めるアルゴリズムについては、下記記事で詳しく解説していますのでご参考ください。

Tips
日付シリアル値からMod演算子を使い曜日を求めるアルゴリズム

VBAコード54、55行目では、2つのIf文により処理される対象を限定しています。まず1つ目のIf文(下図朱書き)では、勤怠表テンプレートの2列目に入力された曜日が、土曜日でも日曜日でもない場合、すなわち月、火、水、木、金曜日の場合、If文以降の処理をするという意味になります。

このIf文内に使われている演算子「<>」は、数学などで使う「≠」(ノットイコール)と同義で、ここで使われるているようにVBAでの表記は「<>」と記述します。

2つのIf文の条件が満たされた場合に、網掛け部の処理が実行される
2つのIf文の条件が満たされた場合に、網掛け部の処理が実行される

さらに、2つ目のIf文(上図青字)では、ユーザ定義ファンクションプロシージャ「祝日チェック」の結果がFalse(= 祝日ではない)の場合、If文以降の処理を実行する意味になります。

以上、2つのIf文の条件を満たした日付の場合、上図の網掛け部の処理を実行します。

では実際にその網掛け部の処理内容について、次の図で解説します。勤怠表テンプレートの3~5列目のデータは、「設定シート」に入力してある勤務開始時間、勤務終了時間、休憩時間の3つです。

開始時間と終了時間、および休憩時間を転記する処理
開始時間と終了時間、および休憩時間を転記する処理

前の2つのIf文の条件を満たした稼働日についてのみ、これら3つのデータが入力されます。よって、休日の場合はこれらのデータは転記されず、ブランクのままです。

60行目:.Range(.Cells(r, 1), .Cells(r, 12)).Font.ColorIndex = 1 
処理中の行の1~12列までの範囲のセルの文字色を黒に変更しています。VBAコード31行目の初期化のところで、勤怠表テンプレートの行データを入力する範囲の文字色を赤で初期設定しました。

操業日は文字色を黒にしたいので、その処理をここで行っています。

64行目:.Cells(r, 6).FormulaR1C1 = "=IF(RC[-3]="""","""",RC[-2]-RC[-3]-RC[-1])"
勤怠表テンプレートの6列目のセルに数式を入力しています。

「FormulaR1C1」は、Rangeオブジェクトのプロパティで、セルに数式を入力する時に使います。使い方は、上記VBAコードのように数式を入力したいセルの「FormulaR1C1」プロパティに、数式を含めた文字列を指定するだけです。

では、そのFormulaR1C1プロパティで指定している文字列(If関数)について解説します。FormulaR1C1プロパティでは、数式を文字列で指定しますが、今回の数式は、下図の下線で示したIf文になります。

作業時間欄に計算式を入力するためのVBAコード
作業時間欄に計算式を入力するためのVBAコード

If文の中では3つのセルを参照しています。処理の内容は、まず開始時間のセルが""(ブランク)かどうかを判断します。もし開始時間の欄がブランクの場合は、作業時間のセルを""(ブランク)にします。反対にもし開始時間の欄が""(ブランク)以外の場合は、[終了時間] (緑枠セル)- [開始時間](青枠セル) - [休憩時間](赤枠セル)の式により算出された値を、作業時間のセルに表示します。

Tips
R1C1形式におけるセル番地の相対参照による指定方法

文字列の中に""(ブランク)を記述する方法

VBAコード64行目では、FormulaR1C1プロパティでIf関数全体を文字列として指定しています。そのIf文の条件式の中に、開始時間セルが""(ブランク)かどうか、あるいは作業時間欄に""(ブランク)を入力する記述をするために、If関数の中に""(ブランク)を記述する必要があります。

ところが、VBAの文字列は " (ダブルクォーテーション)でくくるという約束事があるため、文字列の中に""(ブランク)を記述したい場合、そのまま記述すると、「"」 が文字列をくくるためのダブルクォーテーションなのか、ブランクを記述するためのダブルクォーテーションなのか判別がつかなくなります。

このことを色付きの図で同じ説明をすると、下図のようになります。

文字列中に
文字列中に""(ブランク)を含める場合のVBAコード記述

つまり、一言でいうと「文字列の中に含めたい"(ダブルクォーテーション)は、その直前に1つ"(ダブルクォーテーション)をつけてやる」ということになります。

「祝日チェック」ファンクションプロシージャ

引数として渡された日付が、設定シートに設けた土日以外休日リストの中に記載されているいづれかの日付と一致した場合はTrueを、どれとも一致しなかった場合はFalseを返すファンクションプロシージャです。

74行目:Dim rngHDay As Range
設定シートの土日以外休日リストを、RangeオブジェクトとしてセットするためのRangeオブジェクト変数です。

77行目:Set rngHDay = .Range("C1").CurrentRegion
設定シートのC1セルのCurrentRegionプロパティで取得される範囲をオブジェクト変数にセットします。ここでいう範囲とは、下図赤枠の「土日以外休日」にリストアップされた日付データ範囲のことです。

設定シートの土日以外休日リスト
設定シートの土日以外休日リスト

Tips
CurrentRegionプロパティの使い方詳細

79行目:If rngHDay.Find(vDay, lookat:=xlWhole) Is Nothing Then
土日以外休日データ一覧を格納したrngHDayの中に、引数で渡された日付があるかどうかをFindメソッドの結果で判定しています。

引数で渡された日付が、土日以外休日の一覧になければVBAコード80行目が実行され関数の戻り値はFalseとなり、土日以外休日の一覧にあればVBAコード82行目が実行され戻り値はTrueで返されます。

Tips
VBA集計業務でのFindメソッドの使い方

「メンバーフォルダへ配布」プロシージャ

89行目:Dim objFSO As Object
File System Object(FSO)機能を使うためのオブジェクト変数です。FSOは、ファイルやフォルダを扱う際に便利なメソッドやプロパティを持つWindowsの機能です。

90行目:Dim objFldr As Object
VBAサンプルコード内でフォルダを扱うためのオブジェクト変数です。

92行目:Dim wbkTemp As Workbook
各メンバーの勤怠表テンプレートが格納された新規ブック用のワークブックオブジェクト変数です。

93行目:Dim strPath As String
親フォルダのフルパス情報を格納する変数宣言です。

96行目:strPath = "C:\・・・・・\勤怠表フォルダー"
各自の環境に応じて変更する箇所になります。この文字列型変数に、親フォルダのフルパス情報を入力します。

99行目:Set objFSO = CreateObject("Scripting.FileSystemObject")
FSO機能をオブジェクト変数にセットしています。こうすることで、FSOのフォルダやファイルに関するメソッドやプロパティを利用できるようになります。

101行目:objFSO.GetFolder(strPath) 
FSOのGetFolderメソッドに、取得したいフォルダのフルパス情報を与えることでフォルダオブジェクトとして取得できます。ここではフルパス情報に親フォルダのフルパス情報を渡していますので、親フォルダ「勤怠表フォルダー」が取得されます。

VBAコード102行目以降は、親フォルダ内のサブフォルダに対する繰り返し処理になります。下図は、そのサブフォルダをFor Each ~ Nextの中で処理するイメージをフォルダの絵と共に記してみました。

For Each ~ Next(青字)の中では、1つ1つのサブフォルダに対する処理が実行される
For Each ~ Next(青字)の中では、1つ1つのサブフォルダに対する処理が実行される

それでは、ここからFor Each ~ Nextの処理内容について解説します。

103行目:TM.Copy
更新された「テンプレート」シートを、新しいワークブックへコピーします。内容が新しい月に更新された「テンプレート」シートを、各メンバー別フォルダへ1ファイルずつ作成するための前準備です。

104行目:Set wbkTemp = ActiveWorkbook
VBAコード103行目で作成された新しいワークブックを、オブジェクト変数「wbkTemp」として管理します。

VBAコード105行目は、下図のように勤怠表テンプレートの氏名欄にメンバー名を入力します。VBAコード上では、objFldr.Nameの値を入力しています。このobjFldrは、処理中のフォルダになります。そのobjFldrのNameプロパティ、すなわちフォルダ名が、勤怠表テンプレートのCells(3, 11)に入力されることで、結果的に勤怠表テンプレートの氏名欄に各メンバーの名前が入力されます。

勤怠表テンプレートの氏名欄に各メンバーの名前が入力されるアルゴリズム
勤怠表テンプレートの氏名欄に各メンバーの名前が入力されるアルゴリズム

VBAコード108行目は、下図のように各メンバーの勤怠表テンプレート(Excelファイル)のファイル名を組み立てた上で、ワークブックオブジェクトのSaveAsメソッドにより名前をつけて保存処理をしています。

更新月とメンバー名を組み合わせたファイル名でファイル保存
更新月とメンバー名を組み合わせたファイル名でファイル保存

ワークブックオブジェクトのSaveAsメソッドにはフルパス情報を指定する必要がありますが、ここではobjFldrオブジェクトのPathプロパティでフルパス情報を与えています。

110行目:wbkTemp.Close 
各フォルダ最後の処理になります。ワークブックオブジェクトのCloseメソッドで処理中のメンバー用ワークブックを閉じます。

まとめ

VBAで勤怠表の日付・曜日や勤務時間帯などの情報を、更新月に合わせてリセットするサンプルコードをご紹介しました。さらに、親フォルダ配下にある氏名別フォルダ配下に、自動作成された勤怠表を自動配布するVBAコードについてもご紹介しました。

今回ご紹介したVBAコードや勤怠表テンプレートをアレンジすれば、これまで毎月手作業で更新していた勤怠表の更新を効率よく行うことができます。もし職場の各自が同じように勤怠表を手作業で直しているなら、一人の担当者がVBAを実行して自動配布までできますので、グループ全体としての業務効率化にも効果が期待できそうです。

[スポンサーリンク]



サイト内検索

-出力する, 別ブック

Copyright© 集計用VBAサンプルコードをやさしく学ぼう , 2020 All Rights Reserved.