VBA/エクセルの日付入力でYYYYMMDD

質問

• VBAやエクセルを使用して特定のセルに日付を入力する際、さまざまな入力がされることがあります。入力された文字列がYYYYMMDDの連続数字の形式である場合には、正しく日付として扱うことができません。それを解決するために、マクロを使用して入力規則を設定することができますが、再度YYYYMMDDの連続数字で入力するとエラーが発生する問題があります。
• 上記の問題を解決するために、マクロのコードを修正する必要があります。修正後のコードは、入力された文字列がYYYYMMDDの連続数字の形式である場合は、日付として正しく扱い、それ以外の形式の場合は日付に変換するようになります。
• さらに、日付をYYYYMMDDの連続数字で入力することは一般的ではありません。一般的には、yyyy/mm/ddやmm/dd/yyyyのような形式で入力されます。しかし、特定の場合には、YYYYMMDDの形式で入力されることもあります。

回答No.12

cj_mover

こんにちは。お邪魔します。

色んなアプローチがあっていいと思いますけれど、自分は、
Excelに備わっている日付判定をそのまま活用することを最初から考えていました。
試しに書いてみたら、とても面白かったので、回答してみることにしました。
想定している"日付擬き値"が十分かどうか判りませんけど、結構マジで保守よりの回答（のつもり）です。

まずは、テスト用サンプル。
＝＝＝＝＝＝＝＝＝
20140712
2014/7/12
7月12日
2014年7月12日
H26.7.12
2014/7/12 22:06

0
-1
ABC
'20140712
'2014/7/12
'2014/07/12
'7/12
'7月12日
'14年7月12日
'2014年7月12日
'H26.7.12
'14-7-12
'2014-7-12
＝＝＝＝＝＝＝＝＝
※　先頭の６行は何れも日付値、
※　空行は空セルの意、
※　日付に変換しようがない数値と、文字列値、
※　' は文字列値を想定したプレフィックスなので、
　　コピペした後に、普通にトリミングしてから、F2、Enter、で確定し直します。
＃実体験として日付を文字列値で入力されている場面を何度も見かけたので、一応。
※　エラー値セルがあると（メッセージと共に）処理が失敗するように書いてます。

早速、実コード。
' ' ======================================================================
Private Sub Worksheet_Change(ByVal Target As Range)　'　8675020
Dim vBuf
Dim rMark As Range, rWork As Range, c As Range
Dim sRef As String
　　Set rMark = Intersect(Columns(3), Target)
　　If rMark Is Nothing Then Exit Sub
　　Application.EnableEvents = False
　　Set rWork = Cells(1, Columns.Count).End(xlToLeft)(1, 2)　'　作業セルを指定
On Error GoTo ErrOut_
　　rMark.NumberFormatLocal = "yyyy/m/d"　'　先に表示形式を決めておく
　　For Each c In rMark
　　　　If Not IsEmpty(c) Then
'Debug.Print c.Row,
　　　　　　sRef = c.Address(0, 0)
　　　　　　rWork.Formula = "=""""&iferror(--" & sRef & "," & sRef & ")"
　　　　　　vBuf = rWork.Value
　　　　　　Select Case vBuf
　　　　　　Case 1 To 2958465　'　「日付」または「Excel数式が日付と判断できる値」（1900/1/1-9999/12/31）
　　　　　　　　vBuf = Format$(vBuf, "yyyy/mm/dd")　'　〓　As String★　　時刻値が混じっている場合の為に日付で丸める
　　　　　　Case 19000101 To 99991231　 '　　「８桁表記の日付」。「Excelが日付として扱える整数値」に限る
　　　　　　　　vBuf = Format$(vBuf, "####/##/##")　'　〓　As String★　　整数を日付値に纏める
　　　　　　Case Else　'　以上の条件で日付ではないと判断された場合は、#VALUE! エラーを返す
　　　　　　　　vBuf = CVErr(xlErrValue)　'　〓　As Error
'　　　　　　　　Range(sRef & "," & c.EntireColumn.Address & "," & c.EntireRow.Address).Select
'　　　　　　　　MsgBox c.Formula & vbLf & "違っ！"
　　　　　　End Select
'Debug.Print RegDate, TypeName(RegDate)
　　　　　　c = vBuf
　　　　End If
　　Next
ErrOut_:
　　rWork.ClearContents　'　作業セルを値消去
　　Debug.Print Me.UsedRange.Column　'　作業セルによってUsedRangeが変更された場合、元に戻す為のダミー処理
　　Application.EnableEvents = True
If Err.Number Then MsgBox "失敗"
　　Set rMark = Nothing:　　Set rWork = Nothing
End Sub
' ' ======================================================================

（C列を対象に）For Each で書いたのは複数のサンプルで確認するのが容易だからです。
単セル仕様にするにはColumns(3)をRange("D2,F2,C4")とか書き換えるだけでも対応は可能です。

セルに返すVariant変数vBufの最終的なデータ型は、Variant/StringまたはVariant/Errorにしてあります。
日付型にしないと気持ち悪いという場合は（結果は変わりませんが）★印の行でCDate()関数などで処理することになります。
その場合は、c = vBuf、に代えて、c.Value = vBuf、とした方が記述としては一貫性がある（理解され易い）と思います。

ワークシート関数のIFERROR()関数を使っていますので、Excelバージョンのよっては書換えが必要です。

作業セルを使っているのは、Excelワークシートの機能としてのデータ型の変換機能を使う為です。
当初は数式だけでなんとか出来るかと思いEvaluateメソッドでトライしてみたのですが、
実際にセルで数式の戻り値を吐かないと型のキャストをして貰えないことに気づき、諦めようかと思いました。
しかし、試しに、作業セルで数式を計算させてみると、汎用的に型の変換をしてくれることに驚きました。
作業セルを使うなんてもっての他、という考えもあるでしょうし、勿論できれば使いたくないですけれど、
この方が簡単で確実なように思えて、敢えて採用してみました。
偶然の恩恵として、Select Case にて、入力できる日付期間を比較的簡単に制限できるようになっています。
ここまでの経緯だけでも私はやってて楽しかったのですが、もう一つ、
特に意図した訳ではないのですが、軽く書き上げようと進めていたら、
Variant型変数での型のキャスト、型の変化が目まぐるしくて、というか、Variant型って本当に凄いです。
と、こんなこと書くとプログラマー志向の高いVBA開発者には敬遠されるかも、ですが、
Excel VBAらしく一般機能の活用を図ると避けて通れないのがVariant型だったり型のキャストだったりもしますね。
＃ぃゃすみません勝手に一人で楽しんじゃって。質問あげてくれたことに感謝します。

> もう1点、日付をYYYYMMDDの連続数字で入力することは普通、エクセルではあまり見たことないですが、これって一般的な方法なのでしょうか？
Excelで一般的かというと、やや特殊に近いかも知れませんが、同じExcelでもデータベース扱う機会が多いと一般的でしょうね。
私自身も８桁数字で日付を扱うことの方がやや多いです。手書きの帳票でも中にはそういうフォーマットがあったりしますし、、、。
ですから、YYYYMMDDで入力してしまう人にとっては、それは癖だったりする場合もある訳で、決して変わったことをしている訳ではないですね。
ちなみに６桁や７桁という場合は、単なるタイプミスですから、ご安心を。

お邪魔しました。それではまた。

お礼

cj_moverさん、またお世話になります。
とても勉強になる回答をありがとうございます。
ただ今回は、コピペ入力や時間まで入るケースは想定しなくてよいので、もっと簡単（というか、後任者が見てもすぐわかるようなコード）にやってみました。
回答8のお礼に書いたコードです。
ありがとうございました。
これからもよろしくお願いいたします。