以前に作業時間を計測できる ToDo リストの作成方法について記事を書きました。
こちらからダウンロードできるようにしました ↓
是非ご活用下さい。また、ご自分で活用しやすいようにアレンジして頂くのも良いと思います。
ご意見ありましたらご連絡頂けると嬉しいです(^^)
さて、この ToDo リストの作成にはマクロ(Excel VBA)を使っています。
今日は、上の記事の中でも載せた Excel VBA コードの内容を説明したいと思います(前の記事からずいぶんと間が空いてしまいました。。。)。
1、業務の作業時間を計測できる ToDo リストの仕組み
まずは、以前と同じ内容になりますが、 ToDo リストの仕組みについておさらいしておきますね。
作成した ToDo リストはこのようなものです↓
普通の ToDo リストと同じようにやるべき業務をリストアップします。
1~10 まで10個リストアップできるようにしてあります。
作業時間計測の基本操作
やるべき業務をリストアップしたら、これから実際にこれらの業務を開始することになります。
上記の通り、業務にかかった作業時間を計測できるようにしてあります。
例えば、これから1番の「○○社 消費税不課税、非課税出し」の業務を行うとした場合を考えてみます。
まず開始する業務の番号(1番)横のオプションボタンを選択し、ToDoリストの上にある「スタート」ボタンをクリックします。
※「スタート」ボタンにはマクロが組み込んであります。
すると、該当する業務の「状態」蘭が「計測中」と表示されるようになり、また表の外の同じ行に計測を開始した時間が表示されます。
これで、業務の作業時間の計測がスタートしました。
ここから先は、「○○社 消費税不課税、非課税出し」の業務を普通に行います。
そして、この業務が終わったら ToDo リストの上にある「ストップ」ボタンをクリックします。
※「ストップ」ボタンにはマクロが組み込んであります。
すると、状態蘭の「計測中」の表示が消えて空白になり、表の外には計測を終了した時間が表示されます。
また、「作業時間」蘭には作業にかかった時間(=計測を終了した時間-計測を開始した時間)が表示されます。
完全にその業務が完了した場合には、「状態」蘭に「完了」と入力します。
一度手を付けた業務が完了する前に、他の業務に移った場合
さて、一度ある業務を開始してから完了する前に、他の業務に移る場合があると思います。
例えば、番号1の「○○社 消費税不課税、非課税出し」の業務を開始してから途中で中断し、番号2の「○○社 消費税申告書作成」を開始して、また途中で中断し番号1の業務に戻るような場合です。
番号1 開始→中断→番号2 開始→ 中断→番号1 開始
そのような場合には、一旦最初の業務の作業時間計測を停止して、他の業務の作業時間計測に移ります。
「状態」欄が「計測中」になっている業務がある場合には、他の番号のオプションボタンを選択することができなくしてあります。
したがって、番号1の業務が「計測中」の場合には、番号2のオプションボタンを選択して作業時間の計測を開始することはできません。
番号2を選択できるようにするためには、必ず「ストップ」ボタンで番号1の作業時間計測を終了しておかなければなりません。
番号1の作業時間計測を終了したら、「作業時間」欄に番号1の業務に関して作業にかかった時間(=計測を終了した時間-計測を開始した時間)が表示されるので、この状態で番号2のオプションボタンを選択します。
そして、今度は番号2の業務開始と同時に「スタート」ボタンをクリックし作業時間の計測を開始します。
番号2の業務を中断したら、「ストップ」ボタンをクリックします。
先と同様に、番号2の「作業時間」欄には作業にかかった時間が表示されます。
次に引き続き、番号1の業務を再開する場合には、番号1のオプションボタンを選択して「スタート」ボタンをクリックし、作業時間を計測します。
終了(又は中断)したら「ストップ」ボタンを押します。
すると、先に計測した作業時間と今計測した作業時間の累積が「作業時間」欄に表示されます。
また、表の外には計測をスタートした時間とストップした時間が表示されますが、これは直前の計測のみ(作業1の2回目の計測であって最初の計測は関係ない)の表示なので、累積時間とは関係ありません。
*表の外の時間は自分自身のプログラミングの動作確認用ですので、普段見るべきところではありません。普段見るべきところはあくまで表の中です。
このようにして、一旦作業を中断して他の業務に移った後に再度中断していた作業を再開した場合にも、1つの業務にかかった時間の累積を把握できるようにしています。
全ての業務の作業時間の合計
なお、表の下側には全ての業務の作業時間の合計が出るようになっています。
合計の計算には SUM 関数を使っています(セルG15に、「=SUM(G5:G14)」と入力してある)。
「リセット」ボタンと「全てリセット」ボタン
オプションボタンで業務の番号を選択して「リセット」ボタンを押せば、その業務についての作業時間などの記録がクリアされます。
「状態」欄が「計測中」の状態でクリックしても、そうでない状態でクリックしても同様にクリアされます。状態欄が「完了」であるの場合も同じです。
この「リセット」ボタンを使う機会は滅多にありませんが。
また、「全てリセット」ボタンを押せば、全ての業務の全ての項目がクリアされます。
「ToDo」欄に記載したやるべき業務も含めて全てがクリアされるので、新しく ToDo リストを作成する時(一日の業務が始まった時)に使います。
2、プログラミングコード(VBA)と解説
Excel VBA によるプログラミングコードは、シートモジュールとブックモジュールの中に記述します。
・シートモジュールは「Sheet1(ToDoリスト)」
・ブックモジュールは「ThisWorkbook」
のように表記されています。
さて、全てのコードをモジュール別に示して内容を解説します。
シート上に配置したコマンドボタン、オプションボタンなどのオブジェクトを、デザインモードにした上でダブルクリックすると VBE のシートプロシージャが開きます。
コードは各オブジェクト(コマンドボタン、オプションボタン など)のイベントプロシージャの中に記述してあります。
CommandButton1 オブジェクト(「スタート」ボタン)の Click イベントプロシージャの中に書かれたコードであれば、CommandButton1 をクリックしたときに動作するマクロを構成していることになります。
画面上側で、該当するオブジェクト名とイベントプロシージャ名を選択して、コードを書き込みます。
コードを記述する際は、Private Sub プロシージャ内に記述することになります。
———————–
Private Sub CommandButton1_Click()
処理
End Sub
———————–
各ボタンをクリックした際に実行するイベントプロシージャはこのように↓なっています。
シートモジュール「Sheet1(ToDoリスト)」内のプログラミングコード
モジュール内変数の定義
冒頭部分にモジュール内変数(モジュール内のどのプロシージャでも共通に使える変数)を定義します。
モジュール内変数の定義
1 2 3 4 5 6 |
Dim i 'ToDoリストの番号' Dim Before_i '前に選択されていたToDoリストの業務番号' Dim Start_Flag(1 To 11) 'スタートボタンが押されているか判定するフラグ' Dim Start_Time(1 To 10) '計測開始時間' Dim End_Time(1 To 10) '計測終了時間' Dim Pass_Time(1 To 10) '経過時間' |
解説
これらの変数は、シートモジュール内のどのプロシージャの中でも使えるように、宣言セクション(モジュールの一番上)でモジュール内変数として定義したものです。
Dim Start_Flag(1 To 11)
は1~11まで11個の要素を持つ配列です。
Start_Flag は ToDo リストの項目1つ1つにつき、スタートボタンが押されて「計測中」になっているか否かを判定する変数です。
ToDo リストは1~10まで10項目があり
・番号i の項目を選択した状態で「スタート」ボタンを押すと Start_Flag(i) に「1」が代入され
・番号i の項目を選択した状態で「ストップ」「リセット」「全てリセット」のどれかのボタンを押すと Start_Flag(i) に「0」が代入されます。
Start_Flag(1) が1の場合、 番号1の項目が「計測中」であることを意味し
Start_Flag(1) が0の場合、 番号1の項目は「計測中」ではないことを意味します。
同様に
Start_Flag(2) が1の場合、 番号2の項目が「計測中」であることを意味し
Start_Flag(2) が0の場合、 番号2の項目は「計測中」ではないことを意味します。
他の番号3~10も同じです。
なお、スタートボタンを押したら番号1~10のどれか1項目の Start_Flag のみが「1」となり、他の項目の Start_Flag は「0」のままになるようにコードを組んでいます。
「スタート」ボタンクリックした際に動作するコード
CommandButton1_Click イベントプロシージャに記述します。
Private Sub CommandButton1_Click()
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
Private Sub CommandButton1_Click() 'スタートボタン' If Start_Flag(i) = 1 Then Exit Sub Start_Flag(i) = 1 End_Time(i) = 0 Pass_Time(i) = Cells(4 + i, "G") Cells(4 + i, "F") = "計測中" Before_i = i Start_Time(i) = Time 'システム時間を「計測開始時間」に設定' Cells(4 + i, "I") = Start_Time(i) End Sub |
解説
まず冒頭の
1 |
If Start_Flag(i) = 1 Then Exit Sub |
の部分では、番号i の項目が計測中であるか否かを示す Start_Flag(i) が「1」である場合、つまり計測中である場合にはこのプロシージャから出るように指示しています。
どれか番号を選択して、既に一回スタートボタンを押して「計測中」となっている状態で、再度スタートボタンを押した場合にこの指示が動きます。
新たに計測を開始するのではなく、これまで行っていた計測を継続するためにこのようにしています。
そして、新たに計測を開始した場合には以降の指示が動作します。
1 |
Start_Flag(i) = 1 |
上記コードによって、Start_Flag(i)が「1」となり「計測中」となったことを意味します。
1 |
End_Time(i) = 0 |
上記コードは、計測が終了した時間を示す変数 End_Time(i) にとりあえず 0 を代入しています。
End_Time(i) は「ストップ」ボタンが押された時に、計測が終了した時間が代入されます(後で説明します)。
1 |
Pass_Time(i) = Cells(4 + i, "G") |
上記コードの Pass_Time(i) は、番号i の項目について既に計測済みの時間を表す変数です。
作業を中断するために後に説明する「ストップ」ボタンを押すとG列に作業時間が表示されます。
他の作業に移った後に再度元々やっていた番号iの項目の作業を再開した際に、作業時間を累積で計算できるようにするために既に計測済みの時間が表示されるG列のセル「4 + i, “G”」の値を読み込んでいます。
1 |
Cells(4 + i, "F") = "計測中" |
状態を表す F列の表示を「計測中」にします。
1 |
Start_Time(i) = Time 'システム時間を「計測開始時間」に設定' |
Start_Time(i) は計測を開始した時間を表す変数で、Time 関数を用いてこの変数にシステム時間(PC 画面の右下に表示される時間)を代入します。
1 |
Cells(4 + i, "I") = Start_Time(i) |
セル「4 + i, “I”」に開始時間を表示させます。
「ストップ」ボタンクリックした際に動作するコード
CommandButton2_Click イベントプロシージャに記述します。
Private Sub CommandButton2_Click()
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
Private Sub CommandButton2_Click() 'ストップボタン' If Start_Flag(i) = 0 Then Exit Sub Start_Flag(i) = 0 End_Time(i) = Time 'システム時間を「計測終了時間」に設定' Pass_Time(i) = Pass_Time(i) + (End_Time(i) - Start_Time(i)) Cells(4 + i, "F") = "" Cells(4 + i, "G") = Pass_Time(i) Cells(4 + i, "J") = End_Time(i) End Sub |
解説
まず冒頭の
1 |
If Start_Flag(i) = 0 Then Exit Sub |
の部分では、番号i の項目が計測中であるか否かを示す Start_Flag(i) が「0」である場合、つまり計測中でない場合にはこのプロシージャから出るように指示しています。
まだ計測を開始していないか、一度「ストップ」「リセット」「全てリセット」のいずれかのボタンを押して計測を止めた状態で再度「ストップ」ボタンを押した場合にこの指示が動きます。
計測中ではないので「ストップ」ボタンを押しても何も起こらないようにするためにこのようにしています。
そして、計測を停止した場合には以降の指示が動作します。
1 |
Start_Flag(i) = 0 |
上記コードによって、Start_Flag(i)が「0」となり「計測中」ではなくなったことを意味します。
1 |
End_Time(i) = Time 'システム時間を「計測終了時間」に設定' |
上記コードは、計測が終了した時間を示す変数 End_Time(i) にシステム時間(PC 画面の右下に表示される時間)を代入しています。
1 |
Pass_Time(i) = Pass_Time(i) + (End_Time(i) - Start_Time(i)) |
上記コードの Pass_Time(i) は、番号i の項目について既に計測済みの時間を表す変数です。
「スタート」ボタンを押すと同時に読み込まれた計測済みの時間 Pass_Time(i) に、今回新たに計測した時間 (End_Time(i) – Start_Time(i)) をプラスして、新たな Pass_Time(i) を計算しています。
1 2 3 4 |
Cells(4 + i, "F") = "" Cells(4 + i, "G") = Pass_Time(i) Cells(4 + i, "J") = End_Time(i) |
ここでは、状態を表すセル「4 + i, “F”」を空白にし(「計測中」の表示を消す)、作業時間を表すセル「4 + i, “G”」に計測済みの時間 Pass_Time(i)を表示しています。また、セル「4 + i, “J”」に終了時間を表示させます。
「リセット」ボタンをクリックした際に動作するコード
CommandButton3_Click イベントプロシージャに記述します。
Private Sub CommandButton3_Click()
1 2 3 4 5 6 7 8 9 10 11 |
Private Sub CommandButton3_Click() 'リセットボタン' Start_Flag(i) = 0 Start_Time(i) = 0 End_Time(i) = 0 Pass_Time(i) = 0 Cells(4 + i, "F") = "" Cells(4 + i, "G") = "" Cells(4 + i, "I") = "" Cells(4 + i, "J") = "" End Sub |
解説
番号iの項目に関する全ての変数を0にしています。
・計測中か否かを表す Start_Flag(i)
・計測開始時間を表す Start_Time(i)
・計測終了時間を表す End_Time(i)
・計測済みの時間を表す Pass_Time(i)
また、ToDo リストの項目以外の全ての表示を空白にしています。
例えば、番号4のリセットをしたい場合はオプションボタン4を選択した状態で「リセット」ボタンを押せば良いです。
「全てリセット」ボタンをクリックした際に動作するコード
CommandButton4_Click イベントプロシージャに記述します。
Private Sub CommandButton4_Click()
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
Private Sub CommandButton4_Click() '全てリセットボタン' Dim all_reset all_reset = MsgBox("全てリセットする処理を行いますか?", vbYesNo) If all_reset = vbYes Then '「はい」を押した場合の処理' Start_Flag(i) = 0 Start_Time(i) = 0 End_Time(i) = 0 Pass_Time(i) = 0 Range("E5:J14").ClearContents Else Exit Sub End If End Sub |
解説
1 |
all_reset = MsgBox("全てリセットする処理を行いますか?", vbYesNo) |
上記コードで本当に全てをリセットするか否かを確認する「はい(Y)」「いいえ(N)」ボタン付きのメッセージボックスが表示されます。
1 2 3 4 5 6 7 8 9 10 11 12 |
If all_reset = vbYes Then '「はい」を押した場合の処理' Start_Flag(i) = 0 Start_Time(i) = 0 End_Time(i) = 0 Pass_Time(i) = 0 Range("E5:J14").ClearContents Else Exit Sub End If |
この部分では、「はい(Y)」を選択した場合に変数を0にして、項目も含めたすべての表示を空白にしています。
オプションボタン1(作業1を選択)をクリックした際に動作するコード
オプションボタンは1~10まであり、それぞれを選択(クリック)した場合に動作するコードがありますが、オプションボタン1に絞って説明します。
他のオプションボタン2~10もコードの意味は同様ですので、説明は割愛致します。
OptionButton1_Click イベントプロシージャに以下を記述します。
Private Sub OptionButton1_Click()
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
Private Sub OptionButton1_Click() 'ToDoリスト番号1 を押した場合' For i = 1 To 10 '計測中の場合に、他の ToDoリスト番号を選択できなくする処理' If Cells(4 + i, 6) = "計測中" Then Select Case i Case 1: Worksheets("ToDoリスト").OptionButton1.Value = True Case 2: Worksheets("ToDoリスト").OptionButton2.Value = True Case 3: Worksheets("ToDoリスト").OptionButton3.Value = True Case 4: Worksheets("ToDoリスト").OptionButton4.Value = True Case 5: Worksheets("ToDoリスト").OptionButton5.Value = True Case 6: Worksheets("ToDoリスト").OptionButton6.Value = True Case 7: Worksheets("ToDoリスト").OptionButton7.Value = True Case 8: Worksheets("ToDoリスト").OptionButton8.Value = True Case 9: Worksheets("ToDoリスト").OptionButton9.Value = True Case 10: Worksheets("ToDoリスト").OptionButton10.Value = True Case Else End Select Exit Sub Else End If Next i i = 1 End Sub |
解説
このコードは、ある番号の状態蘭に「計測中」と表示された状態では、他の番号のオプションボタンを選択できないようにするためのものです。
1 2 3 4 5 6 7 8 9 10 11 12 13 |
Select Case i Case 1: Worksheets("ToDoリスト").OptionButton1.Value = True Case 2: Worksheets("ToDoリスト").OptionButton2.Value = True Case 3: Worksheets("ToDoリスト").OptionButton3.Value = True Case 4: Worksheets("ToDoリスト").OptionButton4.Value = True Case 5: Worksheets("ToDoリスト").OptionButton5.Value = True Case 6: Worksheets("ToDoリスト").OptionButton6.Value = True Case 7: Worksheets("ToDoリスト").OptionButton7.Value = True Case 8: Worksheets("ToDoリスト").OptionButton8.Value = True Case 9: Worksheets("ToDoリスト").OptionButton9.Value = True Case 10: Worksheets("ToDoリスト").OptionButton10.Value = True Case Else End Select |
上記部分で、選択されているオプションボタン番号ごとに、再度そのオプションボタンを選択するように指示しています。
例えば、オプションボタン1を選択して計測している最中に、オプションボタン4を選択しても強制的に再度オプションボタン1を選択するようにしています。「計測中」でない場合にのみ以降のコードに移ります。
1 |
i = 1 |
上記部分で、何番目の項目が選択されたか分かるように i に値を入れています。
番号1 のオプションボタンが選択された場合は i = 1 です。
ブックモジュール「ThisWorkbook」内のプログラミングコード
ワークブックを開いた時と、閉じた時にそれぞれ動作するコードについて以下で説明します。
ワークブックを開いた際に動作するコード
Workbook_Open イベントプロシージャに記述します。
Private Sub Workbook_Open()
1 2 3 4 5 6 |
Private Sub Workbook_Open() Worksheets("ToDoリスト").OptionButton10.Value = True Worksheets("ToDoリスト").OptionButton1.Value = True End Sub |
解説
ワークブックを開いた際に、強制的にまずオプションボタン10を選択した後にオプションボタン1 を選択するようにしています。
強制的にオプションボタンを選択する理由は、変数i に値を持たせるためです。
変数i に何も値が入っておらず空白の状態だと、他のコード(シートモジュール中の)を実行中にエラーが発生していまうため、それを回避するためです。
また、なぜ一度オプションボタン10を選択した後にオプションボタン1 を選択するかについては、
オプションボタン1 を強制的に選択するようにするだけでは、前にオプションボタン1を選択した状態でファイルを閉じていた場合だと、今回開いた際に i に値が代入されない(空白)ことがあったためです(理由はよく分かっていません、、すみません)。
ワークブックを閉じる際に動作するコード
Workbook_Open イベントプロシージャに記述します。
Private Sub Workbook_BeforeClose(Cancel As Boolean)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
Private Sub Workbook_BeforeClose(Cancel As Boolean) For j = 1 To 10 If Cells(4 + j, "F") = "計測中" Then Cancel = True MsgBox "計測を終了(ストップ)してからファイルを閉じて下さい。" Exit Sub Else End If Next j End Sub |
解説
状態を表すF列に「計測中」の項目がある場合は、「ストップ」「リセット」「全てリセット」のいずれかを選択して計測を終了してからでないと、ワークブックを閉じることができないようにしています。
1 2 3 4 5 6 7 8 |
If Cells(4 + j, "F") = "計測中" Then Cancel = True MsgBox "計測を終了(ストップ)してからファイルを閉じて下さい。" Exit Sub Else End If |
上記部分のF列に「計測中」の表示がある場合には「Cancel = True」により、ワークブックを閉じる動作をキャンセルます。
また「MsgBox “計測を終了(ストップ)してからファイルを閉じて下さい。”」の部分でその旨を画面表示しています。
3、まとめ
以前に紹介した、作業時間を計測することができる ToDo リストの作り方について、プログラミングコードの解説をしました。
先回の記事はこちらです(冒頭にも示しましたが)↓
上記記事では、シート設計を中心に解説しています。
先にこの記事を読んでいただいてから今回の記事を読んで頂くと分かりやすいと思います。
なお、冒頭にも示しましたがこちらから ToDo リストをダウンロードできるようにしました ↓
是非ご活用下さい。また、ご自分で活用しやすいようにアレンジして頂くのも良いと思います。
ご意見ありましたらご連絡頂けると嬉しいです(^^)