処理速度の向上はどこまでやれば良い(GW特別号No3)最終更新日:2014-11-11第16回.処理速度の向上はどこまでやれば良い(GW特別号No3)エクセルで顧客管理を作ろう、ゴールデンウイーク特別号No3です、前回の「記述による処理速度の違い」が好評(本当に?)のようでしたので、再度、処理速度に関する内容をお届けします。実際に、作成中の顧客管理で処理速度対策を施してみます。具体的にどの処理を対策するかですが、以前から気になっていたのですが、「顧客一覧」でF1を押して、「顧客登録」に表示する際に、私の古いノートPCでは、カーソルが砂時計になるのが、完全に目視出来ます。処理内容からいって、これは明らかに遅いと感じます。(この感じるは、経験上からです)では、この処理について、処理速度対策を施してみます。タイム測定の為に、各モジュールの先頭と最後に、Debug.Printで、Timer関数を表示します。処理時間が短いので、ミリ秒を測定する為に、Timer関数を使用します。Timer関数は0時0分0秒から現在までの経過秒数を、小数2桁で返します。全処理所要時間は、0.23秒でした。その結果判明したのは、Subモジュール「顧客一覧より取得」が2度起動されている事です。実は、事前に把握していたのですが、機会があったらイベント処理の注意として説明するつもりでした。2度起動されるかの理由は、ごく簡単に説明します。(詳細には説明しきれませんので)まず、シート「顧客登録」に、Worksheet_Changeのイベントを作成しました。Subモジュール「マクロ開始処理」で、イベントの発生を停止しているのですが、同じモジュール内で「マクロ終了処理」も実行している為、モジュール終了後に、イベントが処理されてしまっていました。これは、他のSubモジュールからCallしている為に発生していると思われます。そこで、まずこの、2重起動しないように修正します。各Subモジュールに以下の変更を加えます。対象モジュールは、「顧客登録シート作成」「顧客一覧より取得」「顧客一覧へ登録」の3つです。マクロ終了処理太字部分が修正箇所になります。False)は、Optionalが付いた引数は、省略可能な引数で、省略された場合は、この値が引数に設定されます。引数が省略された場合は、いままで通りに動作させ、引数にTrueが指定された場合は、マクロ開始処理、マクロ終了処理を行わないようにしました。次に、Subモジュール「ファンクションF1」を以下に変更します。顧客一覧より取得(True)マクロ終了処理ここは、前述の修正を受けて、マクロ開始処理、マクロ終了処理を組み込み、各モジュールは、(false)を付けて呼ぶことで、マクロ開始処理、マクロ終了処理を止めています。各Subモジュールから、マクロ開始処理、マクロ終了処理を消してしまっても良いのですが、単独使用の余地を残すことと、Optionalの説明もできますので。イベントの発生を期待して、「顧客一覧より取得」をCallしない方法もありますが、他人任せはキライなので(笑)この結果の所要時間は、0.16秒でした。この程度の時間短縮でも、砂時計を目視するのは困難になりました。普通に考えれば、この程度で十分です。重要な事は、変なテクニックを使う事より、正攻法で問題点を解決することです。それではブログとしてはつまらないので、どこまで早くなるかやってみます。処理時間が、計測可能範囲ぎりぎりですので、少し処理時間がかかるようにします。「顧客一覧」の項目を、200項目に増やします。この時点での、所要時間は、0.868秒でした。百分の一秒の争い(オリンピック並み)なので、5回の実行の平均を算出しています。処理速度対策をします。気になるのが、セルの結合、罫線等のセルに対する操作が、列数分行われている事です。これを全部、ひとまとめにしてしまいます。Subプログラム内のコメントは消しました。太字が主な変更箇所です。操作対象となるセル範囲を、Rangeに全て入れてしまいます。rng1は、見出し+データ入力セルrng2は、データ入力セルを入れます。Unionは、複数のRangeを1つのRangeにする事が出来ます。全ての行の処理が終了した後、rng1とrng2に対し、クリア、マージ、ロック解除、罫線の処理を施しています。この結果は、所要時間は、0.624秒でした。確実に早くはなりました。これで限界でしょうか、もう少しやってみましょう。早くなりそうな記述を全て修正してみました。Subプログラム内のコメントは消しました。太字が主な変更箇所です。変更点は以下1.毎回シート取得のFunctionを呼ばず、先頭で変数へ保存し、それを使用する。変更この結果は、所要時間は、0.562秒でした。これ以上は、あまり意味がないので、終わりにします。最初の二重起動については、バグです。実害はありませんが、私は、このような場合はバグと認識しています。大きな時間差ではありませんが、プログラムを拡張していく過程で、思わぬバグを招く可能もあります。その後、項目数を増やしての挑戦は、0.868秒→0.624秒→0.562秒まあ、確実に速度は向上してはいます。Unionを使って、Rangeをまとめて処理するようにした場合の効果は大きいです。私は、このコーディングを是とはしません。非常に処理時間がかかっており、少しでも早くしたいとの明確な理由があれば、このようなテクニックも止むを得ないないでしょうが、通常はここまでやる必要はないと思います。おいて、どうしても先のプログラムより劣るからです。数秒(ここでは1秒未満)の処理を半減したからと言って、何の意味があるかが問題です。1日に数百回と繰り返し処理するなら、5秒が3秒になるとしても価値はあるかも知れません。処理では、あまり意味を見出せません。上記理由において、本ソフトにおいては、現時点で、二重起動を防止した時点のプログラムを採用します。処理速度の向上はどこまでやれば良い(GW特別号No3)このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。掲載のVBAコードは自己責任で使ってください。万一データ破損等の損害が発生しても責任は負いません。