処理の高速化とは！？
処理の高速化とは単純に処理開始から完了までの時間を短縮する事ですね。
口で言うのは簡単ですが、何をもってして処理を高速化するのか！？という所が今回のミソです。
処理が遅くなる原因を考えてみましょう
Excelシートをいじっているとして考えてみましょう。
- セルを選択した時、この時はカーソル(アクティブになっているセル範囲)が動きます。
- シートを変更(別のシートを選択)する時、この時は画面の偏移が発生します。
- セルに何かを入力した時、この時はセルに値が入り内部的に計算するイベントが発生します。更にそのセルに入った値を見て別の場所で合計などを計算している場合はその計算の処理も発生します。
処理が遅くなる時ってこういったケースの積み重ねなんです。
例えばその一つ一つが0.1秒かかっていたとしてもその処理を100回行っただけでも
0.1(秒) × 100(回) = 10(秒)
と馬鹿に出来ない数字になりますね。
処理を早くする方法を考えてみましょう。
これは遅くする原因を省く事が一番ですね。
- 必要がない時はセルを選択しない。
- 必要がない時はシートをわざわざselectやactivate(選択)しない。
- 値は配列に一度格納してから一括で吐き出す(処理を1回で済ます)。
- そもそもExcelのシート上に計算式を組まず、全てをプログラム側で計算させてからセルに吐き出す
という、細かな点に気を付けるだけでも処理は圧倒的に早くなります。
しかしシートがアクティブじゃないとデバッグが出てしまう場合など、そうもいかない時もあるかも知れません。
そういった時は下記に記す方法を試してみましょう。
①．画面偏移を止める
セルの選択や別シートの選択などの処理を内部的に行い画面上は処理をしていないかの様にふるまわせる事が出来ます。
画面の偏移を止めたい箇所で
Application.ScreenUpdating = False
と入れましょう。
これだけでもかなりの高速化には繋がります。
画面偏移を止める処理なので必要がなくなったらプロシージャの最後(処理が完了する前)に
Application.ScreenUpdating = True
と入力して画面偏移を戻しましょう。
②．再計算を止める
セルに値が入ったらいちいち走る再計算のイベントを止める事が出来ます。
セルに多数の値が入力される手前で
Application.Caliculation = xlCalculationManual
と入力しましょう。
再計算を止める事が出来ます。
Excel上部のメニュー→計算方法の設定→手動
と設定した時と同じ状態ですね。
これも元に戻さなければ再計算は実行されないので
Application.Caliculation = xlCalculationAutomatic
と入力しましょう。
再計算は最後にひとまとめにすることにより処理を高速化する事が出来ます。
忘れてしまうと後で使う方に迷惑をかけてしまいますので忘れないようにしましょう。
③．イベントを止める
イベントと一言で言ってもちょっと難しいのですが、処理中に余計なイベントが発生して処理に時間がかかるのを防ぐことができます。
処理の冒頭で
Application.EnableEvents = False
と入力しましょう。
これも最後に元に戻すのを忘れずにしましょう。
Application.EnableEvents = True
これで元に戻ります。
Applicationオブジェクト
これらは全て最初にApplicationと入りますね。つまりApplicationオブジェクトのプロパティであるという事です。
Withを使えばすっきりと使いやすく書き上げる事が出来ます。
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
.EnableEvents = False
End With
これをプロシージャの冒頭に書けば処理が高速になる。というわけですね。
一つのプロシージャとして管理してみよう
上記のプログラムをTrueかFalseを受け取って高速化ONとOFF出来るプロシージャを作ってみましょう。
True = 高速化ON
False = 高速化OFF
という事で組み上げてみましょう。
Sub test(bool As Boolean)
With Application
.ScreenUpdating = Not bool
.EnableEvents = Not bool
If bool Then
.Calculation = xlCalculationManual
Else
.Calculation = xlCalculationAutomatic
End If
End With
End Sub
これでboolという引数にTrueが入って来た時は高速化ON、Falseが入って来た時は高速化OFFがどこからでも呼び出せる為利便性を上げる事が出来ました。