【第1位】エクセルVBA高速化ランキング
今回発表していく高速化ランキングでは、「ちょっとしたテクニックを使う
だけで、エクセルVBAがこんなにも速く、サクサクと動くようになる!」
といった内容を、その効果の高い順に独断と偏見もまじえてランキングし、
同時に実測した結果も発表していこうというものです。
それでは、さっそく今日はその第1位の発表です。
(本来なら下から発表するのが筋なんですが、今回はワケあって(後ほど
お話しますが。。。)いきない1位!から発表させてもらいます。)
【画面更新の非表示】
ちょっと前置きになりますが、VBAのAというのは言わずと知れた(!?) アプリケーションの頭文字のAを指す訳ですが、エクセルVBAの場合は、 このアプリケーションが=(イコール)「エクセル」ということに他なりま せん。
一方、コンピュータシステムというものは、通常、入力・処理・出力の3つ のセッションからなっていますが、この内、入出力とりわけ出力というのが レスポンススピードのネックとなる場合が極めて多いものです。
エクセルVBAの場合は、この入出力の主なものは言うまでもなく「エクセルの シート」と言うことになりますので、他のコンピュータシステム同様、 エクセルVBAの場合には、エクセルのシートへの入出力(とりわけ出力) というのが、VBAの処理スピードを左右する一番の要因である場合が極め て高いということになります。
そこで、このエクセルシートへの読み書きを速くする方法として、最もよく 知られているのが、今日「エクセルVBA高速化ランキング」第1位として 発表する「画面更新の非表示」というものです。
構文
―――――――――――――――――――――――――+
Application.ScreenUpdating = False
―――――――――――――――――――――――――+
これは、知る人ぞ知るエクセルVBAの高速化にはダントツで効果大である ことはよく知られていますから、今回あえて第1位から発表した(発表せざ るを得なかった)理由というのも、もし下から発表していったら1位はこれ だ!って最初からバレバレなので、ランキング発表の面白みが半減してしま うことを恐れての理由からです。
で、これがどれだけダントツの1位かというと、
下記のサンプルプログラム(単純に1000行×100列のセルに数字の1を表示 させる)の測定では、
サンプル1が 154秒
サンプル2が 11秒
という具合に、圧倒的な実に14倍の差が出ました。
ここまで目覚しい差がある以上、高速化といったらまずこの構文を使って 「画面更新の非表示」をやってみない手はありません。
これが、エクセルVBAを高速化させるための一番であることは疑う余地の ない事実です。特にシートに対しての読み書きの多いプログラムの場合には その効果は絶大です。
サンプル1 ―――――――――――――――――――――――――+ For i = 1 To 1000 For j = 1 To 100 Cells(i, j).Select ActiveCell.FormulaR1C1 = 1 Next j Next i ―――――――――――――――――――――――――+ サンプル2 ―――――――――――――――――――――――――+ Application.ScreenUpdating = False For i = 1 To 1000 For j = 1 To 100 Cells(i, j).Select ActiveCell.FormulaR1C1 = 1 Next j Next i Application.ScreenUpdating = True ―――――――――――――――――――――――――+
ですが、ちょっと待ってください!!
「画面更新の非表示」って、ほんとうにただ非表示にしてしまっていいんで しょうか?
非表示って、詰る所ユーザーには見せない!ってことに他なりません。
いくら高速化したいからといって、デベロッパーが勝手にユーザーには見え ないプログラムなんてものを作ってしまってもよいのでしょうか?
実は、当センターではこの問題をほんのちょっとしたテクニックを使うこと によって回避しています。
その内容は、次回この続きとして詳しくお話することにします。
PS.
あと、止めるという意味ではもう一つ、 よく知られているのがセル参照系関数使ってるシートを処理する場合には よく起こりうる無駄な再計算の連発です。
私はセルの関数(長々した数式書くのは)好きでないので、 あまりこれは使いませんが、シートによく関数入れて使ってるタイプの人は、 先ほどの
―――――――――――――――――――――――――+
Application.ScreenUpdating = False
―――――――――――――――――――――――――+
この構文の後にでも、下記の
―――――――――――――――――――――――――+
Application.Calculation = xlCalculationManual
―――――――――――――――――――――――――+
再計算を手動でやる(すなわち、自動の再計算を止める)という構文を一つ入れておくと、 セルに入れた関数次第では劇的に速くなる可能性があります。
同様に、元への戻し方(止めた場合の再開方法)は、
―――――――――――――――――――――――――+
Application.Calculation = xlCalculationAutomatic
―――――――――――――――――――――――――+
となります。
けれど、やはり基本は、何処で何やっているか見えてこない、 都度バンバン再計算も起る数式(セルの関数)は、極力使わない (その処理もVBAコードに組み込んでやらせたほうがいい)というのが、 VBA知る人の肝要事ですね。
NEXT >>
・第1位(続き)画面更新の「半」非表示
・高速化ランキングの目次へ戻る