Excel (VBA)

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

 
(Windows 10 Pro : Excel 2016)
文字列の時刻への変換について 2
投稿日時: 19/09/30 10:43:48
投稿者: TI

おはようございます。
 
以前、同名の質問をさせていただいたものです。
 
前回、解決済みとさせていただきましたが、もう2点問題が発生しました。
 
24時を超える時間の入力の際に表示がおかしくなってしまいます。
(「26:00」 → 「62400:00」)
 
また、以下のようにプログラムを記載しているのですが、時刻データが
「0600」 → 「1900/1/3 6:00:00」
と日付が加算された状況で数式バーには表示されます。なぜ、加算されるのかが
不明です。
 
現状の問題として
1.24時間を超える入力の表示方法
2.計算できる状態でのデータの取得方法
のアドバイスをいただきたい状況です。
 
入力の都合上は、「26:00」等の24時間を超える入力が希望であり、
時間計算をする都合上は、時刻形式でデータを持つことが希望です。
 
24時間を超える時間の表示自体は、「[h]:mm」等の書式設定で可能なことは
調べて分かったのですが、今回のプログラムで実行する方法がわかりません。
 
いろいろと調べて、現状は以下のように記述をしており、時間の計算はできる状況です。
 
Private Sub Worksheet_Change(ByVal Target As Range)
Dim v As Variant
Dim s As String
 
' If Target.Address(False, False) <> "I2:I3" Then
' 'Target.Address(False, False) = "I3" Then
' Exit Sub
' End If
    If Target.Address <> Range("I2").Address Then
        If Target.Address <> Range("I3").Address Then
         
            Exit Sub
        End If
    End If
     
    v = Target.Value2
     
    If IsNumeric(v) = False Then
        Exit Sub
    End If
     
    s = Format(v, "0000")
 
Dim h As String '時を取得
Dim m As String '分を取得
Dim d As String '日付を指定
 
    h = Mid(s, 1, 2)
    m = Mid(s, 3, 2)
     
    '24時間を超える入力
    If h > 23 Then
        d = "1900/1/2 "
        h = h - 24
    Else
        d = "1900/1/1 "
    End If
     
    '2桁ずつに分けて、間に「:」を入れる
    '時刻の形に整形
    s = d & h & ":" & m
     
    'イベントの発生を停止
    Application.EnableEvents = False
     
    Target.Value = DateValue(s) + TimeValue(s)
     
    'イベントの発生を再開
    Application.EnableEvents = True
     
End Sub
 
どうか、アドバイスをお願いします。
 

回答
投稿日時: 19/09/30 11:10:58
投稿者: mattuwan44

引き続きの質問なら、リンクを貼りつけていただけるとより親切です。
前回の質問の内容及びどうやって解決にされたかがよくわかってないので、、、
 
Timevalue関数では、
 
26:30
 
という文字が時間に変換できないということですね?
ならば、自分で計算するまでです。
 
基本的に日付型のデータは、
1日が1です。
なので、1時間は1÷24の値
1分は、1÷24÷60です。
つまりセルの内部では小数で管理されてます。
 
なので
26÷24+30÷24÷60
の結果をセルに入れてやればOKです。

回答
投稿日時: 19/09/30 13:14:01
投稿者: sk

引用:
h = Mid(s, 1, 2)
m = Mid(s, 3, 2)

引用:
Target.Value = DateValue(s) + TimeValue(s)

Target.Value = TimeSerial(h, m, 0)
 
--------------------------------------------------------------
 
以上のようなコードでも充分かと。

回答
投稿日時: 19/09/30 14:25:00
投稿者: WinArrow
投稿者のウェブサイトに移動

難しく考えていませんか?
24時を超えているかどうか判断することもないと思いますが・・・
  
元の値は、文字列で入力するんですよね?
  
   
2640と入力する場合
セルの表示形式を文字列で設定しておきます。
  
次のような感じで対応できると思います。
  
Private Sub Worksheet_Change(ByVal Target As Range)
 Dim DATA As String, H As Long, M As Long
   
     If Target.NumberFormatLocal = "@" Then
         DATA = Target.Value
       
         H = DATA / 100
         M = DATA Mod 100
         Target.NumberFormatLocal = "[h]:mm"
         Target.Value = H & ":" & M
     End If
   
 End Sub
 
余談
表示形式が「[h]:mm」で設定されているセルに対して
.Value2
で取得するのは、おすすめできません
.Text
で取得したほうがよいでしょう

投稿日時: 19/10/01 17:24:11
投稿者: TI

皆様、ご回答ありがとうございます。
 
mattuwan44様
 
リンクを張ると言う事は全く考えにありませんでした。申し訳ありません。
 
'イベントの発生を停止
    Application.EnableEvents = False
     
    Target.Value = 26÷24+30÷24÷60
     
    'イベントの発生を再開
    Application.EnableEvents = True
 
としてみましたが、(どのようにすればよいかがわからなかったので)、「26:30」とは
ならず、「64200:00」?(生活ではありませんが)のような表示になってしまいました。
 
sk様のご提示いただいた方法でうまくいきました。
 
WinArrow様の方法の場合はまだ試してはいないのですが、こちらの場合ですと
Bookを閉じるタイミングなどで、表示形式を変更するのでしょうか。
また、一度入力後に再度変更する場合などは、どのようなタイミングで表示形式を
変更すべきなのでしょうか。
 
質問ばかりで申し訳ありませんが、よろしくお願いします。
一旦、現状の問題は解決しております。
 
 
 
 

回答
投稿日時: 19/10/01 18:11:40
投稿者: WinArrow
投稿者のウェブサイトに移動

>一度入力後に再度変更する場合などは、どのようなタイミングで表示形式を
変更すべきなのでしょうか。
 
入力後は、[h]:mmの形式いなっているので、
変更する場合は、 [h]:mm形式で入力すればよいのでは・・・・
 
同じセルを文字列と時刻の両方で使おうとしていることが間違いの元です。
文字列で入力するセルと時刻のセルは別にするようにならないのですか?

投稿日時: 19/10/02 07:24:19
投稿者: TI

ご回答ありがとうございます。
 
[h]:mmで入力の場合、「:」を入力しないといけないので、それを避ける方法を
いろいろと模索をしているところでした。
 
入力セルを分けるということも、全く考えていませんでした。
 
今までいただいたアドバイスをもとに、もう少し、シンプルに考えてみたいと思います。
 
ありがとうございます。