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

解決済

質問者：shibisei
質問日時：2010/02/17 16:33
回答数：13件

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

通報する

この質問への回答は締め切られました。

質問の本文を隠す

回答 (13件中11～13件)

ベストアンサー優先
最新から表示
回答順に表示

No.3

回答者： okormazd
回答日時：2010/02/17 20:13

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

ということなので、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は適宜変更のこと。

この回答への補足

理由については、下の回答に補足しましたので参照ください。
VBAは、自動マクロ記録を中心に参考書をめくりながら作成するレベルです。系統だって勉強したわけではないので素人に毛が生えた程度とお思いください。
ですので、お書き頂いたコードについてなんとなくしか分かりません。
ワークシートのチェンジイベント使って、入力したセルの値に合わせて型を変更している様ですが、Application.EnableEventsのtrueとfalseの位置が悪いのでしょうか？うまく稼動しません。
試してみたのは、適用rangeをA10のみとし、A10に"=A1&A2"、A1に"1"、A2に"2"を入力しました。A10には"12"が入力されますが、A10に"10:00"を入力すると、やはり文字列ではなくシリアル値に変更（表示は10:00）されてしまいました。

補足日時：2010/02/17 20:55

通報する

- 0
- 件

通報する

No.2

回答者： Wendy02
回答日時：2010/02/17 18:06

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

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

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

補足日時：2010/02/17 20:50

通報する