Excel VBA Timer など覚えておきたい日付時刻の処理

Excel VBAで日付や時刻を扱っていて、日付型の相違などで苦労した経験があるのではないでしょうか。前回記事の解説で、日付・時刻関連のマクロ処理について概ね理解いただけたと思います。
しかし、前回解説していない部分で是非覚えておきたいTimer 関数の使用法や日付型の処理について追加で解説したいと思います

くるみこ
くるみこ

お待たせしました! 今回は Timer 関数など、日付や時刻の扱いで知っておいたほうが良いと思うことを解説します(^^)/

日付や時刻いうことは、前回の続きですね!

復習してみたけど日付って難しいです!是非もっと詳しく教えてください。よろしくお願いしますm(_ _)m

前回のおさらいはこちらの記事です(^^)/ 下のカードをクリックすれば開きます

くるみこ
くるみこ

前回記事でわかったことは
・日付に関連する関数の種類がわかりました
日付関連の書式設定にFormat関数を使うことがわかりました
・シリアル値の計算方法がわかりました

【この記事でわかること
・Timer関数の使い方・利用方法がわかります

日付型の違いによる注意点がわかります

覚えておきたい便利な日付・時刻関連VBAマクロ

Timer関数を使った処理時間の測定方法

Timer 関数は「午前0時から経過秒数を表す単精度浮動小数点数型(Single)の値を返します」

特定の時間の周期でVBAマクロを実行させたい場合や、作成したVBAマクロの処理時間を計測して評価する場合等に利用します。その方法は、測定スタート箇所と測定終了箇所でTimer関数で値を記録し、その誤差で経過時間を算出測定します

VBAマクロ処理時間の測定例

次のサンプルは、ループ処理の直前の13行目で、変数 sTime に Timer 関数値を代入しています

Option Explicit
'簡単なタイマー関数使用例
Sub TimerTestSample()
    Dim i As Long, j As Long, k As Long
    Dim n As Double
    Dim sTime As Single '計測開始データ保存用
    
    Columns("B:J").Clear 'データをクリアします
    n = InputBox("処理時間にウエイトをかけます。" & vbCrLf _
                & "数値を入力してください(10で約3秒です)") * 1000000
    
    '測定スタート時のタイマー値を変数に保存する
    sTime = Timer
     
    '計測したい処理を入れる
    For i = 1 To 10
        For j = 2 To 10
            'Rnd関数で単精度浮動小数点型の乱数を発生させています
            Cells(2, j) = Int((55 - 1 + 1) * Rnd + 1)
            Cells(2, j).Interior.ColorIndex = Cells(2, j) 'セルの色変更
            Cells(4, 2) = "処理進捗状況→" & i & "(" & j & "/10)" & "/10"
            '時間調整用
            For k = 1 To n
                '単に時間調整しています
            Next
        Next
    Next
 
    '処理時間を計算してメッセージボックスに表示します
    MsgBox "マクロ処理時間⇒ " & Timer - sTime & "秒"
 
End Sub

・30行目で、その時点の Timer値 – sTime(最初のTimer値の入った変数)の誤差で処理時間を計算してメッセージボックスに表示しています
・23~25行目で、空のループを使って処理時間にウェイトをかけています
・ループのウェイトは、開始時に InputBoxを使って変更できるようにしています

★下の過去記カードでプログレスバーを動かす部分で Timer 関数を使っています

VBAマクロ実行時間にウエイトをかけるには

・先ほどのサンプルで使った空のループ処理を挟む他にもいろいろな方法があります
・次のような例も参考にしてください
上記のサンプルのように直接記入しても良いですが、Call で呼び出す方が良いでしょう

2次元のループを使用する例
'2次元のループを使用する例
Sub WaitSample_01()
    Dim i As Long, j As Long
    For i = 1 To 1000
        For j = 1 To 1000
        Next
    Next
End Sub

・ループ回数を変更したり、次元拡張したりなどが可能ですね

Timer関数を利用する例
'Timer関数を利用する例
Sub WaitSample_02()
    Dim sTime As Single
    
    sTime = Timer + 1   '現在のTimer値+1秒
    Do
        If Timer > sTime Then   '1秒以上経過するまでループ
            Exit Do
        End If
        DoEvents    'これを入れておけば途中キャンセルできる
    Loop
End Sub

ロープ中 Timer 関数を何度も実行しているんだよね

Application.Waitメソッドを使用する例
'Application.Waitメソッドを使用する例
Sub WaitSample_03()
    '現在時刻+1秒待つ
    Application.Wait (Now + TimeValue("0:00:01"))
End Sub

ウエイト時間をキッチリ指定できるのが良いですね

文字列日付を日付型に変換例

日付データが文字列になっている場合、Excelの日付処理関数が使用でません
そんな場合、VBAマクロで文字列を日付型に変換しましょう

Replace関数で文字置換方式

'文字列置換で日付型に変換する
Sub StrReplaceDate()
    Dim er As Long
    er = Range("A1000").End(xlUp).Row   '最終行取得
    '文字列を置換して日付型に変更する
    With Range(Cells(3, 1), Cells(er, 1))
        .Replace What:="年", Replacement:="/"
        .Replace What:="月", Replacement:="/"
        .Replace What:="日", Replacement:=""
    End With
End Sub

・6行目、Replace 関数を使って文字を置換えて日付型にしてしまう方法です。指定範囲を一括で変換できるので効率的です

セル書式を日付型に設定してDateValueで変換

'セル書式を日付型に設定してDateValueで変換
Sub DateFormatValue()
    Dim er As Long, i As Long
    er = Range("A1000").End(xlUp).Row   '最終行取得
    '日付型にセル書式を指定
    Range(Cells(3, 1), Cells(er, 1)).NumberFormatLocal = "yyyy/m/d;@"
    For i = 1 To er
        Cells(i, 1).Value = DateValue(Cells(i, 1).Value)
    Next
End Sub

・6行目、事前にセル範囲の表示形式を日付型に指定しておく必要があります
・7~9行目で、For文で1行ずつ処理します
・8行目で、DateValue 関数で日付データを取得して再代入しています

Findメソッドで日付を検索する例

日付を検索する場合、Excelシートにはその日付が絶対にあることがわかっているのにヒットせず原因も判らず悩んだ方がたくさんいると思います

Findメソッドで日付データを検索する場合は、日付チェックをシリアル値で行っています。セルデータが日付型で検索データも同じ日付型であればセルの表示種類が異なっていてもシリアル値が同じなのでヒットします。逆にそうなっていない(シリアル値でない)場合は、見た目が同じでもヒットしません

'Findメソッドで日付を検索する
Sub DateFindSample()
    Dim mydate As Date, tgrn As Range, myrng As Range
    Dim hitadd As String, msg As String, hitr As Long
    Set tgrn = ThisWorkbook.Worksheets("Sheet1").Columns(1)
    mydate = "2021/1/7"
    With tgrn
        'Findメソッドで検索
        Set myrng = .Find(mydate, , , xlWhole)
        If Not myrng Is Nothing Then
            hitadd = myrng.Address
            Do
                hitr = myrng.Row
                msg = msg & hitr & "行目にありました!"
                Set myrng = .FindNext(myrng)
                If myrng.Address = hitadd Then
                    Exit Do
                End If
            Loop
        Else
            msg = "ヒットしませんでした"
        End If
    End With
    MsgBox "[" & mydate & "]データは" & Chr(10) & msg
End Sub

検索データも検索される側も日付型になっていれば殆んど問題はないと思います

AutoFilterメソッドで日付を検索する例

AutoFilterメソッドで日付を検索す場合も、セルの表示種類によりヒットしないケースがあるので注意が必要です

日付種類を和暦で検索してみる

'和暦でオートフィルタ検索
Sub WaDateFilter()
    Dim er As Long
    Dim tgrn As Range
    er = Range("L1000").End(xlUp).Row   '最終行取得
    Set tgrn = Range(Cells(2, 12), Cells(er, 12))
    tgrn.AutoFilter Field:=1, Criteria1:="令和3年1月17日" '文字列で検索
End Sub

・12(L)列目に文字列の日付データをセットしておき、実行します
・7行目の検索データ「令和3年1月17日」で実行した場合、シート19行目の「令和3年1月17日」がヒットし正常です
・12(L)列目に文字列の日付データを日付書式(シリアル値)に変更して実行すると、「表示されている文字」は同じでもヒットしません

日付をシリアル値で検索する

'シリアル値日付でフィルタ検索
Sub SerialDateFilter()
    Dim er As Long
    Dim tgrn As Range
    er = Range(L1000").End(xlUp).Row   '最終行取得
    Set tgrn = Range(Cells(2, 12), Cells(er, 12))
    tgrn.AutoFilter Field:=1, Criteria1:=DateValue("令和3年1月17日") 'シリアル値で検索
End Sub

・前例とは逆の例です。こちらは7行目の DateValue関数でシリアル値を検索しています
・「令和3年1月17日」がシリアル値になっていなければヒットしません)。

AutoFilterメソッドで日付を検索する場合は、検索するデータも検索される側も同じ表示種類になっていないと問題が発生するので注意してください

スポンサーリンク

まとめ(おわりに)

くるみこ
くるみこ

Timer関数の使い方や利用方法と検索時など日付型の違いによる注意点を解説しました。 どうですか、理解できましたか?

はい! Timer関数の使い方はよくわかりました(^^)
シリアル値については見た目では判断できないので注意しなければならなということがわかりました。日付・時刻を使う場合は細心の注意が必要なんですね!

くるみこ
くるみこ

では、次回は 配列について解説していきたいと思います!
できれば自主的に予習をしてみてくださいね(^^)/

まとめと感想など

・Timer関数の使い方・利用方法がわかりました
・VBAテスト実行などでウエイトをかける方法がわかりました
・文字列日付を日付型に変換する方法がわかりました

・検索時など日付型の違いによる注意点がわかりました

マクロ(VBA)を実行する際は必ずバックアップを取ってから行ってください
・マクロ(VBA)は実行後にファイルを保存すると元に戻すことはできません!
・実行後にファイルを保存せず終了すれば、実行前に戻すことができます!


ブログランキングに参加しています(^^)応援よろしくお願いしますm(_ _)m

今後の記事について

今回の記事はいかがだったでしょうか。皆さまのお役に立てたなら幸いです(^^;
当面は「初心者向けマクロVBA」の記事を継続して書いていきます

【検討中の今後の記事内容は・・・・・】
・実務に役立つものを提供できるよう現在検討中です
・その他雑記的に「小ネタなどいろいろ」・・・・・
・今後の記事にもご期待ください(^^)/

記事のサンプルファイルをダウンロードできます

今回の記事のサンプルをダウンロードできるようにしています
過去の記事で使用したサンプルファイルがダウンロードできるページを設置しています
こちら(このリンク先)からご利用ください

スポンサーリンク

スポンサーリンク