Office TANAKA - Excel VBA Tips[マクロの実行速度を計測する]

マクロの実行速度を気にする人が多いです。ネットを検索すると、何やら配列を使ったりするとマクロが速くなると。だから、そうしてみました。みたいな話をよく聞きます。でも、たいていそういう人には、次のような特徴があります。

マクロ全体に対して「遅い」と思っている
マクロの実行速度を気にしているくせに、実際に計測していない

よく「このマクロ遅いんですよ～何とかなりませんか～」みたいな質問を受けるのですが、マクロ(プロシージャ)全体を見て"遅い"とか"速い"とか考えても、解決策は得られません。マクロが"遅い"とかじゃなくて、どの処理が"遅い"かが問題なんです。

速さを気にするようなマクロは、一般的に"複数の処理"を行っています。

Sub Sample1()
    Range("A1") = 100
End Sub

上のように、ひとつの処理しかしていないマクロに対して「遅いんですよ～」という人はいません。一般的には、次のように複数の処理をしているマクロで、速度を気にするのでしょう。

Sub Sample2()
    Dim buf As String, i As Long
    Const P As String = "C:\Windows\System32\"
    Range("A1") = "名前"
    Range("B1") = "サイズ"
    Range("C1") = "日時"
    Range("A1:C1").HorizontalAlignment = xlCenter
    buf = Dir(P & "*.*")
    Do While buf <> ""
        With Cells(Rows.Count, 1).End(xlUp)
            .Offset(1, 0).Select
            Selection = buf
            .Offset(1, 1).Select
            Selection = FileLen(P & buf)
            .Offset(1, 2).Select
            Selection = FileDateTime(P & buf)
        End With
        buf = Dir()
    Loop
    With ActiveSheet.PageSetup
        .TopMargin = Application.InchesToPoints(0)
        .BottomMargin = Application.InchesToPoints(0)
    End With
    Range("A1").CurrentRegion.Sort Range("B1"), xlDescending
    For i = 2 To Cells(Rows.Count, 2).End(xlUp).Row
        Cells(i, 2).Select
        Selection.NumberFormat = "#,##0"
    Next i
    Range("A1:C1").EntireColumn.AutoFit
    Range("A1").Select
End Sub

ちなみにこのマクロ、私の環境で約53秒かかります。いやぁ、時間がかかるマクロを作るのに苦労しました(笑)。普通に書くと速いマクロになっちゃうんですよ(^▽^)

もちろん、マクロの実行速度は、パソコンの性能や環境で異なります。今回は、

Core i7-4500U 1.80GHz
メモリ 8G
Windows 8.1 Pro(64ビット)
Excel 2013(32ビット)

というスペックでやっています。

さて「このマクロは53秒もかかる」ので、何とか速くしたい－－のように考える人が多いのですが、重要なことは53秒の内訳です。だって、このマクロはひとつの処理をしているのではありません。次のことをやっています。

セル範囲A1:C1に文字列を代入する
セル範囲A1:C1に中央揃えの書式を設定する
C:\Windows\System32\フォルダ内の全ファイル情報をセルに代入する
印刷時の余白を設定する
B列の数値をキーに降順で並べ替える
B列の数値に3桁区切りの書式を設定する
A列からC列までの列幅を自動調整する
セルA1を選択する

マクロの実行速度を検討するのなら、各処理のどこで時間がかかっているのかを調べなければなりません。そのためには、処理の時間を計測します。「処理の時間を計測するなんて、どうやったらいいのだろう？」と悩む方もいますが、難しいことではありませんよ。考えれば分かります。てか、考えてください。まず、マクロ全体でどれくらいの時間がかかるかを調べるには、マクロがスタートしたときの時刻を記録しておいて、終了したときの時刻から引けばいいんです。現在の時刻は、Time関数で分かります。記録しておくのは、イミディエイトウィンドウがいいでしょう。

Sub Sample2()
    Dim buf As String, i As Long
    Const P As String = "C:\Windows\System32\"
    Debug.Print Time
    Range("A1") = "名前"
    Range("B1") = "サイズ"
    Range("C1") = "日時"
    Range("A1:C1").HorizontalAlignment = xlCenter
    buf = Dir(P & "*.*")
    Do While buf <> ""
        With Cells(Rows.Count, 1).End(xlUp)
            .Offset(1, 0).Select
            Selection = buf
            .Offset(1, 1).Select
            Selection = FileLen(P & buf)
            .Offset(1, 2).Select
            Selection = FileDateTime(P & buf)
        End With
        buf = Dir()
    Loop
    With ActiveSheet.PageSetup
        .TopMargin = Application.InchesToPoints(0)
        .BottomMargin = Application.InchesToPoints(0)
    End With
    Range("A1").CurrentRegion.Sort Range("B1"), xlDescending
    For i = 2 To Cells(Rows.Count, 2).End(xlUp).Row
        Cells(i, 2).Select
        Selection.NumberFormat = "#,##0"
    Next i
    Range("A1:C1").EntireColumn.AutoFit
    Range("A1").Select
    Debug.Print Time
End Sub

これで全体の所要時間が分かります。あとは、この考え方で、各処理が終わったときの時刻を記録してやればいいのです。

Sub Sample4()
    Dim buf As String, i As Long, S As Long
    Const P As String = "C:\Windows\System32\"
    Debug.Print Time & " - スタート"
    Range("A1") = "名前"
    Range("B1") = "サイズ"
    Range("C1") = "日時"
    Range("A1:C1").HorizontalAlignment = xlCenter
    Debug.Print Time & " - セルの代入"
    buf = Dir(P & "*.*")
    Do While buf <> ""
        With Cells(Rows.Count, 1).End(xlUp)
            .Offset(1, 0).Select
            Selection = buf
            .Offset(1, 1).Select
            Selection = FileLen(P & buf)
            .Offset(1, 2).Select
            Selection = FileDateTime(P & buf)
        End With
        buf = Dir()
    Loop
    Debug.Print Time & " - ファイル情報の取得"
    With ActiveSheet.PageSetup
        .TopMargin = Application.InchesToPoints(0)
        .BottomMargin = Application.InchesToPoints(0)
    End With
    Debug.Print Time & " - 印刷の設定"
    Range("A1").CurrentRegion.Sort Range("B1"), xlDescending
    Debug.Print Time & " - 並べ替え"
    For i = 2 To Cells(Rows.Count, 2).End(xlUp).Row
        Cells(i, 2).Select
        Selection.NumberFormat = "#,##0"
    Next i
    Debug.Print Time & " - 桁区切り書式設定"
    Range("A1:C1").EntireColumn.AutoFit
    Range("A1").Select
    Debug.Print Time & " - 列幅の自動調整"
End Sub

結果は次のようになりました。
12:55:18 - スタート
12:55:18 - セルの代入
12:55:55 - ファイル情報の取得
12:55:56 - 印刷の設定
12:55:56 - 並べ替え
12:56:09 - 桁区切り書式設定
12:56:09 - 列幅の自動調整

これを見ると、たとえば最初の「セルの代入」や最後の「列幅の自動調整」などは、1秒かかっていないと分かります。そして、明らかに時間を要しているのは「ファイル情報の取得」と「桁区切り書式設定」の2カ所です。ですから、この2つの処理を重点的に改善してやれば、マクロ全体の処理速度が向上するわけです。

ファイル情報をセルに代入しているところは、明らかに冗長ですね。

    Do While buf <> ""
        With Cells(Rows.Count, 1).End(xlUp)
            .Offset(1, 0).Select
            Selection = buf
            .Offset(1, 1).Select
            Selection = FileLen(P & buf)
            .Offset(1, 2).Select
            Selection = FileDateTime(P & buf)
        End With
        buf = Dir()
    Loop

ここを、次のように修正して、再び速度を計測してみます。

    Do While buf <> ""
        With Cells(Rows.Count, 1).End(xlUp)
            .Offset(1, 0) = buf
            .Offset(1, 1) = FileLen(P & buf)
            .Offset(1, 2) = FileDateTime(P & buf)
        End With
        buf = Dir()
    Loop

すると、次のようになります。

13:06:47 - スタート
13:06:47 - セルの代入
13:06:48 - ファイル情報の取得
13:06:48 - 印刷の設定
13:06:48 - 並べ替え
13:06:56 - 桁区切り書式設定
13:06:56 - 列幅の自動調整

次の改善箇所は「桁区切り書式設定」です。

    For i = 2 To Cells(Rows.Count, 2).End(xlUp).Row
        Cells(i, 2).Select
        Selection.NumberFormat = "#,##0"
    Next i

ここも、無駄にセルを選択(Select)していますね。てゆーか、なんで1つずつ書式を設定しているのでしょう。そんなの、B列に入力されているセル範囲に、一括設定すれば済む話です。

Range(Range("B2"), Cells(Rows.Count, 2).End(xlUp)).NumberFormat = "#,##0"

13:16:00 - スタート
13:16:00 - セルの代入
13:16:01 - ファイル情報の取得
13:16:01 - 印刷の設定
13:16:01 - 並べ替え
13:16:01 - 桁区切り書式設定
13:16:01 - 列幅の自動調整

約53秒かかっていたマクロが、約1秒で終わるようになりました。マクロの高速化とは、こういうことです。

「マクロが遅い」と思ったら、そして、それを何とか改善しようと思ったら、まず考えるべきことは"どの処理で時間がかかっているのか"を計測することです。プロシージャの先頭に、おまじないのようにApplication.ScreenUpdating = Falseを書くことではありません。それは、臭い物に蓋をしているだけです。くれぐれも、実際に計測もしないで「遅い、遅い」と嘆かないでくださいね。