VBA マクロ高速化のために停止すべき3項目

VBAでは画面描画を停止すると実行が早くなることは広く知られているが、実はあと2つ、設定すると有効なオプションがある。

手動計算と、イベントの抑制だ。

それぞれ、次のようにして設定する。

Application.ScreenUpdating = False '描画停止
Application.EnableEvents = False 'イベント抑制
Application.Calculation = xlCalculationManual '手動計算

With文で一度に設定しても良い。

    With Application
        .ScreenUpdating = False
        .EnableEvents = False
        .Calculation = xlCalculationManual
    End With

設定を解除するには次のようにする。

    With Application
        .ScreenUpdating = True
        .EnableEvents = True
        .Calculation = xlCalculationAutomatic
    End With

ただし、使用にあたってはそれぞれ注意点がある。

画面描画の停止について

描画の停止によって実行時間は早くなるが、画面に動きが無い分、体感速度が下がってしまう可能性がある。
ユーザーは待たされることそのものよりも、進捗が見えないことにイライラするものである。
時々画面を描画させるなどの小技を使うこともできるが、大して時間のかからないマクロであればむしろ描画は止めずに動きを見せるのも手だ。

イベントの抑制について

EnableEventsをFalseにしている間、イベントが検知されない。
マクロによるセルの変更でWorksheetイベントが誘発されないようにOFFにするのが本来の使い方であるが、マクロ実行中もイベントの発生が必要な場合は使えないので注意。何もイベントが登録されていなくても、これをOFFにすることで若干の高速化が期待できる。

自動計算の停止

セル上に一切の計算式が無くても、自動計算をOFFにすると若干の高速化が期待できる。
マクロ実行中に計算結果を参照する場面では、手動で計算させる必要がある。

実験

今回のサンプルでは、3つのオプションを簡単に停止・再開できるよう、標準モジュールにFocusというプロパティを作成した。
Focus(フォーカス)とは集中するという意味の英単語である。
マクロの実行に集中するために余計なことをやめるという意味をこめて、プロパティにFocusと名づけた。

Property Let Focus(ByVal Flag As Boolean)
    With Application
        .EnableEvents = Not Flag
        .ScreenUpdating = Not Flag
        .Calculation = IIf(Flag, xlCalculationManual, xlCalculationAutomatic)
    End With
End Property

これで、設定・解除が簡単にできる。

    Focus = True '描画停止、イベント抑制、手動計算
    Focus = False '描画再開、イベント監視再開、自動計算

Subプロシージャとして作成することもできる。

Sub Focus(ByVal Flag As Boolean)
    With Application
        .EnableEvents = Not Flag
        .ScreenUpdating = Not Flag
        .Calculation = IIf(Flag, xlCalculationManual, xlCalculationAutomatic)
    End With
End Sub

Subにした場合、呼び出しは、つぎのいずれかの方法になる。

    Focus True '方法1
    Call Focus(True) '方法2

Property機能はあまり知られていないが、何らかの状態を設定・取得するような場合はPropertyを使用し、何らかのアクションを起こすような操作にはSubプロシージャを使用するという風に使い分ける。

今回は状態の設定にあたるので、Propertyが望ましい。

そしてFocusプロパティを使ったマクロを同じプロシージャ内に作成する。

Sub 速度テストのサンプル()
    Focus = True
    開始時刻 = Timer()
    For i = 1 To 255
        For j = 1 To 255
            With Sheet1.Cells(i, j)
                .Value = i * j
                .Interior.Color = RGB(i, j, Int(i + j / 2))
            End With
    Next j, i
    Debug.Print Timer() - 開始時刻
    Focus = False
End Sub

実行すると以下のようにセルが塗りつぶされ、行×列の計算結果がセルに入る。
f:id:t-hom:20150831055004p:plain

これはExcelの最小ズーム(10%)で表示させている。
灰色の木目みたいな線に見えるのは、数字の繰り上がりの境界線である。桁数が変わっている場所はより濃く見える。

さて、実行にかかった時間は約6秒だった。

次に、それぞれをONにした場合の影響を見てみた。

画面描画ON 45秒(約39秒差)
イベントON 7秒(約1秒差)
自動計算ON 9秒(約3秒差)

なお、画面描画の影響は、Excel上の表示範囲だけである。
下図のように最大ズーム(400%)時は表示範囲が狭くなるので、画面描画ONでも約7秒で完了した。
f:id:t-hom:20150831060341p:plain

また、別シートが選択されている場合も描画範囲は表示されていないため6秒程度で完了する。
Excel本体を最小化させた場合も同様に、6秒程度で完了した。

ただし、最小ズーム(10%)で、VBエディタを最大化してExcelを背面に隠しただけでは画面描画OFFと同等の効果は無く、45秒かかった。
ウインドウが隠れているだけでは駄目で、Excelが描画範囲と認識しているかどうかの違いが影響するようだ。

このように、画面描画は条件によって効果が大きく変わるのに対し、手動計算とイベントの抑制は、セルへの書き込み回数に比例して一定の効果がある。

セルへのアクセスが多い場合はまず配列の使用を検討すべきだが、すでに作ってしまったマクロを直すのは手間がかかる。
遅いマクロを手っ取り早くスピードアップさせるには、まずこれら3点を設定してみるのが良いと思う。

その他の高速化プロパティ(2017/1/13追記)

素晴らしい記事をみつけたので紹介。
dev-clips.com

特にページ設定周りで、なんでこんなに遅いんだろうと思ってたんだけれど、たぶんApplication.PrintCommunicationが原因。これは知らなかった。

VBAの高速化専門書

マクロのスピードにとことんこだわりたいならば専門に扱っている書籍がある。

Excel VBA パフォーマンスレポート (Something U want)

作者: 坪崎誠司
出版社/メーカー: 株式会社プレスティージ
発売日: 2010/01/26
メディア: 単行本（ソフトカバー）
クリック: 3回
この商品を含むブログを見る

ただ、検証環境はCPU2.1GHz、メモリ2GB、Excel2007なので、環境によって同じくらいの高速化が望めるかどうかは不明。
内容も100万回ループして1秒の差といったものなので、それだけのデータを扱うのでなければ微々たるものだ。また、ものによってループが1000万回だったり、有意差が出るように調整されている。

少なくとも10万件程度のデータや、1万件超のファイルを扱うなどの規模の大きなマクロでなければ、意味のある差は見いだせないと思う。そのような大規模な処理を行わないなら、この本はおススメしない。

高速化をClassにまとめてみた↓
thom.hateblo.jp

その高速化、ちょっと待った！↓
thom.hateblo.jp

配列を使ったセルの高速読み書き↓
thom.hateblo.jp

Propertyの活用について↓
thom.hateblo.jp

Now()関数はPropertyとして実装されているという話↓
thom.hateblo.jp