【ExcelVBA】マクロで処理時間を計測するには

11分

SHARE

この記事では、処理時間を計測する方法についてご説明します。

マクロで処理時間を計測するには

マクロで処理時間を計測するには、処理の開始時刻から終了時刻を引くだけです。

なので、必要な情報は次の3つです。

  1. 処理時間を計りたい処理が実行開始した時刻
  2. 処理時間を計りたい処理が実行完了した時刻
  3. 上記の②から①を差し引いた時間

なので、まずは処理時間を計りたい処理が実行開始した時刻と、処理時間を計りたい処理が実行完了した時刻をマクロに出力させます。

イメージとしては次の通りです

STEP.1
処理時間を計りたい処理の開始直前で現在時刻を出力する
処理時間を計りたい処理の開始直前で現在時刻を出力します。
STEP.2
計りたい処理を実行する
計りたい処理を実行します。
STEP.3
処理時間を計りたい処理が終了した直後に現在時刻を出力する
処理時間を計りたい処理が終了した直後に現在時刻を出力します。
STEP.4
STEP.3で出力した時刻からSTEP.1で出力した時刻を引く
STEP.3で出力した時刻からSTEP.1で出力した時刻を引きます。
'現在時刻を取得するコード

(処理時間を計測したい処理のコード)(開始)
　　　・
　　　・
　　　・
(処理時間を計測したい処理のコード)(終了)

'現在時刻を取得するコード
目次へ戻る

Excelのマクロのコード(例)

処理時間を計測するExcelのマクロのコード(例)

時刻を出力するコードの例は次の通りです

Sub ShowTime(cellPos)

    '現在時刻を取得してセルに出力する
    Worksheets("top").Range(cellPos).Value = "" & Hour(Now) & ":" & Minute(Now) & ":" & Second(Now)

End Sub

1行目は、マクロで処理時間を計測するプロシージャの名前をShowTimeとし、引数に変数cellPosを指定しています。

cellPosは、処理の開始時刻および終了時刻を出力するセルの位置が格納されています。

どのセルに処理の開始時刻および終了時刻を出力するのかを、ShowTimeを呼び出す側でcellPosに格納しておきます。

4行目は、時分秒をセルに出力する処理のコードです。

時はHour(Now)から、分はMinute(Now)から、秒はSecond(Now)から取得します。

プロシージャを処理時間を計測したい処理の直前と直後から呼び出すExcelのマクロのコード(例)

先ほどお話した「処理時間を計測するExcelのマクロのコード(例)」で紹介したプロシージャは、処理時間を計測したい処理の直前と直後から呼び出す必要があります。

処理時間を計測したい処理の直前には下の通りに記述します。(サンプル)

Call ShowTime("G3")

処理時間を計測したい処理の開始直前に上記のコードを記述することで、処理時間を計測したい処理の開始時刻が特定できます。

今回のサンプルでは開始時刻をセルに出力させるようにしていますが、上記のコードでは、開始時刻はセル「G3」に出力されます。

また、処理時間を計測したい処理の終了時刻を計測するには下の通りに記述します。

    Call ShowTime("G4")

今回のサンプルでは終了時刻もセルに出力させるようにしていますが、上記のコードでは、開始時刻はセル「G4」に出力されます。

実際に出力されたExcelのサンプルでは下の画像のとおりになります。

なお、上記のExcelファイルは下のマクロを使いまわしています。

【ExcelVBA】指定したファイルサイズに合致するファイルを検索してListboxに一覧表示するには

画像では、G3のセルには開始時刻が、G4のセルには終了時刻が出力されています。

開始時刻は「15:03:46」で、終了時刻は「15:04:22」です。

この終了時刻から開始時刻をマイナスすることで、処理時間が算出することができます。

なお、終了時刻から開始時刻をマイナスするのは、今回はセルの計算式を使っています。(=G4-G3)(マクロは使っていません)

終了時刻は「15:04:22」から開始時刻は「15:03:46」をマイナスすると「0:00:36」となり、36秒であることが分かりました。

以上で、処理時間を計測することができました。

目次へ戻る

処理時間を計測するExcelのマクロのコード(例)

下の記事で取り扱っているマクロに、今回紹介した処理時間を計測するExcelのマクロのコードを追記したものを改めてご紹介します。

【ExcelVBA】指定したファイルサイズに合致するファイルを検索してListboxに一覧表示するには

以下のコードは上記ページのマクロのコードを使いまわしています。

Option Explicit

Private Sub btn_exec_Click()
    
    Dim ws                  As Worksheet            'Worksheet用変数
    Dim outPutCSVFile       As String               '出力するCSVファイルのフルパス用変数
    Dim checkDir            As String               '確認したいファイルが存在するフォルダのパス
    Dim maxFSize            As Long                 '最大ファイルサイズ
    Dim minFSize            As Long                 '最小ファイルサイズ
    Dim strCmd              As String               'コマンド用変数
    Dim sqlStr              As String               'SQL文
    Dim oShell              As Object               'WshShellオブジェクト用変数
    Dim adoCON              As ADODB.Connection     'Connection用変数
    Dim oRS                 As ADODB.Recordset      'レコードセット用変数
    Dim rcdCnt              As Long                 'レコード件数
    Dim shtExistFlg         As Boolean              'シート存在確認フラグ

    '現在時刻を取得するプロシージャを呼び出す(セル「G3」を引数に指定)
    Call ShowTime("G3")
    
    '読み込んだCSVファイルのデータを出力する行の先頭位置
    Const bgnRowPos As Long = 3
    
    'SQL Serverのテーブルから取得したデータを貼り付けるシート名
    Const dtSheetNM As String = "data"
    
    'Excelの最大行数
    Const rowMaxCnt As Long = 1048575
    
    '出力するCSVファイル名
    Const outPutCSVFileNM As String = "data_output.csv"
    
    '出力するCSVファイル名を取得する(保存先はマクロのExcelファイルと同じ場所とする)
    outPutCSVFile = ThisWorkbook.Path & "\" & outPutCSVFileNM
    
    '本マクロのブックのシート名を取得する
    Set ws = Worksheets("top")
    
    '確認したいファイルが存在するフォルダのパス
    checkDir = ws.Range("searchpath").Value
    
    '検索するファイルサイズの最大値
    maxFSize = ws.Range("maxFSize").Value
    
    '検索するファイルサイズの最小値
    minFSize = ws.Range("minFSize").Value
        
    'フォルダ名とファイル名貼り付け用シート有無のチェック
    For Each ws In Worksheets
    
        If ws.Name = dtSheetNM Then
        
            'シート「data」が存在している場合
            
            '変数「shtExistFlg」にTrueを設定する
            shtExistFlg = True
        
        End If
        
    Next ws
    
    If shtExistFlg = False Then
    
        'シート「data」が存在しない場合
        
        'テーブルから取得するデータを列挙するためのシートを新規作成する
        Worksheets.Add(After:=Worksheets(Worksheets.Count)) _
                       .Name = dtSheetNM
                       
    Else
    
        'シート「data」が存在する場合
    
        'シート「data」のセル全てをクリアする
        Worksheets(dtSheetNM).Cells.Clear
                   
    End If
    
    'サイズの指定を条件にパソコン内のファイルを検索・出力してその出力内容をCSVファイルに書き込むためのPowrShellコマンド文を取得する
    strCmd = "powershell.exe -Command ""Get-ChildItem -Path '" & checkDir & "' -Recurse | " & _
             "Where-Object {!$_.PSIsContainer -and $_.Length -ge " & _
             minFSize & "MB -and $_.Length -le " & maxFSize & "MB} | " & _
             "Select-Object @{Name='FilePath'; Expression={$_.DirectoryName}}, Name, " & _
             "@{Name='FileSize (MB)'; Expression={($_.Length / 1MB)}} | " & _
             "Export-Csv -Path '" & outPutCSVFile & "' -Encoding UTF8 -NoTypeInformation"""
              
    'WshShellオブジェクト用インスタンスを生成する
    Set oShell = CreateObject("WScript.Shell")
    
    'PowerShellを呼び出してコマンドを実行する
    oShell.Run strCmd, 0, True
    'Connectionインスタンスの生成
    Set adoCON = New ADODB.Connection
    
    'CSVへのコネクション
    With adoCON
    
        .Provider = "Microsoft.ACE.OLEDB.12.0"
        .Properties("Extended Properties") = "Text;HDR=No;FMT=Delimited"
        
        'コネクションを開く
        .Open ThisWorkbook.Path & "\"
        
    End With
    'データを取得するSQL文を作成する
    sqlStr = "select"
    sqlStr = sqlStr & " F1, F2, F3"
    sqlStr = sqlStr & " from"
    sqlStr = sqlStr & " [" & outPutCSVFileNM & "]"
    sqlStr = sqlStr & " order by F3 desc"
            
    'Recordsetオブジェクトのインスタンスを生成する
    Set oRS = New ADODB.Recordset
    
    'カーソルタイプにキーセットカーソル使用
    oRS.CursorType = adOpenDynamic
    
    'SELECT文を実行してRecordsetを開く
    oRS.Open sqlStr, adoCON, adOpenStatic
    If oRS.RecordCount = 0 Then
    
        '検索データが存在しない場合
    
        With Worksheets(dtSheetNM)
            
            'シート「data」のセル全てをクリアする
            .Cells.Clear
        
            'listboxのヘッダに表示させる項目名をセルに設定する
            .Range("A1").Value = "項番"
            .Range("B1").Value = "ファイルパス"
            .Range("C1").Value = "ファイル名"
            .Range("D1").Value = "ファイルサイズ"
        
            'Listboxの表示(ListFillRangeプロパティ)をクリアする
            ListBox1.ListFillRange = dtSheetNM & "!$A$2:$D$2"
            
        End With
        
        '後処理
        oRS.Close
         
        Set oShell = Nothing
        Set adoCON = Nothing
        Set oRS = Nothing
    
        MsgBox "検索データがありません。"
        
        '処理を終了する
        Exit Sub
    
    ElseIf oRS.RecordCount > rowMaxCnt Then
    
        '検索データの件数がExcelの最大行数を超えている場合
    
        With Worksheets(dtSheetNM)
            
            'シート「data」のセル全てをクリアする
            .Cells.Clear
        
            'listboxのヘッダに表示させる項目名をセルに設定する
            .Range("A1").Value = "項番"
            .Range("B1").Value = "ファイルパス"
            .Range("C1").Value = "ファイル名"
            .Range("D1").Value = "ファイルサイズ"
        
            'Listboxの表示(ListFillRangeプロパティ)をクリアする
            ListBox1.ListFillRange = dtSheetNM & "!$A$2:$D$2"
            
        End With
        
        '後処理
        oRS.Close
         
        Set oShell = Nothing
        Set adoCON = Nothing
        Set oRS = Nothing
    
        MsgBox "検索データの件数がExcelの最大行数を超えているので処理を終了します。"
        
        '処理を終了する
        Exit Sub
    
    End If        
        
    With Worksheets(dtSheetNM)
        
        'listboxのヘッダに表示させる項目名をセルに設定する
        .Range("A1").Value = "項番"
        .Range("B1").Value = "ファイルパス"
        .Range("C1").Value = "ファイル名"
        .Range("D1").Value = "ファイルサイズ"
        
        'テーブルデータをシートに貼り付ける
        .Range("B2").CopyFromRecordset oRS
        
        'シート「data」を選択する
        .Select
    
        '表のA列の先頭行にROW関数を使って項番を振る
        .Range("A" & 2).FormulaR1C1 = "=ROW()-1"
    
        'ROW関数を使ったセルをコピーする
        .Range("A2").Copy
    
        'データが出力された行位置までA列のセルを選択する
        .Range("A2:A" & (2 + CLng(oRS.RecordCount) - 1)).Select
    
        '選択されたセルに、数式のみをセルに貼り付ける
        Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
            SkipBlanks:=False, Transpose:=False
    
        '表のA列の先頭のセルをを選択する
        .Range("A1").Select
    End With
        
    'シート「top」に表示を切り替える
    Sheets("top").Select
    
    With ListBox1
    
        'Listboxの表示するデータのセル範囲を指定する
        .ListFillRange = dtSheetNM & "!$A$2:$D$" & CLng(oRS.RecordCount) + 1
        
        'ヘッダを表示させる
        .ColumnHeads = True
        
        'Listboxの列数を設定する
        .ColumnCount = 4
        
        'Listboxの列の幅を設定する
        .ColumnWidths = "50;300;200"
            
    End With
    
    '後処理
    oRS.Close
     
    Set oShell = Nothing
    Set adoCON = Nothing
    Set oRS = Nothing
    
    '現在時刻を取得するプロシージャを呼び出す(セル「G4」を引数に指定)
    Call ShowTime("G4")
        
End Sub

Sub ShowTime(cellPos)

    '現在時刻を取得してセルに出力する
    Worksheets("top").Range(cellPos).Value = "" & Hour(Now) & ":" & Minute(Now) & ":" & Second(Now)

End Sub

注目すべきコード①

最初に見て頂きたいのは19行目です。

    '現在時刻を取得するプロシージャを呼び出す(セル「G3」を引数に指定)
    Call ShowTime("G3")

コードの説明

以上のコードは、現在時刻を取得するプロシージャを呼び出す処理のコードです。

現在時刻を取得するプロシージャはShowTimeで、引数には現在時刻を出力するセル「G3」を指定しています。

ここで取得された現在時刻が、処理時間を計測したい処理の開始時刻になります。

注目すべきコード②

次に見て頂きたいのは244行目です。

    '現在時刻を取得するプロシージャを呼び出す(セル「G4」を引数に指定)
    Call ShowTime("G4")

コードの説明

以上のコードは、現在時刻を取得するプロシージャを呼び出す処理のコードです。

現在時刻を取得するプロシージャはShowTimeで、引数には現在時刻を出力するセル「G4」を指定しています。

ここで取得された現在時刻が、処理時間を計測したい処理の終了時刻になります。

注目すべきコード③

次に見て頂きたいのは248行目から253行目です。

Sub ShowTime(cellPos)

    '現在時刻を取得してセルに出力する
    Worksheets("top").Range(cellPos).Value = "" & Hour(Now) & ":" & Minute(Now) & ":" & Second(Now)

End Sub

コードの説明

以上のコードは、現在時刻を取得してセルに出力する処理のコードです。

ShowTimeの呼び出し側に指定された引数の値がcellPosに格納されているので、そのcellPosの値が251行目のRangeプロパティに指定されてどのセルに値を出力するかが決まります。

例えばcellPosに「G3」の値が格納されている場合は、セル「G3」に値が出力されます。

また、そのセル(G3のセル)に出力されるのは現在時刻で、現在時刻は時と分と秒それぞれ関数を使って取得します。

現在の時はHour関数の引数にNow関数を指定することができます。

現在の分はMinute関数の引数にNow関数を指定することができます。

現在の秒はSecond関数の引数にNow関数を指定することができます。

以上の時と分と秒の間に「:」の文字を結合させてValueプロパティに格納することで開始時刻をセルに出力することができます。

目次へ戻る

動作確認

今回は以下の記事で紹介したExcelファイルを使って、処理時間を計測します。

【ExcelVBA】指定したファイルサイズに合致するファイルを検索してListboxに一覧表示するには

改修したExcelファイルは下の通りです。

処理時間を計測するコードを追加したソースコードは「処理時間を計測するExcelのマクロのコード(例)」をご覧ください。

また、改修したExcelファイルは「プロシージャを処理時間を計測したい処理の直前と直後から呼び出すExcelのマクロのコード(例)」の説明をご覧ください。

マクロ実行後

マクロを実行すると、開始時刻(セル「G3」)、終了時刻(セル「G4」)、処理時間(セル「G5」)がセルに出力されました。

目次へ戻る

最後に

この記事では、処理時間を計測する方法についてご説明しました。

マクロを作成していると、マクロ内の処理時間が気になる場面が出てくると思います。

例えば、なんでこんなに時間がかかるんだろう？もっと処理時間を短縮したいけど一番処理時間がかかっている箇所はどこなんだろう、など処理時間が気になることがいくつか出てくることがあるかと思います。

なのでマクロのコードで処理時間を調べたい時は、本記事を参考にしてみてくださいね。

プログラミングのスキルを習得するなら

プログラミングのスキルを習得したい、今のスキルをもっと高めたい、そう考えているなら「プログラミングスクール」がおすすめです。

プログラミングのスキルの基礎を身につけるなら「TechAcademy」で1週間の無料体験があるので、これで「プログラミングの基礎」を学ぶのにおすすめですよ。

→　TechAcademyの「1週間 無料体験」はこちら



目次へ戻る

前の記事

【ExcelVBA】Excelのマクロ・PowerShellを…

次の記事

【ExcelVBA】シートに設置されたListboxにデータを…