数式バーに表示される値（文字列）を取得したい

質問

とあるセルの数式バーに表示されている文字列を取得したいのですが、
うまくいきません。

Excelが作成されたときの状況がわからない（確認することができません）のですが、
以下のようなセルがあります。

　(1) セルに表示されている値：　00:00.0
　(2) セルの書式設定：　mm:ss.0
　(3) 数式バーに表示されている値：　2012/10/6 0:00:00

このセルのvalue、value2、text、formulaをそれぞれmsgboxで表示させてみたところ、
下記のようになりました。

　value = 41188
　value2 = 41188
　text= 00:00.0
　formula = 41188

数式バーに表示される文字列が格納されているのがformulaだと思っていたのですが、
シリアル値が表示されてしまいました。

また、このセルをダブルクリックして編集状態にした後、内容を変えずにリターンキー押下で
確定させると、表示形式が "yyyy/m/d h:mm" に変更されてしまいます。

マクロの目的としては、このセルの表示形式を "yyyymmdd" に変更したいのですが、
他にも数値のデータ列があるため、このセルが「日付」であるかどうかをなるべく固定の入力値なしで
（例えば表示形式が "mm:ss.0" かどうか、などの判断をせずに）やりたいと考えています。

数式バーに表示されている文字列が取得できれば、IsDateで判定できると思ったのですが・・・

どなたかお詳しい方、ご教授よろしくお願い致します。

回答No.3

WindFaller

こんばんは。

>textプロパティの値：　36:50.6
確認できました。

>IsDateは分:秒形式でもTrueが返る仕様なのでしょうか？
はい、返ります。今回は、秒以下があったのがいけなかったのだろうと思います。
VBA自体には、秒以下の表現方法はありませんから。

個人的には、以下は、あまりすっきりしたコードではないのですが、一応、これで試してください。

'//
Sub Test1R()
　Dim c As Range
　Application.ScreenUpdating = False
　With ActiveSheet
　　For Each c In .Range("A1", .Cells(Rows.Count, 1).End(xlUp))
　　　If c.Text Like "##:##*" Then
　　　　c.NumberFormatLocal = "yyyymmdd"
　　　End If
　　Next c
　End With
　Application.ScreenUpdating = True
End Sub

お礼

何らかの方法で数式バーの表示内容が取得できない限り、
やはりそういったやり方でしのぐしかありませんよね。

とりあえず現状のまま（表示形式の指定を外出し）で
運用することにしたいと思います。

IsDateの仕様について、勉強になりました。
長らくお付き合いいただいて、ありがとうございました。

補足

確認のために試してみたところ、以下はFalseでした。

　・55:30
　・24:30

23:30 はTrueでしたので、やはりIsDateは時/分形式のものしか
有効な値とは判定しないようです。

回答No.2

WindFaller

すみませんが、補足の返事の内容が、私には、よくわかりません。

>IsDateは時刻だけを渡してもFalseになってしまうので、上記ではできないのです・
IsDate は、時刻を渡してもTrue になります。時刻も日付型(Date型）です。

これで、あなたのExcelのVBEditorでは、これは、どうなりますか？

Sub Test_IsDate()
　a = "00:00.0"
　MsgBox IsDate(a)
End Sub

こちらは、True が返りますが、そちらでは、どうなりますか？
もし、False になる場合は、何か別のものが混入している可能性があります。

http://msdn.microsoft.com/ja-jp/library/00wf8zk9(v=vs.80).aspx
-----------------------
IsDate は、Expression が[日付型]である場合、または Date に変換できる場合、True を返します。それ以外の場合は False を返します。

Date データ型は、[日付と時刻両方の値]を保持します。IsDate は、Expression が有効な日付、時刻、または日付と時刻の値である場合に True を返します。
------------------------(一部加筆)-------------

補足

数式バーに表示されている文字列：　2012/10/6 15:36:51
textプロパティの値：　36:50.6

上記のセルでFalseが返ります。

IsDateは分:秒形式でもTrueが返る仕様なのでしょうか？
現象からして、時:分形式のみなのだろうなと思っていました。

回答No.1

WindFaller

こんにちは。

>セルが「日付」であるかどうかをなるべく固定の入力値なしで
「固有の入力値」の意味だと解釈しました。

方法は、IsDate以外にも、いくつかあるとは思います。
数値を、日付に変換した時に、その日付が常識の範囲内に日付が変換されているか判定する方法などがあります。数式バーに表示されているのは、内部の数値だから、たぶん取得できないような気がします。

>マクロの目的としては、このセルの表示形式を "yyyymmdd" に変更したい
とすれば、例えば、こうなると思います。

'//
Sub Test1()
　Dim c As Range
　Application.ScreenUpdating = False
　With ActiveSheet
　　For Each c In .Range("A1", .Cells(Rows.Count, 1).End(xlUp))
　　　If IsDate(c.Text) Then
　　　　c.NumberFormatLocal = "yyyymmdd"
　　　End If
　　Next c
　End With
　Application.ScreenUpdating = True
End Sub

補足

textプロパティに入っているのは表示形式に従った値なので、"00:00.0"です。
IsDateは時刻だけを渡してもFalseになってしまうので、上記ではできないのです・・・