excel vba 時間計算と条件分岐の質問

質問

vbaにて勤怠表を作っているのですが、時刻と時間の区別はどのようにするのでしょうか。
初歩的な質問でしたら、どうかご容赦ください。

項目には出勤時間、休憩時間、退勤時間、勤務時間、他（省略します）があるのですが、
退勤時間から出勤時間を引いた時間をmyTimeとして、myTimeに応じて休憩時間の判定を行ないたいです。

myTimeが４時間未満で休憩時間０分
４時間以上６時間未満で休憩時間３０分
６時間以上８時間未満で休憩時間４５分
８時間以上で休憩時間は１時間
の時間を休憩時間セルに入力させたいのですが、myTimeが時刻から変えられず困っています。
恐らく、８時より前か後か、といったような判断をしているのだと思います。

ちなみにセルには別のExcel関数が入っていて、マクロを実行するときに関数を無視して（上書きして）入力させたいので、Excel関数での提案は今の所考えておりません。
コードの書き方を教えてくださいませんでしょうか。
現在の表記がh：mmになっている為、これで統一出来れば嬉しいです。
このあと勤務時間の集計も行なう為、時間の計算方法なども教えて頂けると助かります。
（一応自力で調べてみてはいるものの、まだ理解には及びません。）

Dim STAR_TIME As Date
Dim RECESS_TIME As Date
Dim myTime As Date

STAR_TIME = Cells(0, STAR_CL)　　　'出勤時間の行です
LAST_TIME = Cells(0, LAST_CL)　　　'退勤時間の行です
myTime = LAST_TIME - STAR_TIME

With Cells(0,RECESS_CL)　　'休憩時間の行です

If myTime >= Format(Time, "8:00") Then
.Value = Format(Time, "1:00")

ElseIf Format(Time, "8:00") > myTime And myTime >= Format(Time, "6:00") Then
.Value = Format(Time, "00:45")

ElseIf Format(Time, "6:00") > myTime And myTime >= Format(Time, "4:00") Then
.Value = Format(Time, "00:30")

ElseIf Format(Time, "4:00") >= myTime Then
.Value = Format(Time, "0:00")

End If

End With

記号（大なり、小なり）の挙動もいまいち理解出来ていません。
何度も試してみて今の位置にしてみたのですが、
いろいろ試して変えまくって、なんだかよく分からなくなってきました。正直合っている気はしないです。
実際にはもうすこし長いコードですが、必要そうな所だけ書き出しました。
私のミスで足りないところがあるかもしれません。必要であれば書き足します。

作成はExcel for mac 2011ですが、
実際に運用するのはWindowsの各Excelバージョン（複数）の為、
互換性のあるコードで教えてください。宜しくお願い致します。

回答No.1

cj_mover · ベストアンサー

こんにちは。

mac互換といわれると、最近の事情はまったく知らないのですが、
とりあえず３例挙げてみます。

ところで、投稿用に便宜的に書き換えた？のでしょうけれど、
> STAR_TIME = Cells(0, STAR_CL)　　　'出勤時間の行です
Cells の rowindex に 0 を指定するのは
（相対参照で書く場合以外は）失敗しますから、
便宜的に、変数 i に置き換えて提示します。

IF ... ElseIf ... Else ... End If
で書いていると後から見ても条件分岐が解り難そうですから、
一般的な、Select Case ... Case ... End Select
で書いてみます。

勤務時間と比較する時間値は、色々な書き方がありますが、
やはり読み易さ重視で、TimeValue()関数で書きます。

出力値は、"0:45"のように文字列の直値でもＯＫなので、
これも読み易いものを選びます。
時刻を出力するのにDate型を出力する場合は必ず
書式の表示形式を設定しておかなければなりませんから、
時刻値の出力は文字列値の方が却ってバリアントです。

ご提示された（抜粋された）記述を読む限りは、
With フレーズ を使う必然性が低いので、
こちらでは、新規の変数として、休憩時間を指す myRecess
に一旦格納してから、この変数値をセル出力するように
（他の記述での変数の扱い方に合わせるように）書いています。

とりあえず、普通な感じで。
' ' ///
Dim myRecess As Variant
Dim i As Long
' ...
　　STAR_TIME = Cells(i, STAR_CL)　'　出勤時間
　　LAST_TIME = Cells(i, LAST_CL)　'　退勤時間
　　myTime = LAST_TIME - STAR_TIME　'　休憩時間

　　Select Case myTime
　　Case Is >= TimeValue("8:00")　'　８ 時間以上で休憩時間は1時間
　　　　myRecess = "1:00"
　　Case Is >= TimeValue("6:00")　'　６ 時間以上8時間未満で休憩時間45分
　　　　myRecess = "0:45"
　　Case Is >= TimeValue("4:00")　'　４ 時間以上6時間未満で休憩時間30分
　　　　myRecess = "0:30"
　　Case Else　'　４ 時間未満で休憩時間０分
　　　　myRecess = "0:00" '
　　End Select

　　Cells(i, RECESS_CL) = myRecess
' ...
' ' ///

ここに書かれていない他の記述での条件さえ合えば、
Hour(myTime) 時刻値の"時"だけを比較する方法もあります。
別段コメントを付加しなくても意味が呑み込み易いという意味で
ビギナー向き、の書き方です。

' ' ///
Dim myRecess As Variant
Dim i As Long
' ...
　　STAR_TIME = Cells(i, STAR_CL)　'　出勤時間
　　LAST_TIME = Cells(i, LAST_CL)　'　退勤時間
　　myTime = LAST_TIME - STAR_TIME　'　休憩時間

　　Select Case Hour(myTime)
　　Case Is >= 8　'　８ 時間以上で休憩時間は1時間
　　　　myRecess = "1:00"
　　Case 6 To 8　'　６ 時間以上8時間未満で休憩時間45分
　　　　myRecess = "0:45"
　　Case 4 To 6　'　４ 時間以上6時間未満で休憩時間30分
　　　　myRecess = "0:30"
　　Case Else　'　４ 時間未満で休憩時間０分
　　　　myRecess = "0:00" '
　　End Select

　　Cells(i, RECESS_CL) = myRecess
' ...
' ' ///

また、Switch()を知っている人なら、以下のようなシンプルな書き方
の方が解り易く馴染み易い、という場合もあるでしょうから、
一応挙げておきますが、
実質４行という短い記述にはなるものの、慣れていない人には扱い難いですね。

' ' ///
Dim i As Long
' ...
　　STAR_TIME = Cells(i, STAR_CL)　'　出勤時間
　　LAST_TIME = Cells(i, LAST_CL)　'　退勤時間
　　myTime = LAST_TIME - STAR_TIME　'　休憩時間
　　Cells(i, RECESS_CL) = Switch(myTime >= TimeValue("8:00"), "1:00", _
　　　　　　　　　　　　　　　　　　myTime >= TimeValue("6:00"), "0:45", _
　　　　　　　　　　　　　　　　　　myTime >= TimeValue("4:00"), "0:30", _
　　　　　　　　　　　　　　　　　　myTime >= 0, "0:00")
' ...
' ' ///

> このあと勤務時間の集計も行なう為、時間の計算方法なども教えて頂けると助かります。
教室ではないのでスキルアップ講座的に授けることは、時間的・空間的に無理です。
逆に教室で教わるにしても、実例に則して学ぶのが易しく効率的ですから、
解らなかったことを都度都度、訊ねるようにした方が、
身に付くと思いますし、未然に混乱を避ける意味もあるかと思います。

とりあえず、以上です。

お礼

素早く丁寧なご回答、感謝致します。問題なく動作しました。ありがとうございました。
今まで意識していなかったのですが、IfステートメントとSelect Caseステートメントの使い分けがわからず、どちらも条件分岐が出来るステートメントという認識に留まっていました。強引にIfで押し進めて来たな...と、反省しております。
variant型も、購入した本で極力使うなと書いてあったもので、なんとなく意識から外れていまして、
当たり前ですが、（極力）というからには使う機会があるのですね。
不等号も、上から順番に処理を進めていくのであんなに悪戦苦闘する必要は無かったとのだと。（だいぶ時間を使いました。）
Swichは知らなかったので勉強になります。ファイルサイズが増えていくようなら、このような短い記述の方が処理が軽くなったりするのでしょうか。こういった関数を積極的に使えるように、勉強を重ねたいと思います。

以下、少しだけ疑問におつきあい頂けませんでしょうか。

TimeValue関数は、やはり時間の表示もしてくれるみたいなのですが、時刻と時間を区別しなくても問題ないということですか？
私がFormatでわざわざ時刻を指定してしまっていた。という認識でよいのでしょうか。

回答No.2

cj_mover

＃１です。お礼欄拝見しました。

> variant型も、購入した本で極力使うなと書いてあったもので、なんとなく意識から外れていまして、
> 当たり前ですが、（極力）というからには使う機会があるのですね。
ＶＢＡ内部での演算に関しては、
ハッキリとデータ型が特定できるものは適切な型を選ぶべきです。
Variant方を避ける理由として、
　・使用するメモリが大きい
　・演算が（適切な型に比べると）比べると遅い
ここら辺の理由は、現代的には一般的ＰＣスペックの向上とともに
以前ほどには説得力ない気もしますが、無駄は無いに越したことはないです。
でも、これからもＶＢＡを勉強するのでしたら、
　・上達が遅くなる
という理由が大きくなります。
Variant型は、どんなデータ型でも格納できる、というものなので、
例えば、Variant/Double とか Variant/Long とか Variant/String とか
内部にはしっかりデータ型が指定されて格納されるものなのです。
ここをキチンと理解しておかないと、Variant型の方が却って難しい場面もあります。
例えば、小数点数を扱うデータ型には、
Single、Double、Currency、Date、等がありますが、中身は同じ小数点数ですから、
これらを異種相互に演算したり、代入したり、することも条件付きで可能です。
そんな時、どのデータ型で演算するのか、
本来的なプログラムではデータ型を変換して揃えないと
エラーになって計算出来ないものでも、
"気を利かせて"、"器用に"、"無難に"、演算してくれるのがVariantです。
しかし、見方を変えると、"勝手に"データ型を変えてしまうこともあるので、
こちらの意図を見抜いて適切な型を選ぶ機能は無いので、
そういった特性を正しく理解していないと、
部分的な演算は通っても、型のキャストのせいで、その後の処理でエラーになる
というような場合も出てきます。
そういう観点からすると、
初級の初めぐらいの学習者にとっては、問題になることもなく、
ただただその重宝さを享受して、次々に書く経験を増やせる点でメリットがあるけれど、
そろそろ中級ともなれば、却って扱いが難しくなってきます。
何より、あらゆるデータ型を理解しなければ、Variant型を使いこなすことも出来ませんし、
その上でVariant型の特性を理解するのも容易ではないです。
なので、まずはともかく、Variant型以外を覚えて貰わないと、
Variant型の使い方の話は出来ない、という意味で、
初級の初めを過ぎたら、極力使うな、という教え方になります。
実際は、Variant型でなければならないケースも多数ある訳ですが、
今は、そういうことに拘って停滞しちゃうよりは、
記憶の内にだけ留めて前に進んだ方がいいですし、
この課題は、今考えるのでなく後回しにした方が、
この課題への理解を深めるまでの近道になるんだと思います。

私が＃１で書いた
Dim myRecess As Variant
については、
部分的には、文字列値を格納していますから、
String型でも、コードは問題なく通ります。
また、格納したいのは、日付時刻型なのですから、
Date型に格納するのが、本来的・教科書的・基本的、な、やり方です。
（時刻値の扱いは掘り下げると書き切れないので略）
では何故わざわざVariant型を選んだかというと、
＃１にも軽く説明しましたが、セル範囲には文字列型で出力した方が
手間が省けるという、ご都合的・実戦的・応用的、な、方法論
（＋視覚的に解り易い記述を選んだ）です。
ここまでは、何故文字列値なのかという説明で、
Variant型の変数であっても、内部的には、Variant/String型になっています。
この値を演算（加・減算）することがあるなら、
myRecess = CDate(myRecess)
のように１行加えて、
本来のVariant/Date型に型変換してやるだけで済むので、
都合により一時的にString型にしますが、
いつでもDate型に型変換してくださいね、
という意味でもあり、受け皿としてはVariant型を選ぶことになったのです。
また、他の観点から、
日付時刻に関するＶＢＡの関数の戻り値は軒並みVariant型ですし、
セル範囲の値等とＶＢＡ変数とのデータのやりとりも基本的にVariant型を使います。
個人的に最近は、時刻のみのデータはなるべくVariant、日付を含む場合はDate
を使うようにしてデータ型変換関数と組み合わせるようにしています。

でもまぁＶＢＡは様々な書き方を許容するように作られている面もあって、
教本での教え方と、私（やここで回答する人）の教え方が、
一致する可能性は低いですから、
> 未然に混乱を避ける意味もある
解らなくなったら、今手にしている教本を終わらせることを優先した方がいいです。
質問掲示板も使いよう、、、です。
一応、疑問にはお応えするべきと思ったので説明を書きましたが、
先にも述べたように、以上のことは軽くスルーしてくれた方が吉、
（記憶の隅に置いといてくれるなら尚）私としてもベターです。

> Swichは知らなかったので勉強になります。ファイルサイズが増えていくようなら、このような短い記述の方が処理が軽くなったりするのでしょうか。こういった関数を積極的に使えるように、勉強を重ねたいと思います。
ファイルサイズの差は、看過できるレベル、でしょう（誤差のレベルと言ってもいい）。
処理速度という面では（テストしてませんが）関数をひとつ余計に使う訳ですから、
こちらもメリットは無い筈です。
ですので、Switch関数については、
今からワザワザ覚えるほどのものではない、というニュアンスで書いたつもりだったのです。
まぁ、タイプする文字数が少ない、ということは、仕事が早く片付く訳ですから、
状況によってはメリットありそうです。
マクロといっても繰り返し実行するものばかりではなくて、
例えば、新しいシートを作成する仕事など、一度しか使わないから早く簡単に書きたい場合
もありますし、デバッグ用、テスト用のコードとかもサクッと終らせたいですよね。
行数が少ないということは、昨今の小さなモニター環境での作業には有利でしょう。
ＴＰＯによっては使いでがあるとか、人によってはそういう回答をするかも、
という程度のものです。

> TimeValue関数は、やはり時間の表示もしてくれるみたいなのですが、時刻と時間を区別しなくても問題ないということですか？
まず、
"時刻と時間を区別"については、基本的にExceもＶＢＡも内部的には区別しないものと考えて下さい。
> myTime = LAST_TIME - STAR_TIME
これは形式的に、時間 = 時刻 - 時刻 という計算をしているわけです。
この文脈では時間は時刻として扱われます。
"時刻と時間を区別"する必要があるのは、
セルに時間を表示する場合、や、
ExcelまたはＶＢＡで文字列として時間を得たい場合、に限られます。
例えば、セルＡ１に勤務時間を合計した「123時間45分」を意味する値が入っているとして、
（内部的には 1900/1/1 0:00:00 を１とした 1900/1/5 3:45:00 という日付時刻値です）
"123:45"のように表示したい場合は、
[セルの書式設定][表示形式][分類][ユーザー定義][種類]に、[h]:mm のように指定するとか。
例えば、関数を使って他のセルに文字列として"123:45"を返したい時は、
　=TEXT(A1,"[h]:mm")
とか。
例えば、ＶＢＡでセルＡ１の値から、文字列値"123:45"を取得するには、
　rtn = WorksheetFunction.Text(Cells(1, 1).Value, "[h]:m")
とか。
「表示する文字列」　と　「値」　との違いを意識するようにしましょう。
意味が解らないようでしたら、
セルに表示されている文字列、と、数式バーに設定されている値、は、
違っていることがある、ということを時々思い出してみてください。
今は理解出来なくても、その内スーッと呑み込めるようになるでしょう。

> "TimeValue関数は、やはり時間の表示もしてくれるみたい"
表示は関係ないので、ちょっと意図が汲めませんでした。
ＶＢＡのヘルプでTimeValue関数を引くと、
"時刻を表すバリアント型 (内部処理形式 Date の Variant) の値を返します。"
と書かれています。

> 私がFormatでわざわざ時刻を指定してしまっていた。という認識でよいのでしょうか。
幾つか勘違いされていますが切り口として一点だけ説明します。
TimeValue関数は、文字列値を渡して日付値を返しますが、
Format関数は、この場合なら日付値を渡して文字列値を返します。
ExcelのTEXT関数と似た機能を持ちます。
主な使い方としては、数値（日付値も中身は数値です）を
指定した書式の文字列値にする、といった感じです。
例えば、セルＡ２の値が日付値 2015/1/2 3:45 だったとして、
"15/01/02 03:45"という書式を適用した文字列を返す場合には、
　MsgBox Format(Range("A2").Value, "yy/mm/dd hh:mm")
などの様に使います。

以上で文字数制限4000文字。

お礼

詳しく説明してくださってありがとうございます。
ここまで長い説明大変でしたでしょうに、おかげさまでスッキリしました。
私の知識では、適所にVarinat型を配置するのは出来ないでしょうから、今まで通り別の型から使っていきたいと思います。Swichに関しても、やはり知っているものを駆使して出来る範囲で。を基本に困ったらまた、こちらで質問させて頂くことと致します。
何度も何度も、読み込んでしまいました。疑問の返答が反って来るというのは、勉強する上で大きく理解がかわるものですね。
＞＞"時刻と時間を区別"については、基本的にExceもＶＢＡも内部的には区別しないものと考えて下さい
＞＞（内部的には 1900/1/1 0:00:00 を１とした 1900/1/5 3:45:00 という日付時刻値です）
そうか、そう言うものなのかと、ストンと落ちて来た感じがします。

こちらの内容が、私にとって目から鱗でしたが、本題の回答である＃１をベストアンサーにさせていただきます。
本当にありがとうございました。