この「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のセミナーでも「とにかく画面を止めてください！おまじないだと思って！」みたいな解説は、まるでお門違いということですね。