EXCELでコロン「：」を使用した時にシリアル値にならないようにしたい

Question

EXCEL2007（他ヴァージョンは分かりません）にて、コロンを使用した時刻を入力すると、勝手に書式が変わって、表示は時刻のままですが、値がシリアル値になってしまいます。これが機能しないようにしたいです。
元々数式が入力されているセルで、必要に応じて時刻を手入力しているので、書式を文字列にするのは不可です（手入力後はマクロで元の数式を入れてます）。
要は通常は数式を使用し、コロン「:」を使用して入力した時は文字列としたいのです。
コロンを使用しないで、例えば15:00を1500のように入力すれば、目的は果たせるのですが、他の（EXCELの知識がない）作業者が使用するファイルのためどちらの入力方法でも稼働するようにしたいのです。同じ理由でシングルクォートの使用も不可です。
条件付き書式やTEXT関数を使用しても、シリアルへの変換後に機能するためかうまくいきませんでした。

ASIMOV · Answer

１．現在の書式は何になってるのでしょうか？
２．その数式などんな数式ですか？

Wendy02 · Answer

この前の質問も同じようなものがありました。

数字と「：」が入った一部の数字が、シリアル値にはなるのですが、それを、型のキャスティングといいます。本来は、それを起させないということが、VBAとして必要かどうか、という疑問があります。

ご質問には、必要であるという、その理由が抜けているようです。

>同じ理由でシングルクォートの使用も不可です。
シングルクォート(')を、Rangeオブジェクトの値と共に入れた後は、文字列になります。こうすると、シングルクォート(')は、PrefixCharacter プロパティに入りますから、その後は、見えるけれども、印刷等には出ません。また、CSV にも表れません。この前の質問者さんも、これを認めようとしなかったけれども、もともとLotus123との互換性のある書式文字列です。それがダメだという理由はなぜなのでしょうか、Excelでは、ある程度は、こちらは詳しいと思っていますが、どうも分かりません。それとも、Excelの仕様が変わったとでも言うのでしょうか。
'-------------------------------------------
Sub Test1()
With Range("A1")
　 .Value = "'" & "11:10"
　 If InStr(1, .Value, "'", vbBinaryCompare) > 0 Then
　　　MsgBox "シングルクォートは存在します。", vbInformation
　 End If
End With
End Sub

もし、これで、メッセージが出るのでしょうか？
PreFixCharacter に入っていれば、このシングルクォートを検索や置換出来ないはずです。

その入力した部分を、数式バーで、再取得すれば、シングルクォートは出せるはずですが、そこまで必要かどうかです。

それを難しい方法で解決するというのは、理由が書かれていない以上、私が間違っていなければ、何かご質問者さんに誤解があるように思っています。しかし、それ以上に、VBAは、Textプロパティというもので、表示を取る方法があるので、特別なことがない限りは、そのままでも良いはずなのです。私は、マクロ中心の回答者ですが、特別な方式が必要になったことはありません。

あえて、それをするなら、以下のようになります。

'-------------------------------------------
Sub Test2()
With Range("A2")
　 .ClearFormats
　 .Value = "11:12" & Chr(9)　'このように特別なコードをおけばキャスティングは働きません。
End With
End Sub

okormazd · Answer

「手入力後はマクロで元の数式を」
ということなので、VBAはいくらか使えるのだと思います。
で、
何でこのようにしなければならないのか理解に苦しみますが、
どうしてもというなら、
下記のようにすればよろしいのではと思います。
理由がいまいち不明なので、これでいいかどうかは、わかりません。
そのシートモジュールに、イベントプロシージャ、
Private Sub Worksheet_Change(ByVal Target As Range)
Dim isect As Range, mystr As String, c As Integer
Application.EnableEvents = False
Set isect = Application.Intersect(Range("A1:A10"), Target)
If isect Is Nothing Then End
c = InStr(Target.Text, ":")
If c > 0 Then
Target.NumberFormat = "@"
mystr = Format(Target.Text, "hh:mm:ss")
Range(Target.Address) = mystr
End If
Application.EnableEvents = True
End Sub
適用rangeは適宜変更のこと。

ASIMOV · Answer

こんなのはどうでしょう．．．

図参照
その１
コントロールツールボックスからテキストボックスを D2 の辺りに貼り付けています
コードの表示で、
changeイベントに
Private Sub TextBox1_Change()
　　Cells(2, "A") = "'" + TextBox1.Text
End Sub
とします

その２
作業セル（B5とC5）を使って、時と分を分けて入力してもらいます
A5 には =B5&":"&C5 と入れておきます

Wendy02 · Answer

#2の回答者です。

>ミス防止や効率化のためにEXCELの関数とVBAで自動化したものです。

補足された内容で、残念ながらシングルクォート不可の因果関係自体は理解出来ませんでしたが、ただ、なんとなく事情は想像できます。

ちょっと長くなりますが、私の経験談から書かせていただきます。何かの参考になれば幸いです。

まったくExcelを知らない人に使わせようという前提なら、今のスタイルは、まったく違うだろうと思います。特に、中高年の人に使わせようとすると、まったく想定できない使い方に出会うことがあります。それで、それを教えようと、入力を見ていたりしても、プライドを傷つけるというのか、怒り始めるので、手が付けられないのです。それを急ぐので若い人に頼んだら、仕事を奪うのか、と言い始めます。それも、1週間程度、パソコン教室に通ってもらっても、おまけに、私の使っていた設計用のテキストを無断で持ち出しても、その程度でした。それなのに、自分の言葉が、東京の方言でさえ気がつかないので、IMEが変換できないと言うレベルの人でした。本当に困ってしまいました。

若い人は、パソコン教室に行くこともなく、1週間、毎日、PCの前でなんとなく遊んでいるなって放っておいたら、もう、1年やっていた人よりも、上手にできるようになっていました。私は、その人があまりに早く覚えてしまい、怖くなってしまいました。

ご質問者さんの抱いているイメージと、Excelを使わせるというイメージとにギャップがあることなんだろうと思います。私は、十数年も、こんなQ&A掲示板のサポートで、時々、表計算の機能を使わせない、というリクエストを言う人に時々出会うことがあります。キャスティングは、Microsoft 系の表計算としての、利点であり欠点でもあります。この機能は、1980年代からの機能ですから、今更、しょうがないです。

もし、VBAのある程度の技術力があるなら、数式との組み合わせ、という方式は、完全にやめたほうが良いようです。入力アプリケーション・システムという形にして、Excelのセルやコマンドは、ほとんどさわらせないという方式にします。

UserForm にするのか、#4さんでもサンプルが出ていますが、埋め込み型のテキストボックスや他のコントロールに入力する方法で作ったほうがよいです。場合によっては、Excelのメニューなどは、一切隠して、ボタンで保存、終了まで作ってしまいます。もちろん、シートタブも隠して、ボタンのクリックでシートの切り替えを作ります。

本来、Excelを使う人が、1500 と 15:00 自体を分からないというのでは、まったく話にならないと思います。しかし、1500で入れようが、15:00で入れようが、どちらでも「シリアル値」になるという条件自体は、ある程度マクロの技術があれば簡単なことですが、「シリアル値」自体を不可というのでしたら、それは、基礎的な誤解だと思います。

#2で書いたように、シリアル値であろうがなかろうが、セルに表示している限りは、VBAでは関係ありません。表示していない、というなら、表示自体を時間書式にしなければなりません。

これは、VBAの技術の低い人とトラブルになったことですが、ユーザーが入力した値を、完全、エラー・フリーで、その値をデート型に代入して設計できるかどうかは、技術力の差そのものです。こんなことは、最初は教わらなくては自分で開発するのは容易なことではありません。これには、多くの先人たちが、ああでもないこうでもないとした技術なのですから。

今のご質問から予想されるスタイルは、まったく新しくしないと、Excelを知らない人には使えるようなアプリケーションにはならないと思います。それを、どうするかは、ご自身がお決めなってください。こういう限られた掲示板では、手取り足取りということは出来ません。

#3さんのマクロをダメだししたようですが、少し直せば、こうしたQ&A掲示板での回答としては、目的自体はご質問者さんの満足するものが得られるように思います。また、入力の間違いに対して、補正できるようにするか、エラー表示するかレベルなら、簡単だと思います。ただし、VBAを扱うなら、最低限、シリアル値不可などという話はやめていただきたいです。時間表示している以上、文字であろうが、シリアル値であろうが、そんなことは、まったく関係ないのですから。

>VBAは、自動マクロ記録を中心に参考書をめくりながら作成するレベルです。

それは難しいかもしれません。私自身は、設計自体の問題を言っているので、考え方が違います。Excelを使えない人を対象にするのなら、ある程度の範囲で、たかがExcelでも、クラスの設計から始めないといけないようです。それでも、Excelを使う限りは、十分だと言えるものはありえないかもしれません。

お手本となる材料は、お手元にないのだろうと思います。ここの掲示板のどこを探してもないと思います。ここは設計自体を教えることはありません。いくつかのVBA専門掲示板のダウンロードサイトなどには、VBEがプロテクトせず公開してくれているものがありますから、それを参考にされてもよいと思います。目からウロコのコードの流れが分かります。

=IF(A1=191,QRCD!D14&QRCD!D15,"")

これの程度のものは、VBAで簡単に組み込めるはずでず。どの程度、私が想定できているか分かりませんが、これが、シリアル値がどうとかいう話と結びつけてしまったことが、話をややこしくしているように思います。たぶん、VBAでは、シリアル値でも、そうでなくても、そんなことは関係ないということは、理解されていないと思います。いくら、言葉を尽くしても、技術的なものですから、経験的には、この種の内容で理解してもらったことはないので、あまり深追いはしません。この種の質問に対して、仮にピンポイントの解答をしても、あまり意味がないように思います。

具体的な内容が出せないのは、今のご説明では、せっかくしていただいたにも関わらず、回答者として、理解できていないということです。しかし、それは、たぶん今の流れでは、こちらが納得するのは無理のようですから、それについてはご容赦願います。

sekkii · Answer

知識の乏しい私が口をだすのはおこがましいのですが…
「データの入力規則」を使ってはどうかな？と思いました。
見当違いでしたらごめんなさい。。

ASIMOV · Answer

結局、
１．15:00であろうが1500であろうが、文字として入って欲しい
２．後で、VBAで「式」を入れるので、書式は「文字列」では困る（標準にする）

という事ですよね

で、VBAを考えてみました
15:00と入れた時点で、EXCELが勝手に書式を「時間」にするのはどうしようもないので、VBAで強制的に戻します

書き込むセルを仮に「A2」としています
Changeイベントに記述します
---------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
　　If Target.Address = "$A$2" And Cells(2, "A") < 1 Then
　　　　Application.EnableEvents = False
　　　　mt = Hour(Cells(2, "A"))
　　　　mm = Minute(Cells(2, "A"))
　　　　Range("A2").NumberFormatLocal = "@"
　　　　Cells(2, "A") = mt & mm
　　　　Range("A2").NumberFormatLocal = "G/標準"
　　　　Application.EnableEvents = True
　　End If
End Sub
---------------------------
２行め後半で、書式が「時間」に変わったかどうかを判定してますが、ちょっと怪しいです
シリアル値が24:00までは１以下であることを利用していますが、24:00以上の値を入れるとミスります
あと、書式は、一旦「文字列」にして書込んでから「標準」に変えてます
これによって、「1500」が文字として書き込める様です

Wendy02 · Answer

こんにちは。

>社内において全工程でコロンを使わない（15:00を1500と表す）方法で統一していますので、一か所だけ異なると混乱を招きます。とは言え、派遣などで新しく入ってきた人の場合「時間を現すのはコロンを使う」のが当たり前のはずですから、慣れるまでにコロンを使って入力してしまいこともあるでしょう。

補足を読ませていただいた限りでは、私の話は見えてこないでしょうね。そういう問題というのは、入力者がどうであろうとも、最終的な出力の問題は、シリアル値であろうが、なかろうが、統一すればよい話ですから、本来は、シリアル値に統一させたほうがよいのです。出力データのエラー・フリーにするのは、まったく別問題なのです。もう少し、VBAが上達すれは、何だこんなことか、と分かっていただける話です。ExcelはExcel用のデータに合わせるほうが良いのです。出力用には別にするのです。

>これで誤った値が出力されるのを防止したいので、今回の質問をするに至りました。

おっしゃっている話というのは、見かけ上の問題だと思います。その程度でなら、どうにでもなります。
即席で作りましたので、細かい部分は見ていませんが、違うデータが入ると削除されてしまいます。
ただし、セミコロン(;)までは、受けられます。

'-------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
　　On Error GoTo EndLine
　　Dim r As Range
　　Dim myDat As String
　　'-------------------------------------------
　　Const IN_COL As Integer = 1 '1列目 ;対象列
　　'-------------------------------------------
　　Set r = Intersect(Target, Columns(IN_COL))
　　If r Is Nothing Then Exit Sub
　　If Target.Count > 1 Then Exit Sub
　　If Target.Value = "" Then Exit Sub
　　Application.ScreenUpdating = False
　　Application.EnableEvents = False
　　If InStr(1, Target.Value, ";", 1) > 0 Then
　　　　myDat = Replace(Target.Text, ";", ":", 1, , vbTextCompare)
　　End If
　　If IsDate(Target.Text) And myDat = "" Then
　　　　sTimeChange Target
　　ElseIf myDat <> "" Then
　　　　sTimeChange Target, myDat
　　ElseIf IsNumeric(Target.Value) Then
　　　 Target.NumberFormat = "000"　'数値の場合
　　　 Target.Value = Target.Value
　　ElseIf IsNumeric(Target.Text) = False Then
　　　　Target.Clear
　　End If
EndLine:
　　Application.EnableEvents = True
　　Application.ScreenUpdating = True
End Sub
'サブルーチン
Sub sTimeChange(arg As Range, Optional myDat As String)
　　If myDat = "" And InStr(arg.Text, ":") > 0 Then
　　　　myDat = (arg.Value)
　　End If
　　myDat = Format$(myDat, "hhmm")
　　arg.Clear
　　arg.Value = myDat 'ゼロサプレスしないなら、Format$(myDat,"0000")
End Sub

'-------------------------------------------
ただ、入力値がDate型になるのを防ぐだけでも良いはずです。#6さんの日付値を判定する関数があったか忘れましたが、あれば、入力規則も良いかもしれません。

Wendy02 · Answer

こんばんは。

ご質問者さんは、一度、お金を払ってでも、実際に他のプロの方に相談されたほうが良いと思います。掲示板では、プロも素人の方も、自称では話にならないし、また、掲示板では、限界があります。

=IF(A1=191,QRCD!D14&QRCD!D15,"")

もともと、この数式とそれぞれのQRCD!D1　とQRCD!D15の内容を公開はしていません。
たぶん、QRCD!D14&QRCD!D15　ここが時間値になるというわけでしょう。

片方が、Hour で、もう片方が、Minute というところでしょうか？

おそらく、Excelには、文字列というのは、明示的に、書式「@」や、書式文字列「'」を入れて不活性化させることであって、おそらくは、テキスト値だけの話だと思います。

そして、そこに「：」が入っては困るという話になっているのでしょうけれども、それは、書式で処理すればよいだけの話で、テキスト値に、VALUE関数にして、書式を「hmm」とでもすれば、「：」なしのデータは作れます。

=IF(A1=191,(QRCD!D14&":"&QRCD!D15)*1,"") とし、ユーザー定義の書式を「hmm」または、「hhmm」などとします。書式で、「：」を抜いたものにしておいて、入力の際には、「：」を入れればよいのです。

>これが何らかの理由で修正が必要な場合があるので、同じセルへ入力しないと、作業的におかしな感じがします。

表示の狂いはありません。ただし、数式は消えてしまいますが、数式を消したくなければ、このようにすればよいです。

=IF(A1=191,IF(E1="",(QRCD!D14& ":" & QRCD!D15)*1,E1),"") で、E1に時間値を入れたら、修正するという方法も可能です。

入力規則はあくまでも、入力規則です。その程度のことは事前に見当されてほしいものですが、私の考える範囲では、この件では、入力規則自体も簡単ではないと思います。

>コロンを使用した値が入力できなければ目的は果たせます。

これについては、検討しましたが、簡単ではありません。文字列で入れていれば別ですが、そのような判定を入力規則では出来ないと思います。文字列で入れているという前提では、本末転倒になってしまいます。数字に「：」を入れて、再び数字を入れれば、概ね、瞬間に少数点の数字に変わります。その中身には「：」はありません。単なる数字だけです。私自身で解決方法もありますが、それなら、こちらの考えたVBAを使えばよいのです。

>同様に「違う値が入力できない」コードを作成いただいたようですが、入力規則の方がスマートですし、

スマートとか、いただいたようですとか、感覚的な発言は、慎重にしてください。まったくのVBA初心者の方が、VBAのコードを見ただけで、ダメだしするようことは、どこの掲示板でもマナー違反です。何のために時間を割いて回答を書いているのか分からなくなります。

VBAをお使いになるなら、もう少し、VBAを勉強されることをお勧めしたいと思います。そうそう簡単には、できるようにはなりません。せっかくの貴重な回答をもらったものドブに捨てかねないものです。単にVBAの回答者の回答は、ありきたりの道具を提供しているわけではないことをご理解になると思います。私も、これで終わりにさせていただきます。

okormazd · Answer

#3です。
「適用rangeをA10のみとし、A10に"=A1&A2"、A1に"1"、A2に"2"を入力しました。A10には"12"が入力されますが、A10に"10:00"を入力すると、やはり文字列ではなくシリアル値に変更（表示は10:00）されてしまいました。」
あなたの言うことが理解できません。私のコードは、ちゃんと動くはずですが。あなたの言うように実行すれば、（表示は10:00）ではなくて、10:00:00になるはずですが、rangeだけでなく、
mystr = Format(Target.Text, "hh:mm")に換えましたか。そうすれば、表示は10:00になるでしょう。それはいいのですが、表示が10:00ではいけないのですか。どのように表示されればいいのですか。
この表示は、シリアル値ではなくて、文字列のはずですが、これがシリアル値だというのはどうしてわかったのですか。
Application.EnableEventsの場所にも間違いはありません。
もしかすると、このコードを間違えて、実行が途中で止まったりしませんでしたか。そうすると、Application.EnableEvents=Falseのままなので、次に実行しても、イベントが働かないので、シリアルのままになります。その場合は、Application.EnableEvents=Trueをイミディエイト　ウインドウで1度実行します。
10:00がシリアル値か文字列かの簡単な判定は、セルの書式を「パーセンテージ」にすれば、シリアル値なら41%とかになり、文字列なら10:00のままです。
「「うまく動かない」「目的を果たせない」と判断された場合には」自分のやり方が間違っているかもしれないと少しは考えてみることも必要ではないですか。「目的を果たせない」というなら、回答者は質問者の質問の内容から判断して回答しているので、もしそれが違っているなら、回答者が理解できるように説明していただかないとわかりません。とにかく、「何をどのように表示」させたいのですか。「10:00」ではダメで、何にしたいのでしょう。

EXCELでコロン「：」を使用した時にシリアル値にならないようにしたい

１．現在の書式は何になってるのでしょうか？

この回答への補足

この前の質問も同じようなものがありました。

この回答への補足

「手入力後はマクロで元の数式を」

この回答への補足

こんなのはどうでしょう．．．

#2の回答者です。

この回答への補足

知識の乏しい私が口をだすのはおこがましいのですが…

結局、

こんにちは。

こんばんは。

#3です。

この回答への補足

似たような質問が見つかりました

このQ&Aを見た人はこんなQ&Aも見ています

関連するカテゴリからQ&Aを探す

このQ&Aを見た人がよく見るQ&A

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング