Excel (VBA)

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

 
(指定なし : 指定なし)
セルに入力された文字列を取得する方法
投稿日時: 21/01/10 10:27:12
投稿者: takodo

セルに
0110 と入力したら 21/01/10(日)
1001    21/10/01(金)
200131    20/01/31(金)
と、いうようにしたいと考えています。
 
最初の入力された文字列を取得を取得するところでつまずいてしまいました。
B列への入力を考えています。
B列の書式設定は"yy/mm/dd(aaa)"としています。
 
次のようにコーディングを始めましたが、
4文字の入力か否かのところで行き詰まってしまいました。
「0110」と入力すると「110」
となってしまいます。
 
ここができたら、
4文字の時は頭に「21]を付ける
その数値の妥当性を判断する
という様な処理を行っていきたいと思っています。
入力文字の4文字判定ができなければ先へ進めません。
 
どうかよろしくお願いいたします。
 
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim colName, colNo
    Dim re As New RegExp
    Dim inputDat As Variant
    colNo = Selection.Column
    re.Pattern = "\d{4}"
    re.Global = True
    colName = ColAddr(colNo)
    Target.NumberFormatLocal = "@" '"文字列"
    inputDat = Target.Text
    MsgBox ("入力しました inputDat=" & inputDat & " colName=" & colName)
    If colName = "B" Then
        If re.test(inputDat) Then
            MsgBox "数字4文字"
        Else
            MsgBox "数字4文字でない"
        End If
    End If
    Target.NumberFormatLocal = "yy/mm/dd(aaa)" '"ユーザー定義書式"
End Sub
 

回答
投稿日時: 21/01/10 13:20:23
投稿者: WinArrow
投稿者のウェブサイトに移動

横着しない方が、操作上も、以後のメンテナンスでも
 よっぽど簡単だと思いいますが・・・・
  
 >「0110」と入力すると「110」
これは当たり前です。
 先頭に「0」をプログラムで認識(取得)するためには、表示形式を「文字列」に設定するしかありません。
 
A列セルに
1/10
入力すると
2021/01/10と解釈されます。
 
どうしてもというのでしたら
A列セルを文字列に設定することです。

回答
投稿日時: 21/01/10 13:23:56
投稿者: WinArrow
投稿者のウェブサイトに移動

> Target.NumberFormatLocal = "@" '"文字列"
 
このコードは、入力する前でないと無意味
従って、不要です。

回答
投稿日時: 21/01/10 13:47:22
投稿者: WinArrow
投稿者のウェブサイトに移動

表示形式をセル単位に設定していますが、
列で設定したほうがよいです。
処理速度、ファイル容量的にもメリットがあります。
 
なぜ、」VBAで対応しようとしているかわかりませんが、
予めA列を文字列に設定します。
B列に表示形式:ユーザー定義 yy/mm/dd(aaa)に設定しておきます。
 
B列セルの数式
=IF(LEN(A2)=4,DATE(2021,LEFT(A2,2),RIGHT(A2,2)),IF(LEN(A2)=6,DATE("20"&LEFT(A2,2),MID(A2,3,2),RIGHT(A2,2)),"エラー"))
 
で対応できます。
 

投稿日時: 21/01/10 13:49:49
投稿者: takodo

WinArrow さんの引用:

 >「0110」と入力すると「110」
これは当たり前です。
 先頭に「0」をプログラムで認識(取得)するためには、表示形式を「文字列」に設定するしかありません。

そうですか。
早速の解答をありがとうございます。

回答
投稿日時: 21/01/10 14:04:08
投稿者: WinArrow
投稿者のウェブサイトに移動

今更・・・・
 
コードを眺めていたら
B列セルに、セットするところが見当たらない・・・??
見落としていたらごめんなさい
 
もし、
21/01/10(日)
という値をセットしていたら(セットするつもり)
表示形式で yy/mm/dd(aaa) をセットしているが無意味です。

回答
投稿日時: 21/01/10 14:46:25
投稿者: simple

既に指摘されていますが、
"0102"と入力すると、そのセルが文字列書式でなければ、
Excel君が気を利かせて、 102と解釈してくれるのですね。
こういうのは「小さな親切、大きなお世話」と感じる時もありますね。
 
別手法ですが、ダブルクリックイベントプロシージャとInputboxメソッドを使うと、
上記の話は避けることができますね。
 

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Dim s As String
    Dim d As Date
    
    Cancel = True
    If Target.Column <> 2 Then Exit Sub

    s = Application.InputBox("4桁の数値を入力", Type:=2)
    If Len(s) <> 4 Or Not IsNumeric(s) Then
        MsgBox "4桁の数字を入れてください"
        Exit Sub
    End If
    
    d = DateSerial(Year(Date), Val(Left(s, 2)), Val(Right(s, 2)))
    Application.EnableEvents = False
    Target.NumberFormatLocal = "yy/mm/dd(aaa)"    '"ユーザー定義書式"
    Target.Value = d
    Application.EnableEvents = True
End Sub

回答
投稿日時: 21/01/10 14:52:13
投稿者: simple

どうしてもChangeイベントプロシージャだというなら、こうですか。
事前にB列を文字列書式にしておいてください。
また、複数のセルを同時に変更することは考慮外です。(コピーペイストで更新するなど)
 

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim colNo As Long
    Dim re As RegExp
    Dim s As String
    
    colNo = Target.Column
    If colNo <> 2 Then Exit Sub

    Set re = New RegExp
    re.Pattern = "\d{4}"
    re.Global = True
    
    s = Target.Text
    If re.test(s) Then
        Target.NumberFormatLocal = "G/標準"
        Application.EnableEvents = False
        Target.Value = DateSerial(Year(Date), Val(Left(s, 2)), Val(Right(s, 2)))
        Application.EnableEvents = True
        Target.NumberFormatLocal = "yy/mm/dd(aaa)"
    Else
        MsgBox "数字4文字ではない"
        Application.EnableEvents = False
        Target.ClearContents
        Application.EnableEvents = True
    End If
End Sub

投稿日時: 21/01/10 14:54:18
投稿者: takodo

WinArrow さんの引用:

B列セルに、セットするところが見当たらない・・・??
もし、
21/01/10(日)
という値をセットしていたら(セットするつもり)
表示形式で yy/mm/dd(aaa) をセットしているが無意味です。

B列のセルに入力された値−Target
と、いうことでコーディングしたつもりですが・・・
 
B列全体に  yy/mm/dd(aaa) を設定しています。
そして、B列に値が入力されたときの処理としてコーディングしています。
そのとき、Targetの値が yy/mm/dd(aaa) で取得されてしまうので
一端 Target.NumberFormatLocal = "@" '"文字列" として、
最後に、書式を戻す処理を行ったのですが。
 
セルにキーボードから打鍵されたキーの値そのものを取得することはできないようですね。

投稿日時: 21/01/10 14:54:30
投稿者: takodo

WinArrow さんの引用:

B列セルに、セットするところが見当たらない・・・??
もし、
21/01/10(日)
という値をセットしていたら(セットするつもり)
表示形式で yy/mm/dd(aaa) をセットしているが無意味です。

B列のセルに入力された値−Target
と、いうことでコーディングしたつもりですが・・・
 
B列全体に  yy/mm/dd(aaa) を設定しています。
そして、B列に値が入力されたときの処理としてコーディングしています。
そのとき、Targetの値が yy/mm/dd(aaa) で取得されてしまうので
一端 Target.NumberFormatLocal = "@" '"文字列" として、
最後に、書式を戻す処理を行ったのですが。
 
セルにキーボードから打鍵されたキーの値そのものを取得することはできないようですね。

回答
投稿日時: 21/01/10 15:08:47
投稿者: チオチモリン

>1001    21/10/01(金)
>200131    20/01/31(金)
 
のような入力が前提なら、6文字判定でもいいんじゃないですか?

投稿日時: 21/01/10 15:17:16
投稿者: takodo

simple さんの引用:
どうしてもChangeイベントプロシージャだというなら、こうですか。

ありがとうございます。
想定した動きとなりました。
ただ、
訂正すると、
書式が文字列になっていないので動作しなくなります。
難しいです。

投稿日時: 21/01/10 15:21:44
投稿者: takodo

チオチモリン さんの引用:
>1001    21/10/01(金)
>200131    20/01/31(金)
 
のような入力が前提なら、6文字判定でもいいんじゃないですか?

ただ、少しでもキータッチを減らそうと考えたもので・・・
そもそも、キータッチを減らすことを考えなければ
20/01/31
と、入力すれば良いことになりますので。
更に、この正規表現の扱いをマスターすると多くのことに応用できそうですごく魅力を感じています。
そのためには、キーボードからの入力値をそのまま取得できればよいと思うのですが・・・

回答
投稿日時: 21/01/10 16:16:03
投稿者: チオチモリン

↓のような意味なんですけど。 意味違いました?
 
Private Sub Worksheet_Change(ByVal Target As Range)
    MsgBox IIf(Len(Target.Value) = 6, "数字4文字でない", "数字4文字")
End Sub

回答
投稿日時: 21/01/10 17:21:12
投稿者: simple

6桁の話を読み飛ばしていました。失礼。
 
基本的なことを理解されたほうがいいですよ。
yy/mm/dd(aaa)の書式設定されたセルに入力すべきは、
Date型の数値です。実体は整数です。
それを“0102"といった文字列を入力するのはNGです。
 
一旦Inputboxなどで受けてDate型に変更したうえで
当該セルに入れるのが自然でしょう。
または別の入力セルを使い、当該セルには、そのセルを参照し、
文字列をDate型に変換する数式を入れておくかです。

回答
投稿日時: 21/01/10 17:21:57
投稿者: WinArrow
投稿者のウェブサイトに移動

すみません
A列に入力して、B列に転送しえいるものと勘違いしていました。
 
B列セルに入力したデータを変換したい
ということですね
 
参考コードを紹介します。
予めB列セルは、文字列に設定しておきます。
B列セルを一旦文字列変数に入れて、桁数判定後、値をセットする前に表示形式をセットします。
 

Private Sub Worksheet_Change(ByVal Target As Range)
Dim DAtA As String

    If IsDate(Target.Value) Then Exit Sub
    DAtA = Target.Value
    Select Case Len(DAtA)
        Case 4
            Target.NumberFormatLocal = "yy/mm/dd(aaa)"
            Target.Value = DateValue("2021/" & Left(DAtA, 2) & "/" & Right(DAtA, 2))
        Case 6
            Target.NumberFormatLocal = "yy/mm/dd(aaa)"
            Target.Value = DateValue("20" & Left(DAtA, 2) & "/" & Mid(DAtA, 3, 2) & "/" & Right(DAtA, 2))
        Case Else
            MsgBox "桁数が不正です"
    End Select

End Sub

回答
投稿日時: 21/01/10 17:27:31
投稿者: WinArrow
投稿者のウェブサイトに移動

>B列のセルに入力された値−Target
>と、いうことでコーディングしたつもりですが・・・
 
でも、値が変更されていない状態で、表示形式だけ変更したら、どうなるか検証してみましたか?
例えば、1110 とか201112 の状態で yy/mm/dd(aaa) に変更してみてください。

回答
投稿日時: 21/01/10 17:37:11
投稿者: WinArrow
投稿者のウェブサイトに移動

追伸
 
あたしの参考コードは、かなり手抜きがあるので、
例えば、「B列セル以外はスキップするとか」必要な部分は追加してくださいね。

回答
投稿日時: 21/01/10 17:53:06
投稿者: チオチモリン

>yy/mm/dd(aaa)の書式設定されたセルに入力
そうだったんですか
 
初レスで
>0110」と入力すると「110」となってしまいます。
とあったので
>yy/mm/dd(aaa)の書式設定されたセルに入力
とは考えていませんでした。
その後の展開で話は進んでいたんですね。
失礼しました。

投稿日時: 21/01/10 18:20:06
投稿者: takodo

多くの方から、多くのことを教えていただき感謝です。
教えていただいたコードで動作を確認しています。
 
simpleさんの言われるように
>"0102"と入力すると、そのセルが文字列書式でなければ、
>Excel君が気を利かせて、 102と解釈してくれるのですね。
>こういうのは「小さな親切、大きなお世話」と感じる時もありますね。
と、つくづく思います。
エクセルが、便利ツールとして提供されてきたから仕方ないのかな。
表面的な処理が重要視されすぎてしまう。

回答
投稿日時: 21/01/10 19:59:50
投稿者: K.Hiwasa
投稿者のウェブサイトに移動

横から失礼します。
「110」を「0110」で処理するのは駄目でしょうか。
3桁入力と4桁入力を区別できず、「0110」入力も「110」入力もOKになってしまいますが。
エラー処理や表示処理を省いて、しかも少し雑ですが、書いてみました。
参考になれば。
 

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim re As Object
    Dim strDate As String
    
    Set re = CreateObject("VBScript.RegExp")
    re.Pattern = "^\d+$"
    re.Global = True
    
    If re.Test(Target.Value2) = True Then
       Target.NumberFormatLocal = "yy/mm/dd(aaa)" '"ユーザー定義書式"
       If CLng(Target.Value2) >= 10000 Then
          'yymmdd形式と見做す
          strDate = CStr(Target.Value2 + 20000000)
       Else
          'mmdd形式と見做す
          strDate = CStr(Target.Value2 + 20210000)
       End If
       Application.EnableEvents = False
       Target.Value = DateValue(Left$(strDate, 4) & "/" & Mid$(strDate, 5, 2) & "/" & Right$(strDate, 2))
       Application.EnableEvents = True
    End If

End Sub

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

話は違うが
↓の件で
https://www.moug.net/faq/viewtopic.php?t=80151
 
 
0!/00!/00
という表示形式で解決した
ということでよろしいのでしょうか?
 
これは単純に見た目だけのことで、データとして、日付になっているわけではないので、
計算には使えません。

回答
投稿日時: 21/01/10 22:01:32
投稿者: WinArrow
投稿者のウェブサイトに移動

>B列の書式設定は"yy/mm/dd(aaa)"としています。
この状態で
>「0110」と入力すると「110」
>となってしまいます。
という説明には、違和感があります。
どのような方法で確認したのでしょうか?
 
状況を正確に伝えていただきたいですね・・・・
 そうでないと、空回りしたり、質問者の意図と異なる回答が付くことになります。
 
 
表示形式は、見た目を設定するもので、「値」には影響は与えません。
ですから、予め「yy/mm/dd(aaa)という表示形式が設定してあるセルに
「110」を入力した時
「値」はもちろん「110」です。
コード実行の途中で表示形式を「@」に変更していますが、「値」が変わるわけではないです。
 

投稿日時: 21/01/11 06:30:24
投稿者: takodo

ありがとうございました。
これです。
このような処理ができないものかと思っていました。
 

K.Hiwasa さんの引用:

    If re.Test(Target.Value2) = True Then

Value2というプロパティーがあったんですね。
 
皆さんのおかげで、
入力値、と表示値の処理の歯科など
いろいろなことがよくわかりました。?
−−−よくわかったつもりです。
ありがとうございました。

投稿日時: 21/01/11 06:36:57
投稿者: takodo

WinArrow さんの引用:
https://www.moug.net/faq/viewtopic.php?t=80151
0!/00!/00
という表示形式で解決した
ということでよろしいのでしょうか?
これは単純に見た目だけのことで、データとして、日付になっているわけではないので、
計算には使えません。

当初、これで解決したと思ったのですが、
おっしゃられるように、計算できません。
そこで、VBでの処理でできないものかと挑戦したのですが
自分だけでは行き詰まり
VBの方で、相談させてもらいました。
ご指摘ありがとうございました。

回答
投稿日時: 21/01/11 09:54:40
投稿者: WinArrow
投稿者のウェブサイトに移動

 
参考資料
セルの「Value」「Text」「Value2」プロパティの違いを理解しましょう。
その上で、どのようにセルに入力すればよいか、および、その使い方を考える参考にしてください。
  
Sub test()
Dim myCell As Range
 
    Set myCell = Range("A1")
    Debug.Print "@表示形式:標準、201120入力"
    With myCell
        .NumberFormatLocal = "G/標準"
        .Value = "201120"
        .NumberFormatLocal = "0!/00!/00"
        GoSub Print_Prc
    End With
     
    Set myCell = myCell.Offset(1)
    Debug.Print "A表示形式:標準、2020/11/20入力"
    With myCell
        .NumberFormatLocal = "G/標準"
        .Value = "2020/11/20"
        .NumberFormatLocal = "yy/mm/dd(aaa)"
        GoSub Print_Prc
    End With
     
    Set myCell = myCell.Offset(1)
    Debug.Print "B表示形式:文字列、201120入力"
    With myCell
        .NumberFormatLocal = "@"
        .Value = "201120"
        .NumberFormatLocal = "yy/mm/dd(aaa)"
        GoSub Print_Prc
    End With
     
    Set myCell = myCell.Offset(1)
    Debug.Print "C表示形式:文字列、201120入力"
    With myCell
        .NumberFormatLocal = "@"
        .Value = "201120"
        .NumberFormatLocal = "0!/00!/00"
        GoSub Print_Prc
    End With
     
    Set myCell = myCell.Offset(1)
    Debug.Print "D表示形式:標準、0120入力 """
    With myCell
        .NumberFormatLocal = "G/標準"
        .Value = "0120"
        .NumberFormatLocal = "yy/mm/dd(aaa)"
        GoSub Print_Prc
    End With
     
    Set myCell = myCell.Offset(1)
    Debug.Print "E表示形式:文字列、0120入力 """
    With myCell
        .NumberFormatLocal = "@"
        .Value = "0120"
        .NumberFormatLocal = "yy/mm/dd(aaa)"
        GoSub Print_Prc
    End With
    Exit Sub
Print_Prc:
    With myCell
        Debug.Print .Address(0, 0) & ":Value = " & .Value
        Debug.Print .Address(0, 0) & ":Text = " & .Text
        Debug.Print .Address(0, 0) & ":Value2 = " & .Value2
    End With
    Return
         
End Sub

投稿日時: 21/01/11 09:58:52
投稿者: takodo

WinArrow さんの引用:
参考資料

すごく丁寧なコードをありがとうございます。
これから、しっかりと動作を確認させていただきます。

回答
投稿日時: 21/01/11 11:16:35
投稿者: WinArrow
投稿者のウェブサイトに移動

面白テスト
 

Sub test()
Dim DT

    With Range("A1")
        .NumberFormatLocal = "0!/00!/00"
        .Value = "0100"
        Select Case Len(.Value)
            Case 3, 4
                DT = Split(.Text, "/")
                .NumberFormatLocal = "yy/mm/dd(aaa)"
                .Value = DateSerial(2021, DT(1), DT(2))
            Case 6
                DT = Split(.Text, "/")
                .NumberFormatLocal = "yy/mm/dd(aaa)"
                .Value = DateSerial(DT(0) + 2000, DT(1), DT(2))
            Case Else
                MsgBox "桁数エラー"
        End Select
    End With
End Sub

投稿日時: 21/01/11 12:44:10
投稿者: takodo

更に、貴重なコードの提示、ありがとうございます。
何故か、無限ループに入ってしまいます。
じっくりと調べて見ます。
当初の目的はおかげで達成できました。
一端、閉じさせていただきます。
みなさん、本当にありがとうございました。