画面を止める


この「VBA高速化テクニック」を書いたのは、1995年です。当時はパソコン通信でした。その後、インターネットの時代になり、私も自分のWebサイトを作りました。それがここ「Office TANAKA」です。Webサイトの公開当初には、まだコンテンツの数も少なかったので、パソコン通信時代に書いた「VBA高速化テクニック」をHTML化しました。それ以来、とても多くのExcelユーザーが、この「VBA高速化テクニック」を参考にしていただいています。それはそれで、とても嬉しいことなのですが。ただひとつ、どうも私の考えが"誤って"伝わっていると感じることも多いです。その典型が、本項「画面を止める」です。

よろしいですか?誤解のないようにハッキリ書きます。もし、画面がひんぱんに切り替わるようなマクロだった場合、そのときは、画面を止めるとマクロの速度が速くなりますよという話です。画面がひんぱんに切り替わるマクロというのは、たいていはマクロ記録したコードをそのまま使っているようなケースです。たとえば

○○.Select
Selection.××

という書き方ですね。これは無駄です。手動操作でしたら、操作の対象を「まず選択」します。そして「選択した」対象に対して何かの操作を行います。手動操作だったら、こうするしか方法はありません。しかしマクロは手動操作の高速化ではありません。手動操作とマクロは、まったく違うんです。普通のマクロでしたら、

○○.Select
Selection.××

は、直接

○○.××

と書けます。Selectしないでいいんです。たま~に「いや、マクロ記録したコードからSelectやSelectionを消して、直接書いたらエラーになったんだけど」という、訳の分からない反論を聞くこともあります。ほとんどの場合、エラーの原因は「Select~Selectionを消したから」ではなく、それ以外です。犯人は別にいます。えん罪です。

もう一度書きます。ここ、しっかり理解してください。画面がひんぱんに切り替わるようなマクロだったら、画面を止めると速くなりますよと。ただし、画面がひんぱんに切り替わらないようなマクロで画面を止めても意味がないですよと。

これから実証します。ここでは、Sheet1のセルをSheet2にコピーするマクロで速度を計測します。もちろん手動でコピーするのでしたら、画面がひんぱんに切り替わります。Sub test1は、画面が切り替わるままです。Sub test2は画面の更新を止めます。

Sub test1()
    Dim i As Long
    For i = 1 To 100
        Cells(i, 1).Select
        Selection.Copy
        Sheets("Sheet2").Select
        Cells(i, 1).Select
        ActiveSheet.Paste
        Sheets("Sheet1").Select
    Next i
End Sub
Sub test2()
    Dim i As Long
    Application.ScreenUpdating = False
    For i = 1 To 100
        Cells(i, 1).Select
        Selection.Copy
        Sheets("Sheet2").Select
        Cells(i, 1).Select
        ActiveSheet.Paste
        Sheets("Sheet1").Select
    Next i
    Application.ScreenUpdating = True
End Sub

それぞれ10回計測した結果です。

test1 test2 %
1 10.187 3.203 31.4%
2 9.875 4.704 47.6%
3 10.141 4.750 46.8%
4 10.343 4.765 46.1%
5 11.579 4.797 41.4%
6 10.671 4.797 45.0%
7 9.792 4.656 47.5%
8 10.742 5.141 47.9%
9 11.031 5.109 46.3%
10 10.541 4.703 44.6%
平均 10.490 4.663 44.5%

確かにマクロの速度は速くなっています。ただ、そもそも画面がひんぱんに切り替わるのがおかしいんです。操作の対象を、毎回Selectしているのがおかしいんです。この結果を見て、単純に「とにかく画面を止めるとマクロが速くなる」とは思わないでください。次の検証をご覧ください。

そもそも、操作の対象をSelectすることによって画面が切り替わるのがおかしいのですから、Selectしないでセルをコピーします。Sub test3はSelectしていません。Sub test4は、さらに画面を止めてみます。

Sub test3()
    Dim i As Long
    For i = 1 To 100
        Sheets("Sheet1").Cells(i, 1).Copy Sheets("Sheet2").Cells(i, 1)
    Next i
End Sub
Sub test4()
    Dim i As Long
    Application.ScreenUpdating = False
    For i = 1 To 100
        Sheets("Sheet1").Cells(i, 1).Copy Sheets("Sheet2").Cells(i, 1)
    Next i
    Application.ScreenUpdating = True
End Sub

検証結果は次のとおりです。

test3 test4 %
1 2.547 1.953 76.7%
2 1.422 1.922 135.2%
3 1.859 1.375 74.0%
4 2.313 1.656 71.6%
5 2.250 1.125 50.0%
6 2.218 1.140 51.4%
7 2.235 1.125 50.3%
8 2.250 1.641 72.9%
9 2.234 1.781 79.7%
10 2.234 2.125 95.1%
平均 2.156 1.584 75.7%

この結果を見て「やっぱ画面を止めたら約25%速くなった」などと考えないでくださいね。最初のsub test1が平均で10.490秒ですよ。画面がひんぱんに切り替わる書き方です。それの画面を止めて、平均で4.663秒になりました。でも、そもそもSelectしない書き方をすれば、それだけで平均2.156秒になるんです。ここです、大事なことは。100個のセルをコピーしましたが、これが1,000回のコピーだったらどうなるでしょう。

test3 test4 %
1 19.750 16.672 84.4%
2 18.750 20.578 109.7%
3 21.000 20.281 96.6%
4 22.297 19.109 85.7%
5 19.172 19.610 102.3%
6 22.250 20.156 90.6%
7 22.438 19.047 84.9%
8 18.516 19.750 106.7%
9 22.344 20.532 91.9%
10 21.625 19.015 87.9%
平均 20.814 19.475 94.1%

ね、ほとんど変わらないでしょ。つまり、そもそも画面が切り替わらない(Selectしない)マクロだったら、画面を止めても意味がないということです。このことを誤解している人が実に多いです。インターネットだけでなく、VBAのセミナーでも「とにかく画面を止めてください!おまじないだと思って!」みたいな解説は、まるでお門違いということですね。