Office TANAKA - Excel VBA Tips[日付を検索する]

ほかにもある「ときどき」失敗するケース

白状します。実は前ページのサンプルデータは、ちゃんと検索ができるように手を加えていました。ごめんなさい。あらためて、前ページと同じサンプルデータを作るところからやりましょう。

Excelを起動して、新規ブックを開きます。新しいワークシートでもいいです
1行目にタイトルを入力します
セルA2に「2010/1/16」と入力します
セルA2のフィルハンドルをセルA27(どこでもいいですけど)までドラッグします
B列の担当者名を適当に入力します

これで完成です。

さあ、さっきのマクロを試してみましょう。もちろん、正しく検索できるように、引数LookInにはxlValuesを指定します。

Sub Sample3()
    Dim FoundCell As Range
    Set FoundCell = Range("A:A").Find(What:="2010/1/24", LookIn:=xlValues)
    If FoundCell Is Nothing Then
        MsgBox "見つかりません"
    Else
        MsgBox FoundCell.Offset(0, 1)
    End If
End Sub

そんなバカな。さっきはちゃんと検索できたじゃないですか。上の作成手順でも、特に変わったことはしていません。前ページの解説は、いったい何だったのでしょう。

前ページの、ちゃんと検索できるデータと、いま上で作成したデータは1つを除いて同じです。違いはセルの表示形式です。検索できる方のデータは、表示形式を「2001/3/14」にしていました。

対して、上で作成したように、真っ新なセルに「2010/1/16」のような日付データを入力すると、標準では「*2001/3/14」という表示形式が自動的に設定されます。

[種類]リストの上2つにある「*」がついた表示形式は、Windowsのコントロールパネルで設定されている日付の表示形式です。コントロールパネルの設定を変更すると、セルの表示も変化します。対して「*」のついていない表示形式は、Excel独自の表示形式です。コントロールパネルの設定にかかわらず、常に同じように表示されます。

つまり、

Sub Sample3()
    Dim FoundCell As Range
    Set FoundCell = Range("A:A").Find(What:="2010/1/24", LookIn:=xlValues)
    If FoundCell Is Nothing Then
        MsgBox "見つかりません"
    Else
        MsgBox FoundCell.Offset(0, 1)
    End If
End Sub

という、一見間違いはなさそうなコードは、表示形式「2001/3/14」では正常に検索できますが、表示形式「*2001/3/14」では検索できない、ということです。それぞれの表示形式がどんな意味なのかは、この際大きな問題ではありません。重要なことは、表示形式が違うと検索が失敗することがあるという事実です。しかも一般的なWindowsの設定では、「*2001/3/14」と「2001/3/14」は同じに見えます。

NumberFormatLocalプロパティを調べると、「*2001/3/14」の方は"yyyy/m/d"で、「*」の付かない「2001/3/14」は"yyyy/m/d;@"が返ります。しかし、ユーザー定義の表示形式で"yyyy/m/d"を作って設定すれば、両者をNumberFormatプロパティで区別することはできません。

表示形式の話をしたついでに、もっとハッキリ違う表示形式を設定した場合、検索できるかどうか試してみましょう。実行するのは、上と同じコードです。

そりゃそうですよね。「LookIn:=xlValues」はセルに表示されているデータを検索するのですから。上図のA列には、どこにも「2010/1/24」と表示されているセルはありません。もちろん、この場合は次のようにすれば検索できます。

Sub Sample3()
    Dim FoundCell As Range
    Set FoundCell = Range("A:A").Find(What:="平成22年1月24日(日曜日)", LookIn:=xlValues)
    If FoundCell Is Nothing Then
        MsgBox "見つかりません"
    Else
        MsgBox FoundCell.Offset(0, 1)
    End If
End Sub

ということはですよ。これは、バグを生みやすい非常に重要なポイントだということです。たとえば次のような日付データがあったとします。セルの表示形式には「2001/3/14」を設定しました。ここから、Now関数を使って今日の日付を検索してみましょう。

Sub Sample5()
    Dim FoundCell As Range
    Set FoundCell = Range("A:A").Find(What:=Format(Now(), "yyyy/mm/dd"), LookIn:=xlValues)
    If FoundCell Is Nothing Then
        MsgBox "見つかりません"
    Else
        MsgBox FoundCell.Offset(0, 1)
    End If
End Sub

もし、今日が「2009年10月12日」だったら検索に成功します。

しかし、今日が「2009年10月8日」だったら失敗します。

理由は、おわかりですね。Now関数から日付を加工しているFormat関数で、書式記号として「yyyy/mm/dd」を指定しているからです。対して、セルの表示形式に設定されているのは「yyyy/m/d」です。月と日の数値が、いずれも2桁の場合は、両者は同じになりますが「10月8日」のように1桁の数値は、「mm/dd」だと「10/08」となってしまうからです。A列に「2009/10/8」はありますが、「2009/10/08」はどこにもありません。マクロを実行する日によって失敗する・・・最も原因を解明しにくいバグのひとつです。

いやはや、日付を検索するというのは大変な作業です。でも待ってください。今までは「"2010/1/24"」のような文字列を検索していました。もしかして、それがいけないのでしょうか。何しろ、相手はシリアル値です。検索語にも、きちんとシリアル値を指定すべきなのでは。なるほど、それも一理あります。では、シリアル値を返すDateValue関数を使って日付を検索してみましょう。

関数を使えばいいじゃないか >> 次ページ