Excel (VBA)

Excel VBAに関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(指定なし : 指定なし)
時間計算 2
投稿日時: 22/06/06 14:49:09
投稿者: vaioyuki

先ほど、
https://www.moug.net/faq/viewtopic.php?p=514914#514914
の時間計算でお世話になりました。
 
ちゃんと時間計算できた!!よしっ!!と思ってたのですが、
所要時間は7時間に満たない、6:00 の表示とかだと計算してくれません。
時間計算として -1:00 という表示ができないのでしょうか?
 
何度も申し訳ありませんがよろしくお願いします。

回答
投稿日時: 22/06/06 15:04:17
投稿者: simple

この質問への回答ではなく、先ほどのスレへのコメントです。
 

Dim Tkin, Tsho, Tcho, TT As Date
とされていますが、これは、
Dim Tkin As Variant, Tsho As Variant, Tcho As Variant, TT As Date
と書いているのと同じですよ。
個々に設定する必要があります。
 
Variantでも正常動作したということでしょうか。

投稿日時: 22/06/06 15:20:44
投稿者: vaioyuki

一行に書けば Date になるものだと思ってました。
一応正常に動いているのですが、問題あるかもしれないので訂正します。
ご指摘ありがとうございます。
 
と思ったら全く出来なくなってしまいました(泣)
 
再度修正してみます。

投稿日時: 22/06/06 15:40:10
投稿者: vaioyuki

書き直しました。
Else部分はVLOOKUPに関することなので無視してください。
 

Dim Tstart As Date, Tend As Date, Tkin As Date, Tsho As Date, Tcho As Date, TT As Date
Dim T1 As Date, T2 As Date, T3 As Date, T4 As Date, T5 As Date, T6 As Date, T7 As Date, T8 As Date

    For i = 2 To r_cntD
    On Error Resume Next

    Tstart = .Cells(i, 8) '開始時間
    Tend = .Cells(i, 9) '終了時間
    T1 = .Cells(i, 11) '所要時間1
    T2 = .Cells(i, 25) '所要時間2
    T3 = .Cells(i, 39) '所要時間3
    T4 = .Cells(i, 53) '所要時間4
    T5 = .Cells(i, 67) '所要時間5
    T6 = .Cells(i, 81) '所要時間6
    T7 = .Cells(i, 95) '所要時間7
    T8 = .Cells(i, 109) '所要時間8
    
    TT = TimeSerial(7, 0, 0) '7時間設定
    
        If Err.Number = 0 Then
            .Cells(i, 129).Value = WorksheetFunction.VLookup(.Cells(i, 2), Tanto.Range("A:B"), 2, False)
            .Cells(i, 130).Value = DateAdd("h", -1, Tend) - .Cells(i, 8)
            .Cells(i, 131) = WorksheetFunction.Sum(T1, T2, T3, T4, T5, T6, T7, T8)
            .Cells(i, 132).Value = Hour(Tkin) * 60 + Minute(Tkin) = Hour(Tsho) * 60 + Minute(Tsho)
'            .Cells(i, 132).Value = Tkin = Tsho

            Tkin = .Cells(i, 130) '勤務時間
            Tsho = .Cells(i, 131) '所要時間

'            Stop
            If Tsho <> TimeSerial(7, 0, 0) Then
                .Cells(i, 133).Value = Tsho - TT
            Else
                .Cells(i, 133).Value = ""
            End If

        Else
            .Cells(i, 174).Value = ""
            Tkin = .Cells(i, 9)
            .Cells(i, 130).Value = DateAdd("h", -1, Tkin) - .Cells(i, 8)
            .Cells(i, 131).Value = WorksheetFunction.Sum(.Cells(i, 11), .Cells(i, 25), .Cells(i, 39), .Cells(i, 53), .Cells(i, 67), .Cells(i, 81), .Cells(i, 95), .Cells(i, 109))
            .Cells(i, 132).Value = .Cells(i, 130).Value = .Cells(i, 131).Value
        End If

    On Error GoTo 0
    Next i

 
時間が入ったセルを変数にしました。
結果、なぜか超過していない時間なのに計算され、中身が「-5.55111512312578E-17」こんな数字になっています。
これは前にTRUEなのにFALSEになってしまうときと同じ行です。
(そのために「.Cells(i, 132).Value = Hour(Tkin) * 60 + Minute(Tkin) = Hour(Tsho) * 60 + Minute(Tsho)」表記にしました。普通に「.Cells(i, 132).Value = .Cells(i, 130).Value = .Cells(i, 131).Value」ではTRUEでもFALSEになります。)
ひとつずつ見て行っても、正しく計算される方もエラーになる方も「7:00:00」と表示されています。

回答
投稿日時: 22/06/06 16:19:27
投稿者: WinArrow
投稿者のウェブサイトに移動

変数のデータ型を宣言しただけでは、不充分です。
セルもデータ型を意識する必要があります。
方法は3つ
 
対策1
セルにデータ型を設定する
セル.Numberformatlocal = "hh:mm:ss"
  
対策2
セルに代入する際、データ型変換する.1
セル.Value = CDate(式)
  
対策3
セルに代入する際、データ型変換する.2
セル.Value = Format(式,"hh:mm:ss")
 
※セルのアドレス・列を数字ではなく、英字にしませんか?
私的には、英字の方が分かりやすいです。
多分、修正も楽だと思います。
 
Cells(1, 131) → Cells(1, "EA")
 

投稿日時: 22/06/06 20:46:12
投稿者: vaioyuki

ありがとうございます。
すごーくややこしくなりそうだったので変数にするのはやめました。苦笑
 

    r_cntD = .Cells(Rows.Count, "A").End(xlUp).Row

    For i = 2 To r_cntD
        For j = 11 To 109 Step 14
            If .Cells(i, j) = "未記入" Then
                .Cells(i, j) = TimeSerial(0, 0, 0)
            End If
        Next j
    Next i

    For i = 2 To r_cntD
    On Error Resume Next
    
    .Cells(i, 8).NumberFormatLocal = "hh:mm" '開始時間
    .Cells(i, 9).NumberFormatLocal = "hh:mm" '終了時間
    .Cells(i, 11).NumberFormatLocal = "hh:mm" '所要時間1
    .Cells(i, 25).NumberFormatLocal = "hh:mm" '所要時間2
    .Cells(i, 39).NumberFormatLocal = "hh:mm" '所要時間3
    .Cells(i, 53).NumberFormatLocal = "hh:mm" '所要時間4
    .Cells(i, 67).NumberFormatLocal = "hh:mm" '所要時間5
    .Cells(i, 81).NumberFormatLocal = "hh:mm" '所要時間6
    .Cells(i, 95).NumberFormatLocal = "hh:mm" '所要時間7
    .Cells(i, 109).NumberFormatLocal = "hh:mm" '所要時間8
    .Cells(i, 130).NumberFormatLocal = "hh:mm" '勤務時間
    .Cells(i, 131).NumberFormatLocal = "hh:mm" '所要時間

    TT = TimeSerial(7, 0, 0) '7時間設定

        If Err.Number = 0 Then
            .Cells(i, 129).Value = WorksheetFunction.VLookup(.Cells(i, 2), Tanto.Range("A:B"), 2, False)

            .Cells(i, 130).Value = DateAdd("h", -1, .Cells(i, 9)) - .Cells(i, 8)
            .Cells(i, 131) = CDate(WorksheetFunction.Sum(.Cells(i, 11), .Cells(i, 25), .Cells(i, 39), .Cells(i, 53), .Cells(i, 67), .Cells(i, 81), .Cells(i, 95), .Cells(i, 109)))

            .Cells(i, 132).Value = .Cells(i, 130) = .Cells(i, 131)

'            Stop

            If .Cells(i, 131) <> TimeSerial(7, 0, 0) Then
                .Cells(i, 133).Value = CDate(.Cells(i, 131) - TT)
            Else
                .Cells(i, 133).Value = ""
            End If

        Else
            .Cells(i, 174).Value = ""
            Tkin = .Cells(i, 9)
            .Cells(i, 130).Value = DateAdd("h", -1, Tkin) - .Cells(i, 8)
            .Cells(i, 131).Value = WorksheetFunction.Sum(.Cells(i, 11), .Cells(i, 25), .Cells(i, 39), .Cells(i, 53), .Cells(i, 67), .Cells(i, 81), .Cells(i, 95), .Cells(i, 109))
            .Cells(i, 132).Value = .Cells(i, 130).Value = .Cells(i, 131).Value
        End If

    On Error GoTo 0
    Next i

 
こちらでひとつずつ確認したら、
前はTRUEなのにFALSEになるところは改善されました!!
 
しかし、
7時間未満のもの、4:50 表記の時に
If .Cells(i, 131) <> TimeSerial(7, 0, 0) Then
   .Cells(i, 133).Value = CDate(.Cells(i, 131) - TT)
Else
    .Cells(i, 133).Value = ""
End If

こちらの「CDate(.Cells(i, 131) - TT)」のところにマウスをあてると、
「CDate(.Cells(i, 131) - TT)=2:10:00」と出ますがセルには何も出ません。
 
 
 
※英字表記、Cellsを使うときは数字でないとだめだとばかり思ってました。
 英字も使えるんですね。
 今は全部数字にしてしまっているので次回から英字でやってみます!!

回答
投稿日時: 22/06/06 21:20:44
投稿者: WinArrow
投稿者のウェブサイトに移動

引用:

    .Cells(i, 8).NumberFormatLocal = "hh:mm" '開始時間
    .Cells(i, 9).NumberFormatLocal = "hh:mm" '終了時間
    .Cells(i, 11).NumberFormatLocal = "hh:mm" '所要時間1
    .Cells(i, 25).NumberFormatLocal = "hh:mm" '所要時間2
    .Cells(i, 39).NumberFormatLocal = "hh:mm" '所要時間3
    .Cells(i, 53).NumberFormatLocal = "hh:mm" '所要時間4
    .Cells(i, 67).NumberFormatLocal = "hh:mm" '所要時間5
    .Cells(i, 81).NumberFormatLocal = "hh:mm" '所要時間6
    .Cells(i, 95).NumberFormatLocal = "hh:mm" '所要時間7
    .Cells(i, 109).NumberFormatLocal = "hh:mm" '所要時間8
    .Cells(i, 130).NumberFormatLocal = "hh:mm" '勤務時間
    .Cells(i, 131).NumberFormatLocal = "hh:mm" '所要時間

を↓のように記述することもできます。
dim myCell As Range
    Set myCell = Union(.Cells(i, 8), _
                        .Cells(i, 9), _
                        .Cells(i, 11), _
                        .Cells(i, 25), _
                        .Cells(i, 39), _
                        .Cells(i, 53), _
                        .Cells(i, 67), _
                        .Cells(i, 81), _
                        .Cells(i, 95), _
                        .Cells(i, 109), _
                        .Cells(i, 130), _
                        .Cells(i, 131))
    myCell.NumberFormatLocal = "hh:mm:ss" '所要時間

投稿日時: 22/06/06 21:28:47
投稿者: vaioyuki

ありがとうございます。
だいぶすっきりしますね!!

回答
投稿日時: 22/06/06 21:39:25
投稿者: WinArrow
投稿者のウェブサイトに移動

引用:

こちらの「CDate(.Cells(i, 131) - TT)」のところにマウスをあてると、
「CDate(.Cells(i, 131) - TT)=2:10:00」と出ますがセルには何も出ません。

 
.Cells(i, 131)野値が「4:50」で
 TTの値が「7:0:0」
ということは、マイナスにンるはずです。
しかし、時刻のマイナスは、許可されていませんから、
セルには、[#######]が表示されるはずですが・・・・
 
7:0:0より大きいか/小さいかは判定しないといけませんよね・・・・
 

投稿日時: 22/06/06 22:02:16
投稿者: vaioyuki

それが「########」も表示されません。
 
試しに、
 

            If .Cells(i, 131) > TimeSerial(7, 0, 0) Then
                .Cells(i, 133).Value = CDate(.Cells(i, 131) - TT)
            ElseIf .Cells(i, 131) < TimeSerial(7, 0, 0) Then
                .Cells(i, 133).Value = CDate(TT - .Cells(i, 131))
                .Cells(i, 134).Value = "-"
            Else
                .Cells(i, 133).Value = ""
            End If

 
とすると、計算もしてくれて隣のセルに「-」も表記できました。
こういうことでしか解決は難しいですかね。。。
 
ちなみに、
検索してオプションから「1904年から計算する」にチェックを入れても表示は出ませんでした。

回答
投稿日時: 22/06/07 07:35:00
投稿者: WinArrow
投稿者のウェブサイトに移動

>.Cells(i, 133)
の表示形式の設定がされていないようです。
  
表示形式を確認してみたら・・・・
  
因みに
.Cells(i, 133).NumberFormatLocal = "hh:mm:ss;;;"
または
.Cells(i, 133).NumberFormatLocal = "#"
  
と設定すると何も表示されません。
 
追記です。
 
セルの書式を設定する時は、個々のセルで設定するよりも、
事前に列単位で設定したほうが、
メモリの節約、ファイル容量の増抑制、処理速度などメリットが大きいです。
今回は、時刻の表示形式ですが、罫線の設定は、大きな効果が期待できます。
(但し、データ量による)

投稿日時: 22/06/07 10:24:53
投稿者: vaioyuki

ありがとうございます。
 
133列目を表示形式設定して、列指定にしてみました。
 

    .Columns(8).NumberFormatLocal = "hh:mm" '開始時間
    .Columns(9).NumberFormatLocal = "hh:mm" '終了時間
    .Columns(11).NumberFormatLocal = "hh:mm" '所要時間1
    .Columns(25).NumberFormatLocal = "hh:mm" '所要時間2
    .Columns(39).NumberFormatLocal = "hh:mm" '所要時間3
    .Columns(53).NumberFormatLocal = "hh:mm" '所要時間4
    .Columns(67).NumberFormatLocal = "hh:mm" '所要時間5
    .Columns(81).NumberFormatLocal = "hh:mm" '所要時間6
    .Columns(95).NumberFormatLocal = "hh:mm" '所要時間7
    .Columns(109).NumberFormatLocal = "hh:mm" '所要時間8
    .Columns(130).NumberFormatLocal = "hh:mm" '勤務時間
    .Columns(131).NumberFormatLocal = "hh:mm" '所要時間
    .Columns(133).NumberFormatLocal = "hh:mm" '所要時間

    For i = 2 To r_cntD
    On Error Resume Next
    
'    .Cells(i, 8).NumberFormatLocal = "hh:mm" '開始時間
'    .Cells(i, 9).NumberFormatLocal = "hh:mm" '終了時間
'    .Cells(i, 11).NumberFormatLocal = "hh:mm" '所要時間1
'    .Cells(i, 25).NumberFormatLocal = "hh:mm" '所要時間2
'    .Cells(i, 39).NumberFormatLocal = "hh:mm" '所要時間3
'    .Cells(i, 53).NumberFormatLocal = "hh:mm" '所要時間4
'    .Cells(i, 67).NumberFormatLocal = "hh:mm" '所要時間5
'    .Cells(i, 81).NumberFormatLocal = "hh:mm" '所要時間6
'    .Cells(i, 95).NumberFormatLocal = "hh:mm" '所要時間7
'    .Cells(i, 109).NumberFormatLocal = "hh:mm" '所要時間8
'    .Cells(i, 130).NumberFormatLocal = "hh:mm" '勤務時間
'    .Cells(i, 131).NumberFormatLocal = "hh:mm" '所要時間

'    Tstart = .Cells(i, 8) '開始時間
'    Tend = .Cells(i, 9) '終了時間
'    T1 = .Cells(i, 11) '所要時間1
'    T2 = .Cells(i, 25) '所要時間2
'    T3 = .Cells(i, 39) '所要時間3
'    T4 = .Cells(i, 53) '所要時間4
'    T5 = .Cells(i, 67) '所要時間5
'    T6 = .Cells(i, 81) '所要時間6
'    T7 = .Cells(i, 95) '所要時間7
'    T8 = .Cells(i, 109) '所要時間8

    TT = TimeSerial(7, 0, 0) '7時間設定

        If Err.Number = 0 Then
            .Cells(i, 129).Value = WorksheetFunction.VLookup(.Cells(i, 2), Tanto.Range("A:B"), 2, False)
'            .Cells(i, 130).Value = DateAdd("h", -1, Tend) - .Cells(i, 8)
'            .Cells(i, 131) = WorksheetFunction.Sum(T1, T2, T3, T4, T5, T6, T7, T8)
'            .Cells(i, 132).Value = Hour(Tkin) * 60 + Minute(Tkin) = Hour(Tsho) * 60 + Minute(Tsho)
            .Cells(i, 130).Value = DateAdd("h", -1, .Cells(i, 9)) - .Cells(i, 8)
            .Cells(i, 131) = CDate(WorksheetFunction.Sum(.Cells(i, 11), .Cells(i, 25), .Cells(i, 39), .Cells(i, 53), .Cells(i, 67), .Cells(i, 81), .Cells(i, 95), .Cells(i, 109)))
'            .Cells(i, 132).Value = Hour(.Cells(i, 130)) * 60 + Minute(.Cells(i, 130)) = Hour(.Cells(i, 131)) * 60 + Minute(.Cells(i, 131))
            .Cells(i, 132).Value = .Cells(i, 130) = .Cells(i, 131)

'            Tkin = .Cells(i, 130) '勤務時間
'            Tsho = .Cells(i, 131) '所要時間

'            Stop
'            If Tsho <> TimeSerial(7, 0, 0) Then
'                .Cells(i, 133).Value = Tsho - TT
'            Else
'                .Cells(i, 133).Value = ""
'            End If

'            If .Cells(i, 131) <> TimeSerial(7, 0, 0) Then
'                .Cells(i, 133).Value = CDate(.Cells(i, 131) - TT)
'            Else
'                .Cells(i, 133).Value = ""
'            End If

            If .Cells(i, 131) <> TT Then
                .Cells(i, 133).Value = CDate(.Cells(i, 131) - TT)
'            ElseIf .Cells(i, 131) < TT Then
'                .Cells(i, 133).Value = CDate(TT - .Cells(i, 131))
'                .Cells(i, 134).Value = "-"
            Else
                .Cells(i, 133).Value = ""
            End If

        Else
            .Cells(i, 174).Value = ""
            Tkin = .Cells(i, 9)
            .Cells(i, 130).Value = DateAdd("h", -1, Tkin) - .Cells(i, 8)
            .Cells(i, 131).Value = WorksheetFunction.Sum(.Cells(i, 11), .Cells(i, 25), .Cells(i, 39), .Cells(i, 53), .Cells(i, 67), .Cells(i, 81), .Cells(i, 95), .Cells(i, 109))
            .Cells(i, 132).Value = .Cells(i, 130).Value = .Cells(i, 131).Value
        End If

    On Error GoTo 0
    Next i

 
しかしながら、7時間満たないものの計算結果は出ませんでした。
普通にセル計算で
A    B
4:50    7:00    
=A1-B2
とすると「-2:10」と表記されます。
 
VBAでは不可能なんでしょうか。。。泣
どうしても7時間未満のものの計算をしてくれません。。。

回答
投稿日時: 22/06/07 11:23:33
投稿者: taitani

本来マイナスの時間は存在しないため、規定では"#####"の表示になる認識です。
書式設定とか、計算式とかで設定されていませんか?
 
・参考
https://dekiru.net/article/18527/

回答
投稿日時: 22/06/07 12:21:45
投稿者: WinArrow
投稿者のウェブサイトに移動

>どうしても7時間未満のものの計算をしてくれません。。。
 
原因は2つ
 
1つは、
OnError resume Next
 
2つめは
> .Cells(i, 133).Value = CDate(.Cells(i, 131) - TT)
この計算式は、本来、実行時エラー(1004)になるはずです。
 
Cdateの引数には、「.Cells(i, 131)」にすべきところ、
計算結果(-2:10)を引数にしています。
時刻にはマイナス値は存在しませんから、エラーになっても、
エラートラップ設定で、スキップしていた。。。という落ちでしょう。
安易にエラートラップを使うと、デバッグにならないということを覚えてほしい。
 
正)
   .Cells(i, 133).Value = CDate(.Cells(i, 131).Value) - TT
でもね、セルに表示形式で時刻をせっていいしたんだから、Cdateは不要でしょう。
 
それから、不用意にエラートラップを使わないことです。
コードの行が多くなると、エラートラップを掛けたこと忘れちゃうよね・・・

回答
投稿日時: 22/06/07 12:35:21
投稿者: WinArrow
投稿者のウェブサイトに移動

追伸
 
デバッグを回答者にお願いしてはだめです。
 
「デバッグ」とは、
プログラムに潜むバグを発見することで、プログラムをより良いものにしようとする作業です。
エラートラップ設定によって
「プログラムに潜むバグ」を見えなくしています。
 
「プログラムに潜むバグ」には、システム設計者(含む、プログラマ)の
先入観、勘違い、思い込み、などが含まれます。

投稿日時: 22/06/07 15:39:38
投稿者: vaioyuki

知らなかったこととはいえ、失礼しました。
エラー処理でスキップされてたのですね。
 
CDateも抜いて、
エラー処理から脱出させてみたらうまくいきました!!
 

    For i = 2 To r_cntD
        For j = 11 To 109 Step 14
            If .Cells(i, j) = "未記入" Then
                .Cells(i, j) = TimeSerial(0, 0, 0)
            End If
        Next j
    Next i

    .Columns(8).NumberFormatLocal = "hh:mm" '開始時間
    .Columns(9).NumberFormatLocal = "hh:mm" '終了時間
    .Columns(11).NumberFormatLocal = "hh:mm" '所要時間1
    .Columns(25).NumberFormatLocal = "hh:mm" '所要時間2
    .Columns(39).NumberFormatLocal = "hh:mm" '所要時間3
    .Columns(53).NumberFormatLocal = "hh:mm" '所要時間4
    .Columns(67).NumberFormatLocal = "hh:mm" '所要時間5
    .Columns(81).NumberFormatLocal = "hh:mm" '所要時間6
    .Columns(95).NumberFormatLocal = "hh:mm" '所要時間7
    .Columns(109).NumberFormatLocal = "hh:mm" '所要時間8
    .Columns(130).NumberFormatLocal = "hh:mm" '勤務時間
    .Columns(131).NumberFormatLocal = "hh:mm" '所要時間
    .Columns(133).NumberFormatLocal = "hh:mm" '超過時間

    For i = 2 To r_cntD
    On Error Resume Next

    TT = TimeSerial(7, 0, 0) '7時間設定

        If Err.Number = 0 Then
            .Cells(i, 129).Value = WorksheetFunction.VLookup(.Cells(i, 2), Tanto.Range("A:B"), 2, False)
            .Cells(i, 130).Value = DateAdd("h", -1, .Cells(i, 9)) - .Cells(i, 8)
            .Cells(i, 131) = CDate(WorksheetFunction.Sum(.Cells(i, 11), .Cells(i, 25), .Cells(i, 39), .Cells(i, 53), .Cells(i, 67), .Cells(i, 81), .Cells(i, 95), .Cells(i, 109)))
            .Cells(i, 132).Value = .Cells(i, 130) = .Cells(i, 131)
        Else
            .Cells(i, 174).Value = ""
            Tkin = .Cells(i, 9)
            .Cells(i, 130).Value = DateAdd("h", -1, Tkin) - .Cells(i, 8)
            .Cells(i, 131).Value = WorksheetFunction.Sum(.Cells(i, 11), .Cells(i, 25), .Cells(i, 39), .Cells(i, 53), .Cells(i, 67), .Cells(i, 81), .Cells(i, 95), .Cells(i, 109))
            .Cells(i, 132).Value = .Cells(i, 130).Value = .Cells(i, 131).Value
        End If

    On Error GoTo 0
    
            If .Cells(i, 131) <> TT Then
                .Cells(i, 133).Value = .Cells(i, 131) - .Cells(i, 134)
            Else
                .Cells(i, 133).Value = ""
            End If
    Next i

 
ちゃんとマイナス表示もしてくれて、
それによる計算も出来ました。
本当にありがとうございました。