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

質問

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

回答No.13

end-u · ベストアンサー

こんばんわ。
もう解決してるかもしれませんが、ちょっとだけ失礼します。

通常は数式で運用して、入力した時には文字列として扱いたい場合、
どんな値を入力しても構わないなら、（aとかbとかもあり？）
試してもいいかもしれないかなーっと思うのは

With Range("A10")
　　.NumberFormat = "general"
　　.Formula = "=A1&A2"
　　.NumberFormat = "@"
End With

こんな感じで書式を「標準」にして数式を設定したら、
その後、「文字列」にしておく事です。
[f2][enter]などとしてしまうとちょっと...ですが、仕様として耐えれるならこれ簡単です。

さて、以下余談です。
回答#3のokormazdさんのコードを参考にされる場合、
>If isect Is Nothing Then End
この部分のEndステートメントには気をつけてください。
何か理由があるのか知りませんが、使用の際はヘルプ参照の事。
VBEのヘルプはクイックアクセス機能があります。
Endにマウスキャレットをあてて[F1]キーです。
それに
>Application.EnableEvents = False
は
>If isect Is Nothing Then End
この行の下に持ってってください。

>では最後に、回答頂いたコードが「うまく動かない」「目的を果たせない」
>と判断された場合にはこちらはどのようにアクションすればよいか教えてください。
質疑応答を重ねる事で解決できます。
「うまく動かない」状況、「目的を果たせない」状況をできるだけ詳しく書いてください。
どこが悪いのかわからないのは質問者の方にはよくある事です。
調べる方法さえもわからない方もいらっしゃるでしょう。
その時、回答側が『経験者』であれば、その辺の機微を察し、
適確な質疑を重ねる事でどこに問題があるか探り出す事ができます。
イベントコードの貼り付け先が違ったりする事ってよくある事ですし、
EnableEvents=Falseのままだったりする事もありがちです。
今回のように、そのような可能性がないか回答側から問いかける事で大抵は解決するでしょう。
また、デバッグのやり方を提示して、質問者の方自ら確認できるようにアドバイスする回答者もいるでしょう。
http://www.asahi-net.or.jp/~ef2o-inue/vba_k/sub04_030.html

文字だけのコミュニケーションってあいかわらず難しいですね。
お互い、言わんとするところがなかなか噛み合わず、ずれてしまう事も多々あります。
なもんで『質疑応答を重ねて試行錯誤する事を続ける事』でしか解消できないと思いますよ。
もうちょっと肩の力を抜けばいいのに。
...って思ったりなんかしたりする事もないではない...かな :D

お礼

お世話になります。
回答のコードの使い方ですが、
> =IF(A1=191,QRCD!D14&QRCD!D15,"")　を
> 手入力後はマクロで元の数式を入れてます。
と書いてますので、このマクロに追加すると言うので良いのでしょうか。
今自宅ですので、実際のファイルが手元にないので試せませんが、なんとなくそれでできるような気がします。
明日試してみて結果ご連絡します。

余談の件ですが、クイックアクセス、自宅PC（2002です）では「インストールされていません」と出てしまいました。そもそも＃３さんのコードが動かないみたいなので、これも会社のPCで試してみます。

> 質疑応答を重ねる事で解決できます。
なのですが、当初の＃３さんの回答に対する補足はそのつもりで書いたつもりだったのですが、どうやら嫌なようにとられてしまって困惑していました。＃２さんの回答は、こちらで一生懸命説明しているのに、それは無視され、あるべき姿を押し付けられたような感じがして、ちょっとでした。
今回の質問のやり取りをしているときに、全く別の質問の回答をしました。こちらでは初めの質問の希望どおりにできているのですが「できない」と回答があり、正直少しばかりムッとしました。その後原因は質問者が間違えて設定していることが予想できたのでそれ以上は突っ込みませんでした（質問も締め切られてしまったのでコメントできませんでした）。これも質問者の「できない」は悪気はなかったと思います。ですが、同じようなことを自分もしてしまっているんだと気づきました。
＃２さんも＃３さんも、初めの私の補足で多分カチンと来てしまったのでしょう。そのせいで話がややこしくなってしまったようです。
今後は補足やお礼の内容に気をつけるようにしたいと思います。
本来の回答の他に、アドバイスまで頂きましてありがとうございました。

補足

お世話様です。
補足とお礼が逆になってしまいましたが…
教えていただいたコードでばっちりでした。
ActiveCell.FormulaR1C1 = "=IF(R[-22]C[-3]=191,QRCD!R[-9]C&QRCD!R[-8]C,"""")"
の代わりに、
With Range("D23")
.NumberFormat = "general"
.Formula = "=IF(R[-22]C[-3]=191,QRCD!R[-9]C&QRCD!R[-8]C,"""")"
.NumberFormat = "@"
End With
としただけで、私のしたいことがイメージ通りクリアできました。とても簡単で、すっきりしていますし他のコードへの影響もありません。
大変助かりました。

ENDステートメントについてですが、ヘルプの解説に書かれている内容についての注意でしょうか。確かにプログラムが強制終了し、初期化されてしまうようなので、他のマクロと合わせて使う場合は気をつけないといけないということですね。ありがとうございました。

これにてこの質問は締め切ることにします。
ありがとうございました。

回答No.2

Wendy02

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

数字と「：」が入った一部の数字が、シリアル値にはなるのですが、それを、型のキャスティングといいます。本来は、それを起させないということが、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

補足

目的は「他の（EXCELの知識がない）作業者が使用するファイルのためどちらの入力方法でも稼働するようにしたいのです。」と書いていましたが、分かりにくいようなので補足します。
作成しているファイルは、指定のセルへ条件（ロットの情報）を入力すると、作業条件が自動出力するというものです。元々紙面での作業条件表で作業していた（させていた）のですが、ミス防止や効率化のためにEXCELの関数とVBAで自動化したものです。
実際に使用するのは、パソコンの基本知識がほとんどない作業者になります。当然複数ですし、人事異動や採用などできた新人も使えるようにする必要があります。つまり「時刻の入力」という作業が、言ってみれば小学生でもできるレベルにする必要があるのです。
以上の理由により、「シングルクォート」は使用不可ですし、時刻の入力も15:00という入力だけでなく、1500と入力しても正常に既定の条件を出力することが望まれます。

回答の終わりにお書き頂いたコードを試してみましたが、入力された値を文字列形式になりますが、その後同じセルへコロンを使用した時刻を入力するとキャスティング（というのですね始めて知りました）は働いてしまいました。

回答No.1

ASIMOV

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

補足

書式は標準
数式は、次になります。
=IF(A1=191,QRCD!D14&QRCD!D15,"")

A1の文字数が191の時は、QRCDというシートのD14とD15をつなぎ合わせた値を出力し（これが時刻データになります）、そうでない場合は空白にしています。