大量のデータを扱う為重い、ある日を境に急に重くなった等々…マクロあるあるですが、本記事の内容を適用する事で確実にスピードアップする事間違い無しなので、お困りな方は是非参考にして下さい！
速度改善基本コード
マクロの実行速度を改善するに辺り、三種の神器とでも呼ぶべきコードが存在します。重いと感じるマクロにもし下記が記述されていないのなら今すぐに導入する事を強く推奨します！
|
1 2 3 4 5 6 7 8 9 10 11 |
Application.Calculation = xlCalculationManual Application.ScreenUpdating = False Application.EnableEvents = False ' | ' | 重い処理 ' | Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True Application.EnableEvents = True |
Application.Calculation
セルの自動計算を抑制する為のコードです。 xlCalculationManualになっている間は例えどこかのセルの内容が変更されたとしても、付随する関数だったりの計算が行われ無い様になります。なので、シートにびっしり関数が埋められていてマクロの処理によりそこの値が変動してしまう場合等が特に効果を発揮します。
処理が終わったらxlCalculationAutomaticで自動計算(デフォルト)に戻します。
Application.ScreenUpdating
画面の更新を抑制する為のコードです。Falseで抑制されている間はFalseと設定した時の画面から更新がされません。色んなファイルを開いたり、シートを飛び回ったり、どんどん画面が変動するようなマクロの場合に特に効果大。処理が終わったらTrueで画面描画されるように戻します。
Application.EnableEvents
イベント検知を抑制する為のコードです。抑制されている間はイベントの検知がされません。セルに値を入力して、それをセルのChangeイベントで拾って～みたいなマクロの場合は機能しなくなるので注意。そういった処理が無いのならFalse推奨。処理が終わったらTrueで画面描画されるように戻します。
手っ取り早く改善を行いたい場合はこれら3つを入れるだけでも大分速度の改善がされる筈です。
更に改善したい！って場合は根本から修正を行う必要も出てくるので中々根気がいる場合が在りますが、特に初学者がやってしまいがちな遅延に繋がる書き方を次からご紹介していきます。
セルの直接操作を減らす
やってしまいがちな激重操作その①セルアクセスしまくりマクロ。
例えばこんなマクロがあったとします↓
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
Private Sub Array() Dim StratTime As Double ' 開始時間計測用 Dim StopTime As Double ' 終了時間計測用 Dim nCnt As Long ' ループカウンタ ' Timer開始 startTime = Timer For nCnt = 1 To 100000 Cells(nCnt, 2) = Cells(nCnt, 1) + Cells(nCnt, 1) Next ' Timer終了 StopTime = Timer Debug.Print (StopTime - startTime) & "秒" End Sub |
Timer結果：2.5秒
セルの値をCells(nCnt, 1)で直接取得し、Cells(nCnt, 2)へ書き込む処理です。VBAではなるべくセル操作を減らす事が鉄則。アクセスが増えれば増える程に遅くなっていきます。
最初は早かったのに最近重くなってきた…みたいな時は結構これが原因の場合が多い印象。
なのでこうしてみる↓
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
Private Sub Array() Dim StratTime As Double ' 開始時間計測用 Dim StopTime As Double ' 終了時間計測用 Dim nCnt As Long ' ループカウンタ Dim arrValue As Variant ' 配列 ' Timer開始 startTime = Timer ' 配列へ格納 arrValue = Range("A1:A100000") ' 配列内で計算 For nCnt = 1 To 100000 arrValue(nCnt, 1) = arrValue(nCnt, 1) + arrValue(nCnt, 1) Next ' 配列を張り付け Range("B1:B100000") = arrValue ' Timer終了 StopTime = Timer Debug.Print (StopTime - startTime) & "秒" End Sub |
Timer結果： 0.1640625秒
大分変りました。都度アクセスするのでは無く一括で範囲を配列へ取り込み、配列の中で操作や計算を行い一括で出力する方式です。つまりループの回数だけセルへアクセスしていたのがたったの2回だけに減る訳です。
その都度取得をしに行くような作りがあるのなら、一度で取得出来るように調整を行いましょう！
不要なSelectや処理を減らす
やってしまいがちな激重操作その②無駄な処理しすぎマクロ。
例えばこんなマクロがあったとします↓
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
Private Sub copipe() Dim StratTime As Double ' 開始時間計測用 Dim StopTime As Double ' 終了時間計測用 Dim nCnt As Integer ' ループカウンタ ' Timer開始 startTime = Timer For nCnt = 1 To 1000 ' Sheet2をアクティブ状態にする Worksheets("Sheet2").Activate ' Sheet2のセルA1を選択状態にする Worksheets("Sheet2").Range("A1").Select ' Sheet2のセルA1をコピーする Worksheets("Sheet2").Range("A1").Copy ' Sheet1のセルA1にペーストする Worksheets("Sheet1").Range("A1").PasteSpecial ' Sheet1をアクティブ状態にする Worksheets("Sheet1").Activate Next ' Timer終了 StopTime = Timer Debug.Print (StopTime - startTime) & "秒" End Sub |
Timer結果： 25.515625秒
激重です。VBA初学者に特に多いんですが、最初やりたい事が出来た時にマクロの記録を使って作る事があると思います。
で、別シートの値をコピーして別シートに張り付けるって操作を記録するとそれぞれのコードが記録される訳ですが、そのまま記載しないといけないと思い込みそれをずっと継承し続けてしまう。すると上の様なものが出来上がる訳です。
なのでこうしてみる↓
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
Private Sub copipe() Dim StratTime As Double ' 開始時間計測用 Dim StopTime As Double ' 終了時間計測用 Dim nCnt As Integer ' ループカウンタ ' Timer開始 startTime = Timer For nCnt = 1 To 1000 ' Sheet2のセルA1をSheet1のセルA1へ代入 Worksheets("Sheet1").Range("A1") = Worksheets("Sheet2").Range("A1") Next ' Timer終了 StopTime = Timer Debug.Print (StopTime - startTime) & "秒" End Sub |
Timer結果： 0.2578125秒
これも大分変りました。Select等も先ほどのセルの直接操作を減らす編で出て来た様になるべく行わない方が吉な操作。このマクロで行うような操作ならいちいちSelectを行わなくても値は取って来れるので削除すべし。
セルへのアクセスに限らず不要な操作や省けるものはどんどん削ぎ落した方が当然実行速度はちりつもで向上します。が、視認性が悪くなる様なら本末転倒なので、単純に短くしまくれば良いってもんでも無いのでそこだけ注意が必要。
余計なコードを埋め込んでませんか？視認性が保たれる範囲で、不要なものはどんどん排除しましょう！
速度改善の極意
ここまでのスピードアップ術を纏めると、
これらを意識するだけで確実に速度向上に繋がります！しっかり守って良いExcelライフを～(ᵔᴥᵔ)
コメント