第15回.記述による処理速度の違い（GW特別号No2）
エクセルで顧客管理を作ろう、
ゴールデンウイーク特別号No2です、
今回は、記述の違いで、どの程度処理速度に変化があるかを検証します。
テストは以下の４点です。
１．変数の型指定
２．罫線の引き方
３．行高の変更
４．配列を使用した処理
この４点について、実測して検証してみました。
１．変数の型指定
以下の２通りのプログラムで検証しました。
Sub Test1()
Dim i As Long, j As Long, k As Long
Dim v1,
v2
Debug.Print Now
For i = 1 To 1000
For j = 1 To
1000
For k = 1 To 1000
v1 = 1
v2 =
v1
Next
Next
Next
Debug.Print Now
End Sub
Sub Test2()
Dim i As Long, j As Long, k As Long
Dim v1 As
Long, v2 As Long
Debug.Print Now
For i = 1 To 1000
For
j = 1 To 1000
For k = 1 To 1000
v1 = 1
v2 =
v1
Next
Next
Next
Debug.Print Now
End
Sub
Test1とTest2の違いは太字の部分だけです。
つまり、テストは変数に型指定するかしないかで、処理速度がどの程度変化するかの検証です。
結果は、
Test1=34秒
Test2=24秒
テスト機は、結構古いノートPCなので、読者のPCならもっと早いと思います。（笑）
で、結構差がつきましたね。
でも本当にそうでしょうか？
このテストでは、1000*1000*1000*2回の変数への値代入のテストです。
つまり、20億回ですよ。
地球シミュレーターじゃないのですから、そんな処理はしないですよね。
普通の処理なら、100万回だってやるかどうかでしょう。
つまり、その場合でも、10秒/1000=0.01秒です。
100万回で、0.01秒ですよ、差がないのも同然と言えます。
つまり、変数における型指定は、処理速度の面では、あまり意味を持たないということです。
ではなぜ、私の書くプログラムは、型指定をきっちりとしているのでしょうか。
理由は全く別の所にあります。
私が型指定をする理由
それは可読性を上げるためです。
例えば、Integerの変数なら、行数はいれないですよね、値がオーバーしてしまいますから。
つまり、読み手に対し、この変数には、行数は入れませんよ、と宣言しているのです。
Integerを宣言するというより、小さい整数しか入れません、と読み手に宣言しているのです。
特にFunctionの戻り値の型では、単にObjectでは、どんなオブジェクトを返す関数か、
プログラムの内容を見ないと分かりません。
正確な型を指定することで、プログラムの内容を見なくても、
「あー、このFunctionはRangeを返す」と直ぐに理解できるようにしているのです。
もちろん、コメントを書くことで伝える事はできますが、まずは、ソースプログラムで伝えているのです。
ですから、プログラムを他人に見せる事がないなら、それほど意味がありません。
２．罫線の引き方
以下の２通りのプログラムで検証しました。
| Sub Test1() Dim i As Long, j As Long Application.ScreenUpdating = False Debug.Print Now For i = 1 To 10000 Range(Cells(i, 1), Cells(i, 100)).Select Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone With Selection.Borders(xlEdgeLeft) .LineStyle = xlContinuous .ColorIndex = xlAutomatic .TintAndShade = 0 .Weight = xlThin End With With Selection.Borders(xlEdgeTop) .LineStyle = xlContinuous .ColorIndex = xlAutomatic .TintAndShade = 0 .Weight = xlThin End With With Selection.Borders(xlEdgeBottom) .LineStyle = xlContinuous .ColorIndex = xlAutomatic .TintAndShade = 0 .Weight = xlThin End With With Selection.Borders(xlEdgeRight) .LineStyle = xlContinuous .ColorIndex = xlAutomatic .TintAndShade = 0 .Weight = xlThin End With With Selection.Borders(xlInsideVertical) .LineStyle = xlContinuous .ColorIndex = xlAutomatic .TintAndShade = 0 .Weight = xlThin End With Selection.Borders(xlInsideHorizontal).LineStyle = xlNone Next Debug.Print Now Application.ScreenUpdating = True End Sub |
| Sub Test2() Dim i As Long, j As Long Application.ScreenUpdating = True Debug.Print Now For i = 1 To 10000 Range(Cells(i, 1), Cells(i, 100)).Borders.LineStyle = xlContinuous Next Debug.Print Now Application.ScreenUpdating = False End Sub |
100列のセルの四辺に実線を引く、これを10000行繰り返す。
結果は、
Test1=74秒
Test2=4秒
これはかなり差が付きました。
もちろん、これは、罫線だけでなく、セルをSelectしている事も関係しています。
つまり、初心者がマクロの自動記録で作ったマクロがいかに遅いかの検証です。
少し、VBAを覚えれば、遥かにスピードアップするということです。
３．行高の変更
以下の２通りのプログラムで検証しました。
Sub Test21()
Dim i As Long
Debug.Print Now
For i = 1 To
10000
Rows(i).RowHeight = 15
Next
Debug.Print Now
End
Sub
Sub Test22()
Dim i As Long
Debug.Print Now
For i = 1 To
10000
If Rows(i).RowHeight <> 15
Then
Rows(i).RowHeight = 15
End
If
Next
Debug.Print Now
End Sub
10000行の行高の変更です。
最初は、行高を標準の13.5にしておき、連続２回実行します。
つまり２回目は実質行高は変化しません。
結果は、
Test1=1回目:8秒、2回目63秒
Test2=1回目:9秒、2回目1秒未満
１回目はほとんど差がありません、IF文での判定がある分、Test2が若干遅い位です。
問題は２回目です。
実際の行高は変化しないのに、この結果は・・・
Test1の２回目が遅いのは、間違いではありません、何度やっても結果は同じです。
私は、経験則からこの結果を承知していましたが、理由は分かりません。
ネットを調べたら、何か分かるかも知れませんが・・・
どなたか、理由が分かる方がおりましたら、お教え願いたいです。
４．配列を使用した処理
以下の２通りのプログラムで検証しました。
Sub Test3()
Dim i As Long, j As Long, k As Long
Dim v As
Variant
Application.ScreenUpdating = False
Debug.Print Now
For i = 1 To
100
For j = 1 To 100
For k = 1 To 100
Cells(j, k) =
1
Next
Next
Next
Debug.Print Now
For i = 1 To
100
For j = 1 To 100
For k = 1 To 100
v = Cells(j,
k)
Next
Next
Next
Debug.Print Now
Application.ScreenUpdating = True
End Sub
Sub Test4()
Dim i As Long, j As Long, k As Long
Dim v As
Variant
Dim ary As Variant
Application.ScreenUpdating = False
Debug.Print Now
ary =
Range(Cells(1, 1), Cells(100, 100))
For i = 1 To 100
For j = 1 To
100
For k = 1 To 100
ary(j, k) =
1
Next
Next
Range(Cells(1, 1), Cells(100, 100)) =
ary
Next
Debug.Print Now
For i = 1 To 100
For j = 1 To
100
For k = 1 To 100
v = ary(j,
k)
Next
Next
Next
Debug.Print Now
Application.ScreenUpdating = True
End Sub
プログラムは全然違いますが、やっていることは同じです。
１．100行100列のセルに「1」を入れる、これを100回繰り返す。
２．100行100列のセルから値を変数に取り出す、これを100回繰り返す。
つまり100万回のセルに対する読み書きのテストです。
Test4は、Function「最終行取得」で使ったテクニックです。（その時は多分早いで誤魔化しました）
結果は、
Test3=54秒、8秒
Test4=3秒、0.1秒（0.1秒は上記では実測できませんが、10000回の繰り返しで10秒です）
左の数値が、セルへの書き込み、右の数値が、セルからの取得です。
これは驚きですね！これは使えます。
しかし、これとて、100万回のセルに対する読み書きなんて、普通はやらないですよね。
ただ、VBAにおいては、むやみにセルを参照するのは、なるべく控えた方が良い事も事実です。
処理速度を早くする為には
１．画面描画を停止、計算を手動
Application.ScreenUpdating = False '画面描画を停止
Application.Calculation = xlCalculationManual '計算を手動に
２．無駄な記述は減らす（ただし、引数の省略はご注意を）
３．プロパティを事前に取得し、変更が無い場合は、むやみに変更しない
４．セルへの同一操作は、Rangeで一括にする。
５．ワークシート関数（WorksheetFunction）が使えるなら、なるべく使う。（これは早いようです。）
まだまだ、いろいろありますが、後はそんなに気にする必要が無いと思います。
処理速度を上げる為に可読性を落とすのは、現在のPC性能を考えれば愚かしい事です。
今回は、難しい話では無かったので、参考にはなったと思います。
おそらく、結構長くVBAをやっている人でも、正しい把握はしていない人が多いと思います。
私とて、今回実測してみて、多少以外な面もありましたから。
同じテーマ「エクセル顧客管理」の記事
第12回.最終行の判定、Rangeオブジェクトと配列、高速化の為に
第13回.コントロールのボタンを配置
第14回.オブジェクトとプロパティの真実（GW特別号No1）
第15回.記述による処理速度の違い（GW特別号No2）
第16回.処理速度の向上はどこまでやれば良い（GW特別号No3）
第17回.商品マスタを作成、２段階の可変リスト
第18回.納品書を作成、顧客情報を取得(1)
第19回.納品書を作成、顧客情報を取得(2)
第20回.納品書を作成、顧客情報を取得(3)
第21回.イベント処理について
第22回.コントールについて
新着記事NEW ・・・新着記事一覧を見る
TRIMRANGE関数（セル範囲をトリム：端の空白セルを除外）｜エクセル入門(2024-08-30)
正規表現関数（REGEXTEST,REGEXREPLACE,REGEXEXTRACT）｜エクセル入門(2024-07-02)
エクセルが起動しない、Excelが立ち上がらない｜エクセル雑感(2024-04-11)
ブール型（Boolean）のis変数・フラグについて｜VBA技術解説(2024-04-05)
テキストの内容によって図形を削除する｜VBA技術解説(2024-04-02)
ExcelマクロVBA入門目次｜エクセルの神髄(2024-03-20)
VBA10大躓きポイント（初心者が躓きやすいポイント）｜VBA技術解説(2024-03-05)
テンキーのスクリーンキーボード作成｜ユーザーフォーム入門(2024-02-26)
無効な前方参照か、コンパイルされていない種類への参照です。｜エクセル雑感(2024-02-17)
初級脱出10問パック｜VBA練習問題(2024-01-24)
アクセスランキング ・・・ ランキング一覧を見る
1.最終行の取得（End,Rows.Count）｜VBA入門
2.繰り返し処理（For Next)｜VBA入門
3.セルのコピー&値の貼り付け（PasteSpecial）｜VBA入門
4.変数宣言のDimとデータ型｜VBA入門
5.RangeとCellsの使い方｜VBA入門
6.ブックを閉じる・保存（Close,Save,SaveAs）｜VBA入門
7.セルのクリア（Clear,ClearContents）｜VBA入門
8.メッセージボックス(MsgBox関数)｜VBA入門
9.条件分岐（Select Case）｜VBA入門
10.ブック・シートの選択（Select,Activate）｜VBA入門
このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。
記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。