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

ExcelマクロVBAの問題点と解決策、VBAの技術的解説
公開日:2013年5月以前 最終更新日:2019-02-21

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


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


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

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

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

1.変数の型指定

2.罫線の引き方

3.行高の変更

4.配列を使用した処理

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

1.変数の型指定


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

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.罫線の引き方


以下の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.行高の変更


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

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

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

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

結果は、

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

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

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

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

4.配列を使用した処理


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

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においては、むやみにセルを参照するのは、なるべく控えた方が良い事も事実です。
そして、
処理速度が遅いマクロがあった場合の対処としては、
配列使用は極めて有効な対策となります。

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


1.画面描画を停止、計算を手動
  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)
大量データにおける処理方法の速度王決定戦


新着記事NEW ・・・新着記事一覧を見る

ExcelマクロVBA入門目次|エクセルの神髄(2024-03-20)
VBA10大躓きポイント(初心者が躓きやすいポイント)|VBA技術解説(2024-03-05)
テンキーのスクリーンキーボード作成|ユーザーフォーム入門(2024-02-26)
無効な前方参照か、コンパイルされていない種類への参照です。|エクセル雑感(2024-02-17)
初級脱出10問パック|VBA練習問題(2024-01-24)
累計を求める数式あれこれ|エクセル関数応用(2024-01-22)
複数の文字列を検索して置換するSUBSTITUTE|エクセル入門(2024-01-03)
いくつかの数式の計算中にリソース不足になりました。|エクセル雑感(2023-12-28)
VBAでクリップボードへ文字列を送信・取得する3つの方法|VBA技術解説(2023-12-07)
難しい数式とは何か?|エクセル雑感(2023-12-07)


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

1.最終行の取得(End,Rows.Count)|VBA入門
2.RangeとCellsの使い方|VBA入門
3.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
4.繰り返し処理(For Next)|VBA入門
5.変数宣言のDimとデータ型|VBA入門
6.ブックを閉じる・保存(Close,Save,SaveAs)|VBA入門
7.並べ替え(Sort)|VBA入門
8.条件分岐(IF)|VBA入門
9.セルのクリア(Clear,ClearContents)|VBA入門
10.マクロとは?VBAとは?VBAでできること|VBA入門




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


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



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