記述による処理速度の違い

公開日：2013年5月以前　最終更新日：2019-02-21

記述による処理速度の違い

記述の違いで、どの程度処理速度に変化があるかを検証します。

どのような記述が処理速度に影響するかという点を分かり易くするために、あえて極端なマクロVBAで検証をしています。

※本記事は2013年に書いたものを2019/2に再計測しつつ一部書き直したものです。

テスト内容は以下の４点です。

１．変数の型指定

2．罫線の引き方

3．行高の変更

4．配列を使用した処理

この４点について、実測して検証してみました。

１．変数の型指定

以下の２通りのプログラムで検証しました。


  
    
      Sub Test11()

      　　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 Test12()

      　　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

Test11とTest12の違いは太字の部分だけです。
つまり、
テストは変数に型指定するかしないかで、処理速度がどの程度変化するかの検証です。

結果は、複数回の平均で、

Test11=11秒
Test12=7秒

テスト機はCorei7ですので、現時点ではそれなりのPCです。

結果としては、結構差がつきましたね。

でも本当にそうでしょうか？

このテストでは、1000*1000*1000*2回の変数への値代入のテストです。
つまり、
20億回ですよ。
地球シミュレーターじゃないのですから、そんな処理は普通はしないですよね。

普通の処理なら、100万回だってやるかどうかでしょう。
つまり、
その場合でも差は、4秒/1000=0.004秒です。

100万回で、0.004秒ですよ、差がないのも同然と言えます。
少なくとも、体感として速度差を感じることは無いでしょう。
つまり、
変数における型指定は、処理速度の面ではあまり意味を持たないということです。
ではなぜ、
私の書くプログラムは、型指定を全て書くようにしているのでしょうか。

理由は全く別の所にあります。

私が型指定をする最大の理由

それは可読性を上げるためです。
例えば、
Integerの変数なら行数はいれないですよね、値がオーバーしてしまいますから。
つまり、
読み手に対し、「この変数には行数は入れません」、と宣言しているのです。
Integerを宣言するというより、「小さい整数しか入れません」、と読み手に宣言しているのです。

別の例としては、
Functionの戻り値の型では、単にObjectでは、どんなオブジェクトを返す関数か、
プログラムの内容を見ないと分かりません。
正確な型を指定することで、プログラムの内容を見なくても、
「あー、このFunctionはRangeを返す」と直ぐに理解できるようにしているのです。
もちろん、
コメントを書くことで伝える事はできますのでそれはそれで必要ですが、
その前に、まずはソースプログラムで伝えているのです。
ですから、
プログラムを他人に見せる事がないなら、それほど意味を持たない場合も出てきます。

2．罫線の引き方

以下の２通りのプログラムで検証しました。

Sub Test21()
　　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 Test22()
　　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行繰り返す。
結果は、複数回の平均で、

Test21=20秒
Test22=1秒

これはかなり差が付きました。
もちろん、
これは罫線だけでなく、セルをSelectしている事も大きく関係しています。

つまり、
初心者がマクロの自動記録で作ったマクロがいかに遅くなってしまうかの検証です。

少し、VBAを覚えれば、遥かにスピードアップするということです。

3．行高の変更

以下の２通りのプログラムで検証しました。


  
    
      Sub Test31()

      　　Dim i As Long

      　　Debug.Print Now

      　　　　For i = 1 To 
10000

      　　　　　　Rows(i).RowHeight = 15

      　　　　Next

      　　Debug.Print Now

      End 
Sub

      
      Sub Test32()

      　　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行の行高の変更です。

最初は、行高を標準のままにしておき、連続２回実行します。

つまり２回目は実質行高は変化しません。

結果は、

Test31=1回目:7秒、2回目7秒
Test32=1回目:7秒、2回目1秒未満

１回目はほとんど差がありません、
IF文での判定がある分Test2が若干遅い場合も出てきます。

問題は2回目です。
実際の行高は変化しないのに、Test1では常に同じ時間がかかっています。
この結果は、当然といえば当然です。
問題は、行高の変更の処理時間が結構かかるということです。
実際に高さが変わらないのなら、変更しないようにしたほうが良いということです。

Excel2010での注意

当初この記事を書いた時点(Excel2010:32bit)での実測では、
Test31の２回目が極端に遅い（1回目の数倍かかる）という現象が確認されました。
その計測は、決して間違いではなかったのですが、改めて再計測したところ、
現時点(Windows10+Excel2019(64bit))では、2回目も同タイムでしか計測できませんでした。
そこで、
PCを変えて、Ecel2010の32bitと64bitで何度か計測したところ、
2回目の方が多くの時間がかかる現象自体は再現されました。
しかし、
常に2回目の方が時間がかかるという現象が発生するわけではなく、
発生する場合と、発生しない場合があるようです。
3台のPCで検証しているので、決して特定の環境のみで発生するものではないと思われます。
どのような状況で発生するかまで特定できませんでしたが、
Excel2010をお使いの場合は注意しておく必要があります。

上記のコードは、速度差を単純比較するために1行ずつ行高変更していますが、
もちろん変更する範囲を一括で行高変更するほうがはるかに速くなります。

4．配列を使用した処理

以下の２通りのプログラムで検証しました。


  
    
      Sub Test41()

      　　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 Test42()

      　　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

プログラムは全然違いますが、やっていることは同じです。

1．100行100列のセルに「1」を入れる、これを100回繰り返す。
2．100行100列のセルから値を変数に取り出す、これを100回繰り返す。

つまり100万回のセルに対する読み書きのテストです。

Test42は、Function「最終行取得」で使ったテクニックです。（その時は多分早いで誤魔化しました）

結果は、

Test41=54秒、8秒
Test42=3秒、1秒未満

左の数値が、セルへの書き込み、右の数値が、セルからの取得です。

これは驚きですね！
これほどの時間差が付くのです。
しかし、
これとて、100万回のセルに対する読み書きなので、度々やることではないかもしれません。
ただし、
VBAにおいては、むやみにセルを参照するのは、なるべく控えた方が良い事も事実です。
そして、
処理速度が遅いマクロがあった場合の対処としては、
配列使用は極めて有効な対策となります。

処理速度を早くする為には

１．画面描画を停止、計算を手動
　　Application.ScreenUpdating = False　'画面描画を停止
　　Application.Calculation = xlCalculationManual　'計算を手動に

2．無駄な記述は減らす（ただし、引数の省略はご注意を）

3．プロパティを事前に取得し、変更が無い場合は、むやみに変更しない

4．セルへの同一操作は、Rangeで一括にする。

5．ワークシート関数（WorksheetFunction）が使えるなら、なるべく使う。（これは早いようです。）

まだまだ、いろいろありますが、
後は最初のうちはそんなに気にする必要が無いと思います。

処理速度を上げる為に可読性を落とすのは、現在のPC性能を考えれば愚かしい事です。

今回は、難しい話では無かったので、参考にはなったと思います。

おそらく結構長くVBAをやっている人でも、正しく把握されていない場合もあると思われます。

私も今回実測してみて、速度差に多少以外な面もありましたので。

同じテーマ「マクロVBA技術解説」の記事

配列の使い方について
 VBAの配列まとめ（静的配列、動的配列）
最終行の判定、Rangeオブジェクトと配列、高速化の為に
記述による処理速度の違い
速度比較決定版【Range,Cells,Do,For,ForEach】
エクセルVBAのパフォーマンス・処理速度に関するレポート
 VBAのFindメソッドの使い方には注意が必要です
 マクロVBAの高速化・速度対策の具体的手順と検証
 動的2次元配列の次元を入れ替えてシートへ出力（Transpose）
大量データで処理時間がかかる関数の対処方法（SumIf）
大量データにおける処理方法の速度王決定戦

アクセスランキング・・・ランキング一覧を見る

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入門

ホーム
マクロVBA応用編
マクロVBA技術解説
記述による処理速度の違い

このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。

記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。

VBA技術解説
記述による処理速度の違い