Excel (VBA)

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

 
(Windows 10 Home : Excel 2016)
0.:75
投稿日時: 18/02/02 08:53:45
投稿者: 雲取山

勤務表を作りました。
ネットから調べて4桁の数字を入力すると時間に変換される
コードを利用して、下記のようなコードをシートに作りました。
O8からP38の範囲で、例として”0900”と入力すると”9:00”となるようなものです。
非常に便利なものなのですが、”1800”と入力しますと”0.:75”と変換され、セルの左寄せになってしまい
困っております。18:00の帰社の方が多いので、困っております。
理由がわからないもので、すみません。お力を貸していただければとおもっております。
ご指導のほど、よろしくお願いします。
 
 
Private Sub Worksheet_Change(ByVal Target As Range)
 Dim t As String
  
  On Error GoTo ERR1
   
 t = Target.Value
  
 If Application.Intersect(Target, Range("O8:P38")) Is Nothing Then Exit Sub
 
 If Len(t) <> 4 Then Exit Sub
 With Target
      
     .NumberFormatLocal = "h:mm;@"
 
     .Formula = Left(t, 2) & ":" & Right(t, 2)
      
End With
 
ERR1:
 Exit Sub
 
 End Sub

回答
投稿日時: 18/02/02 09:56:45
投稿者: mattuwan44

>理由がわからないもので、すみません。お力を貸していただければとおもっております。
理由は、はっきり探るつもりはありませんが、
キーボードから「0900」と文字を入力しても、
セルの値(Target.Value)は900になります。
その時点で前提条件が崩れます(0900→09:00)
なので、まずは確実に前提条件が担保されることを確認、
あるいはFormat(900,"0000")と強制することが必要です。
そのうえで、文字列を時刻に変換してセルに値を戻してやるといいと思います。
 
また、セルのChangeイベントは、セルの値を変えるごとに発生します。
つまり次々と無限にイベントが発生する可能性があり、処理が終わらなくなる場合があります。
(今回どうなのかはわかりませんが。)
なので、changeイベント内でセルの値を入力する場合は、
イベントの発生を抑制する必要があります。
 
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim s As String
    Dim m As String
 
    With Target
        'イベント処理対象セル範囲外なら抜ける
        If Intersect(.Cells, Me.Range("O8:P38")) Is Nothing Then Exit Sub
        '対象セルが単一セルでないなら抜ける
        If .CountLarge > 1 Then Exit Sub
        '対象セルの値が4文字より大きければ抜ける
        If Len(.Value) > 4 Then Exit Sub
        '対象セルの値を特定の文字に変換できるかやってみる
        s = Format(.Value, "00:00")
        '文字が日付(時刻に)「に変換できなければ抜ける
        If IsDate(s) = False Then Exit Sub
 
        'イベント処理の一時無効化(セルの値を変更すると次のイベントが発生するのでそれを抑制)
        Application.EnableEvents = False
        '文字列を時刻に変換
        .Value = TimeValue(s)
        'イベント処理再開(再開しておかないと、次の入力でイベントが発生しない)
        Application.EnableEvents = True
    End With
End Sub
 
あと、セルの書式設定は1回やっておけばいいので、
個々のセルでいちいち設定するのは好ましくありません。
 
ただ、タイプミスがあった時に何か再入力を促す仕組みを作っておきたいですねー
入力規則の機能で上手く制御できればいいのですが。。。。
0から2400までしか入力出来ないようにすればいいのかなぁ。。。。

回答
投稿日時: 18/02/02 11:03:13
投稿者: WinArrow
投稿者のウェブサイトに移動

>、”1800”と入力しますと”0.:75”と変換され、セルの左寄せになってしまい
VBAで対応するような問題ではないと思います。
 
0900のように入力したときは、900と判断されて4桁とは判断されません。
最終的には、時刻形式に変換しているから
最初から時刻形式で入力すれば、このような問題は発生しません。
 
たった1文字の「:」を入力するの手抜きをしたいために、面倒なコードを考えることは
本末転倒です。
 
同じ手抜きをするならば、オートコレクトを使う方法があります。
 
「..」を「:」に変換できるうにしておけば
「9..」と入力すると「9:00」と時刻形式になります。
「18..」と入力すると「18:00」と時刻形式になります。
 
隣のセル(B1)でよければ、
=TEXT(A1,"00!:00")*1
で、時刻形式になります。
 
 
 
 

回答
投稿日時: 18/02/02 11:15:35
投稿者: WinArrow
投稿者のウェブサイトに移動

>、”1800”と入力しますと”0.:75”と変換され、セルの左寄せになってしまい
の説明は抜けていました。
 
日付、時刻は。シリアル値という数値(目には見えない)で記憶されています。
日付は、整数部、時刻は、小数部になります。
 
24時は、日付で1になりますから、12時は0.5、18時は0.75になります。
 
ですから、単純に数値化すると、少数値となることがあります。
 
時刻として目視できるようにするには、表示形式を設定しなければいけません。
その指定が、例えば、"hh:mm"とか"hh:mm:ss"になります。
 
余談ですが、
日付は、1900/1/1を「1」する連続番号(シリアル値)で記憶されています。
たとえば、2018/2/2は、43133です。
2018/2/2の12:00は、43133.5になります。
これでは、人間は非常に扱う難いですね。
人間が扱う安いように
2018/2/2 12:00:00
と表示する機能が「表示形式」設定なのです。
 
 

回答
投稿日時: 18/02/05 09:27:21
投稿者: 細雪

コレ、うまく動かんでしょ?と思ったのですが・・
そもそもの表示形式を文字列にしてあるんですね(笑)。
 
で、

雲取山 さんの引用:

”1800”と入力しますと”0.:75”と変換され、セルの左寄せになってしまい

流れとしては・・
「1800」と入力→Changeイベント作動→イベントにより「18:00」と入力
       →Changeイベント動作→イベントにより「0.:75」と入力
       →数値以外が入力されたので処理せず終了
とループしている感覚です。
なので、コレを解消するために
    Application.EnableEvents = False
を使って、一時的にイベント発生を抑制してやれば解決。
具体的には
Private Sub Worksheet_Change(ByVal Target As Range)
Dim t As String

On Error GoTo ERR1
    t = Target.Value
    If Application.Intersect(Target, Range("O8:P38")) Is Nothing Then Exit Sub
    If Len(t) <> 4 Then Exit Sub

    [color=red]Application.EnableEvents = False[/color]
    With Target
        .NumberFormatLocal = "h:mm;@"
        .Formula = Left(t, 2) & ":" & Right(t, 2)
    End With

ERR1:
    [color=red]Application.EnableEvents = True[/color]
    Exit Sub
End Sub

こんな感じで。
 
 
ただし、ちょっとアレです。
冒頭で指摘した通り、入力される前の「表示形式」は文字列ですよね?
それをこのイベントで時刻に設定する・・という考え方だろうと思います。
それじゃぁ、「0800 って打っちゃった。ほんとは 0900 なの。」というときにはどう対応します?
多分、 If Len(t) <> 4 Then Exit Sub に弾かれますよ。
 
なので、もう少し回りくどくしてやります。
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ERR1
    If Application.Intersect(Target, Range("O8:P38")) Is Nothing Then Exit Sub

    Application.EnableEvents = False
    With Target
        ' .NumberFormatLocal = "h:mm;@"  ' コレは多分要らない
        .Value = Int(.Value / 100) & ":" & .Value Mod 100 ' ココ変更
    End With

ERR1:
    Application.EnableEvents = True
    Exit Sub
End Sub

要するに・・100の位以上をInt関数で、100の位未満をMod関数で取り出してやったほうが良いかも。
ってことです。これなら「900」にも「0900」にも対応できますしね。
 
ま、どちらにしても
> ネットから調べて〜〜非常に便利なもの
と呼ぶにはちょっと不親切かなぁ、と思わなくもない今日この頃です。
 
 
残る問題は、律義に「18:00」と打ってくれる人への対応くらいですかねぇ(笑)。
    If Target.Value < 1 Then Exit Sub
で充分なような気もするけれど・・運用次第かな。

回答
投稿日時: 18/02/05 16:34:12
投稿者: WinArrow
投稿者のウェブサイトに移動

私見ですが、VBAで、しかも、Chengeイベントで対応することは、
一見すると、便利なように見えますが、
入力ミスへの対処、イベント発生制御、など、裏側でいろんな仕掛けを用意しておく必要があります。
 
今回の例では、
(1)イベントプロシジャの中で表示形式を変更しているから、
   入力ミスがあった場合、同じ入力はできなくなります。(つまり、修正が難しいということです)
 
※細雪 さんも指摘していますが、私は、致命的な問題と思います。
 
質問者さんは、イベントプロシジャの振舞いをよく理解していないように思われます。
 
アドバイスを理解して、このまま頑張って、続けるか?
素直にオーソドックスな方法に切り替えるか?
 
ご回答を!!
 
 
 
 

回答
投稿日時: 18/02/06 19:45:52
投稿者: 細雪

推測でモノを喋るのは良くないのですが、
おそらく「ネットで調べたコード」の中身の理解もままならないのでは?
そんなわけで、ワークシート内の計算式だけで頑張った方が良いような気がします。
 
ひとまず、該当セルの「表示形式」を「00":"00」と設定。
コレで見た目「時刻」に出来ます。
 
で、例えばA1セルに出勤時間(例として「0900」)、
B1セルに退勤時間(例として「1800」)がそれぞれ入力されているとします。
そんな時には
  C1セル:=TEXT(B1,"00"":""00")*1-TEXT(A1,"00"":""00")*1   (※1)
として、C1セルの表示形式を「[h]:mm」にしてやります。
ココまででいわゆる「勤務時間(9:00)」が出せます。
 
ココに休憩時間(1時間)を引きたい、というのであれば
  C1セル:=TEXT(B1,"00"":""00")*1-TEXT(A1,"00"":""00")*1-"1:00"
で、休憩時間を引いた時間(8:00)を引っ張ってやれます。
 
 
 
※1)理屈が分かれば(理解できれば)
  C1セル:=(B1-A1)/100/24
でも問題ないですけれど。
もう少し理解しやすく書くなら・・
  C1セル:=( (B1/100) / 24) - ( (A1/100) / 24)
       ※もちろん、エクセルで使う時は空白を消してくださいね。
こんな感じですね。
 
 
 
 
・・・つまり、VBAはオススメしない、という事です。

投稿日時: 18/02/07 10:30:11
投稿者: 雲取山

本当に色々とご提案ありがとうございました。
私なりに試してみて、細雪様のご提案が一番合っておりました。
.NumberFormatLocal = "G/標準" と入れたほうが、私の環境では、
うまく動作しました。
本当に助かりました。ありがとうございました。
 
 
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ERR1
    If Application.Intersect(Target, Range("O8:P38")) Is Nothing Then Exit Sub
 
    Application.EnableEvents = False
    With Target
        .NumberFormatLocal = "G/標準"
     
        .Value = Int(.Value / 100) & ":" & .Value Mod 100 ' ココ変更
    End With
 
ERR1:
    Application.EnableEvents = True
    Exit Sub
     
 
     
End Sub