VBAで今日の日付を検索する方法

カレンダー
この記事は約8分で読めます。

今回はVBAで「今日の日付」を検索する方法を紹介します。

事例としてA列に日付が並べられているときに、そのなかから本日の日付が何行目にあるのかを検索する方法になります。何行目かを検知できれば同じ行のB列に対して処理したりすることができます。

A列の日付は日付データとして入力されているものとします。

方法はいくつかあるのでそれぞれの方法の注意点や、検出できなかった場合のエラー処理についてもあわせて紹介していきます。

WorksheetFunction.Match メソッドによる検索

概要

まずVBAを使わずにExcelワークシート関数でA列の日付から今日の日付を検索する方法を考えてみましょう。

A1~A10に日付がある場合、 Excelワークシート 関数ではMATCH関数を使って次のように検索することができます。

=MATCH(TODAY(), A1:A10, 0)

このMATCH関数のようにExcelワークシート関数の一部はVBAからWorksheetFunctionオブジェクトを使って呼び出すことができます。

使い方はExcelワークシート関数のMATCH関数とほぼ同じで、

WorksheetFunction.Match(検索値, 検索範囲, 照合の型)

となります。

検索値としてTODAY関数と同じように今日の日付を返す関数としてDate関数を用いますが、注意点として日付データの検索をするときには日付シリアル値に変換して指定する必要があります。

シリアル値として指定するにはCLng関数を使ってLong型に変換します。

時刻まで含めてシリアル値に変換するときはCDbl関数でDouble型に変換します

検索範囲はExcelワークシート関数ではセル番地を直接指定していますが、WorksheetFunction.MatchメソッドではVBAでのセルの指定方法に準拠した記述をします。
→例)Range(“A1:A10”)

照合の型はMATCH関数と同じく -1, 0, 1 で指定します。

rc = WorksheetFunction.Match(CLng(Date), Range(“A1:A10”), 0)

このWorksheetFunction.Matchメソッドが返すのは検索範囲の何番目かを意味する数値です。

エラー処理

WorksheetFunction.Matchメソッドでは一致するデータが見つからなかったときは実行エラーとなります。

エラー処理の一例としては On Error Resume Next で実行エラーによる停止を回避し、Err.Number によって条件分岐させる方法があります。

実例

エラー処理の方法も踏まえてA列から今日の日付を検索し、同じ行のB列に「〇」を記入するサンプルは次のようになります。

Sub sample1()
    Dim todayRow As Long
    On Error Resume Next
    todayRow = WorksheetFunction.Match(CLng(Date), Range("A1:A10"), 0)
    If Err.Number = 0 Then
        Cells(todayRow, 2).Value = "〇"
    Else
        MsgBox "今日の日付を検出できませんでした", vbExclamation
        Exit Sub
    End If
    On Error GoTo 0
    MsgBox "正常に処理が完了しました"
End Sub

Find メソッドによる検索

概要

セル範囲内で検索する方法としてFindメソッドがあります。

セル範囲.Find(what, After, LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte, SearchFormat )

引数whatに今日の日付としてDate関数を指定します。

注意が必要なのは検索対象を指定する引数LookInです。

この引数は省略可能ですが、省略すると前回検索したときの検索対象が引き継がれます。

前回「値」で検索していたら検索対象は「値」に、前回「式」で検索していれば「式」になります

つまり同じプログラムでも実行するたびに異なる現象になってしまいます。

Findメソッドを使うときには引数LookInは必ず指定するようにしましょう。

検索値をDate関数で今日の日付とした場合、検索対象は引数LookInでxlFormulasを指定して「式」を対象にします。

xlValuesで「値」を検索対象にすると、日付の表示形式によっては検索できなくなります。

表示形式が「yyyy/m/d」で「2019/7/15」のように表示されていれば検索できますがそれ以外の表示形式(「2019年7月15日」等)では検索できません。

以上を踏まえて次のように記述します。

Set rng =  Range(“A1:A10”).Find(What:=Date, LookIn:=xlFormulas, Lookat:=xlWhole)

Findメソッドが返すのは検索結果の先頭のセルを表すRangeオブジェクトです。

セルの行番号は rng.Row で取得できます。

エラー処理

Findメソッドでは一致するデータが見つからなかったときは Nothing を返します。

エラー処理としては戻り値が Nothing でないかで条件分岐する方法があります。

実例

エラー処理の方法も踏まえてA列から今日の日付を検索し、同じ行のB列に「〇」を記入するサンプルは以下のようになります。

Sub sample2()
    Dim rng As Range
    Dim todayRow As Long
    Set rng = Range("A1:A10").Find(What:=Date, LookIn:=xlFormulas, Lookat:=xlWhole)
    If Not rng Is Nothing Then
        todayRow = rng.Row
        Cells(todayRow, 2).Value = "〇"
    Else
        MsgBox "今日の日付を検出できませんでした", vbExclamation
        Exit Sub
    End If
    MsgBox "正常に処理が完了しました"
End Sub

For..Next ステートメントと配列

概要

For..Nextステートメントで検索範囲をループし、そのなかでセルの値を判別する方法です。

このときループの度にセルのRangeオブジェクトの値を直接確認していると検索範囲のデータ数が多くなったときに処理速度が遅くなってしまいます。

そこでセルの値を一旦配列変数に収めておいて、配列変数の値を確認していくようにすると処理速度を速めることができます。

処理速度については記事の最後に紹介します。

エラー処理

ループ処理のなかでセルの値を判別して一致するデータがあった場合に変数に行番号を入れるようにすれば、一致するデータがなかったときには変数が初期値(0)のままになります。

エラー処理としては変数が初期値のままかどうかで条件分岐する方法があります。

実例

エラー処理の方法も踏まえてA列から今日の日付を検索し、同じ行のB列に「〇」を記入するサンプルは以下のようになります。

Sub sample3()
    Dim rng As Range
    Dim valAry As Variant
    Dim todayRow As Long
    Dim i As Long
    Set rng = Range("A1:A10")
    valAry = rng.Value
    For i = LBound(valAry) To UBound(valAry)
        If valAry(i, 1) = Date Then
            todayRow = rng(i, 1).Row
            Exit For
        End If
    Next i
    If todayRow > 0 Then
        Cells(todayRow, 2).Value = "〇"
    Else
        MsgBox "今日の日付を検出できませんでした", vbExclamation
        Exit Sub
    End If
    MsgBox "正常に処理が完了しました"
End Sub

【参考】各手法での処理速度について

いろいろな検索方法を紹介しましたが、それぞれの処理速度について気になる方もいると思いますので参考に検証結果を紹介しておきます。

実際の処理速度は使用環境に依存するため絶対値としては意味がありません。あくまで各手法でどれくらいの差が生じるのかの比較用の参考にとどめてください。

ここでは上で紹介したsampleマクロの検索範囲をA1:A500000と50万件に増やして、今日の日付が末尾のセルA500000にある場合で測定しました。

参考としてFor..Nextステートメントで配列を使わなかった場合も測定しました。

結果は以下のとおりです。

手法処理時間
WorksheetFunction.Match メソッド0.008秒
Find メソッド0.375秒
For..Nextステートメント(配列あり)0.102秒
For..Nextステートメント(配列なし)1.891秒

最後のFor..Nextステートメント(配列なし)を除けば50万件を検索しても1秒未満なので実使用上ではそれほど気になる場面はないかもしれません。

それぞれの手法の特徴を理解して状況に応じて使い分けるのがよいでしょう。

今回の事例のように1列(または1行)のなかから1つの検索結果を得るだけならWorksheetFunction.Matchメソッドが一番有効かと思います。

複数の検索結果を得たい場合にはFor..Nextステートメントで配列を用いるのがよいでしょう。

Findメソッドは複数列、複数行のなかから検索するときに記述が簡潔になるというメリットがありますが、引数の指定の仕方などで検索結果が変わってくるので仕様をよく確認して使うようにしましょう。

コメント

タイトルとURLをコピーしました