Office TANAKA - VBA高速化テクニック(表示を止めろ！)

トップページ > Excel > VBA > 高速化テクニック > 表示を止めろ！

表示を止めろ！

マクロでSelectを多用すると、アクティブセルの移動に伴って画面の表示が切り替わります。見た目にも忙しそうですし、何より実効速度が大きく損なわれます。画面の更新を止めたいという質問は多く、定番中の定番 King of FAQ と言われています。

【実験1】

	Test1	Test2	%
1回目	00:09	00:05	55.6%
2回目	00:11	00:04	36.4%
3回目	00:10	00:05	50.0%
4回目	00:10	00:05	50.0%
5回目	00:10	00:04	40.0%
6回目	00:10	00:04	40.0%
7回目	00:09	00:04	44.4%
8回目	00:10	00:05	50.0%
9回目	00:10	00:05	50.0%
10回目	00:10	00:04	40.0%
平均	00:10	00:05	45.5%

マクロ記録で生成されたコードをそのまま使うユーザーが陥りやすいミスです。
操作を忠実に記録したコードをそのまま使っているのでしたら、速度に関して文句は言えません。マクロ記録は操作からVBAのコードを生成する機能ですが、決して最適化されたコードを生み出すわけではありません。たまに、有償で作成された大規模なシステムマクロの中に、マクロ記録のコードをそのまま使っているケースを見かけます。恥ずかしい話です。
マクロ記録のコードがいけないのではありません。記録されたコードを吟味して、少しでも高速にしたり、少しでもミスを減らそうという努力が必要だということです。

【実験1】では次の2つのプロシージャを計測しました。

Sub Test1()
    Dim i As Integer, j As Integer
    For i = 1 To 100
        For j = 1 To 10
            Cells(j, 1).Select
            Selection.Value = j
        Next j
    Next i
End Sub

Sub Test2()
    Dim i As Integer, j As Integer
    Application.ScreenUpdating = False
    For i = 1 To 100
        For j = 1 To 10
            Cells(j, 1).Select
            Selection.Value = j
        Next j
    Next i
End Sub

ApplicationオブジェクトのScreenUpdatingプロパティにFalseを設定すると、画面の更新が行われなくなります。ExcelがCPUの内部で計算する速度に比べて、画面に描画する速度は低速です。VBAの実行速度を考えるとき、画面の更新を抑止するのは基本でしょう。
実験1では、A1:A10セルに、変数j(1～10)の代入を100回くり返しました。本当は、

Cells(j, 1).Select
Selection.Value = j

という記述自体が冗長なのですが、この点については後で詳しく解説します。平均値で見ると50%程度の高速化が記録されました。

VBAの実行速度に影響する画面の更新は、アクティブセルの移動以外にも、アクティブシートの切り替えに注意です。こちらの方が画面のちらつきも激しいですから何とかしたくなりますね。次の【実験2】では同じことをSheet1とSheet2に対して行います。実験結果は次の通りです。

【実験2】

	Test1	Test2	%
1回目	01:21	00:28	34.6%
2回目	01:14	00:23	31.1%
3回目	01:13	00:23	31.5%
4回目	01:16	00:24	31.6%
5回目	01:15	00:24	32.0%
6回目	01:14	00:24	32.4%
7回目	01:12	00:28	38.9%
8回目	01:15	00:24	32.0%
9回目	01:14	00:24	32.4%
10回目	01:14	00:23	31.1%
平均	01:15	00:25	32.8%

次の「Selectしない」で解説しますが、セルやワークシートなどは選択しなくても操作できます。セルにデータを入力したり、他のシートにコピーするとき、いちいちSelectする必要はないのです。そうはいっても、諸々の事情でSelectしなければならないこともあるでしょう。たとえば、グラフやオートシェイプの設定を変更する場合は、対象となるオブジェクトを選択状態にしなければならないこともあります。もし、選択した状態をユーザーに見せなくてよいのなら、ScreenUpdatingプロパティにFalseを設定して画面の更新を抑止しましょう。

ScreenUpdatingプロパティで注意しなければならないのは、UserFormを表示するときです。画面を抑止した状態でUserFormを表示した場合、UserFormをドラッグすると後が残ります。たとえば次のようなコードはいけません。

Sub Sample()
    Application.ScreenUpdating = False
    UserForm1.Show
End Sub

実行した結果はコチラをご覧ください。

またScreenUpdatingプロパティは、プロシージャが終わると元のTrueに戻ります。しかし、Falseに設定して画面を止めた後、抑止の必要がなくなった時点でTrueを明示的に設定するべきでしょう。わずか1行のコードですし、自分で行った処理を自分で後始末するのはプログラマのマナーです。特に、更新と抑止をひんぱんに繰り返し、サブルーチンなど他のプロシージャをコールするようなマクロでは気を付けてください。

[目次]戻る← | →進む[Selectするな！]