Excel (VBA)

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

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

こんにちは。
 
次のようにセルに入力をした文字列を時刻に変換する方法を探しています。
 
0630 → 6:30
(「:」を入力せずに、時刻を入力したい)
 
一旦、以下のように記述をしたのですが、「型が違います」とのエラーが出てしまいます。
 
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = Range("I2").Address Then
        If Len(Target.Text) = 3 Then
            Target.Value = CDate(Left(Target.Value, 1) & ":" & Right(Target.Value, 2))
        Else
            Target.Value = CDate(Left(Target.Value, 2) & ":" & Right(Target.Value, 2))
        End If
    End If
         
End Sub
 
セルの書式設定は、時刻:6:30 としてあります。
 
デバッグで確認すると、「Target.Text」の部分を確認すると「0630」と入力したものが
「0:00」となっております。
 
入力用のセルとデータ表示用のセルを分けることも考えているのですが、1か所で
すます方法があれば、アドバイスをお願いします。

回答
投稿日時: 19/09/26 18:02:06
投稿者: mattuwan44

>1か所ですます方法があれば、
 
手動で入れてもマクロで入れてもイベントは発生します。
今回の件の例でいうと、
一旦手動で値をいれ、その値を既定の値に直して再度入れなおすので、
 
手動で0630と入力

ベント発生

値を直して再入力のプログラム起動

イベント発生

値を直して再入力のプログラム起動

イベント発生

 
と延々と無限にプログラムが起動し、
処理が終わらないか、処理しきれなくなってエラーで止まるということになります。
こういう場合は、
Application.EnableEventsプロパティを使って、
イベントの発生を無視するように書く必要があります。
 
次に、
セルに表示したい値は「時刻」でしょうけど、
セルの値(中身)は文字列型にしておくか日付型にしておくかで、
どちらでしょうか?次にその値を利用したいなら時刻としておいた方が、
計算がしやすいかなと思いますが、どうでしょうか?
その方針によって、セルの書式設定を文字列にしておくか時刻にしておくかの設定をしておかないと、
入力した直後の表示されている文字列を取得しても、
勝手にエクセル君が変えているかも知れないので、不都合が出ます。
 
ということで、
対象セルの書式設定が時刻になっているとして、
サンプルを書いたらこんな感じになります。
 
方法は一つではないと思いますが参考になれば。
 

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim v As Variant
    Dim s As String
    
    If Target.Address(False, False) <> "A1" Then Exit Sub
    v = Target.Value2
    If IsNumeric(v) = False Then Exit Sub
    
    s = Format(v, "0000")
    s = Mid(s, 1, 2) & ":" & Mid(s, 3, 2)
    
    Application.EnableEvents = False
    Target.Value = TimeValue(s)
    Application.EnableEvents = True
End Sub

 
処理の流れとしては、
セルの値を取得し、
もし、数値に変換可能であれば、
4ケタの数字の文字列に一旦変換し、
2文字目と3文字目の間に「:」を入れて文字列を連結し直し、
出来た文字を「時刻」の値に変換してセルに入れなおす。
という感じで考えてみました。

回答
投稿日時: 19/09/26 18:06:33
投稿者: WinArrow
投稿者のウェブサイトに移動

まず、セルの表示形式を文字列で設定しておきましょう
 
そこへ、
630
又は
0630
と入力します。
 
マクロは以下
Private Sub Worksheet_Change(ByVal Target As Range)
Dim JIKOKU As String
 
    If Target.NumberFormatLocal = "hh:mm" Then Exit Sub
    If Len(Target.Text) = 3 Then
        JIKOKU = Left$(Target.Value, 1) & ":" & Right$(Target.Value, 2)
    Else
        JIKOKU = Left$(Target.Value, 2) & ":" & Right$(Target.Value, 2)
    End If
     
    With Target
        .NumberFormatLocal = "hh:mm"
        .Value = JIKOKU
    End With
End Sub

回答
投稿日時: 19/09/26 19:10:12
投稿者: よろずや

「:」コロンキーが押しにくい位置にあるっていう話でしょうか。
 
ファイル−オプション−文章校正−オートコレクトのオプション−オートコレクト
で、
修正文字列→..
修正後の文字列→:
と設定しておくと、テンキーの小数点2つでコロンになります。
 
ついでに
修正文字列→:.
修正後の文字列→…
ってのも入れておくと、小数点3つで3点リーダになります。

投稿日時: 19/09/27 11:38:56
投稿者: TI

ご回答ありがとうございます。
 
mattuwan44様のご提示頂いた方法でうまくできました。
 
「:」を入力する手間を省きたかったので、助かりました。
 
今後とも、よろしくお願いします。