エクセル顧客管理 | 第15回.記述による処理速度の違い(GW特別号No2) | Excelマクロを駆使したカスタマイズ可能なエクセル顧客管理、エクセルVBAの学習教材



最終更新日:2014-11-11

第15回.記述による処理速度の違い(GW特別号No2)


エクセルで顧客管理を作ろう、


ゴールデンウイーク特別号No2です、


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


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


1.変数の型指定

2.罫線の引き方

3.行高の変更

4.配列を使用した処理


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



1.変数の型指定


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


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を返す」と直ぐに理解できるようにしているのです。

もちろん、コメントを書くことで伝える事はできますが、まずは、ソースプログラムで伝えているのです。


ですから、プログラムを他人に見せる事がないなら、それほど意味がありません。



2.罫線の引き方


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

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を覚えれば、遥かにスピードアップするということです。



3.行高の変更


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


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にしておき、連続2回実行します。


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


結果は、


Test1=1回目:8秒、2回目63秒

Test2=1回目:9秒、2回目1秒未満


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


問題は2回目です。


実際の行高は変化しないのに、この結果は・・・


Test1の2回目が遅いのは、間違いではありません、何度やっても結果は同じです。


私は、経験則からこの結果を承知していましたが、理由は分かりません。


ネットを調べたら、何か分かるかも知れませんが・・・


どなたか、理由が分かる方がおりましたら、お教え願いたいです。



4.配列を使用した処理


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


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


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


1.100行100列のセルに「1」を入れる、これを100回繰り返す。

2.100行100列のセルから値を変数に取り出す、これを100回繰り返す。


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


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


結果は、


Test3=54秒、8秒

Test4=3秒、0.1秒(0.1秒は上記では実測できませんが、10000回の繰り返しで10秒です)


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


これは驚きですね!これは使えます。


しかし、これとて、100万回のセルに対する読み書きなんて、普通はやらないですよね。


ただ、VBAにおいては、むやみにセルを参照するのは、なるべく控えた方が良い事も事実です。



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


1.画面描画を停止、計算を手動

  Application.ScreenUpdating = False '画面描画を停止

  Application.Calculation = xlCalculationManual '計算を手動に

2.無駄な記述は減らす(ただし、引数の省略はご注意を)

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

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

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


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


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


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


おそらく、結構長くVBAをやっている人でも、正しい把握はしていない人が多いと思います。


私とて、今回実測してみて、多少以外な面もありましたから。






同じテーマ「エクセル顧客管理」の記事

第16回.処理速度の向上はどこまでやれば良い(GW特別号No3)
第17回.商品マスタを作成、2段階の可変リスト
第18回.納品書を作成、顧客情報を取得(1)
第19回.納品書を作成、顧客情報を取得(2)
第20回.納品書を作成、顧客情報を取得(3)
第21回.イベント処理について
第22回.コントールについて

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

空白セルを正しく判定する方法2|ExcelマクロVBA技術解説(5月6日)
フルパスをディレクトリ、ファイル名、拡張子に分ける|ExcelマクロVBA技術解説(4月15日)
テキストボックスの各種イベント|Excelユーザーフォーム入門(4月9日)
フォルダ(サブフォルダも全て)削除する、Optionでファイルのみ削除|ExcelマクロVBAサンプル集(4月4日)
最後の空白(や指定文字)以降の文字を取り出す|エクセル関数超技(3月26日)
先頭の数値、最後の数値を取り出す|エクセル関数超技(3月26日)
Excelファイルを開かずにシート名をチェック|ExcelマクロVBAサンプル集(3月23日)
数式の参照しているセルを取得する|ExcelマクロVBAサンプル集(3月18日)
CSVの読み込み方法(改の改)|ExcelマクロVBAサンプル集(3月17日)
変数とプロシージャーの命名について|ExcelマクロVBA技術解説(2月12日)

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

1.最終行の取得(End,Rows.Count)|ExcelマクロVBA入門
2.ひらがな⇔カタカナの変換|エクセル基本操作
3.RangeとCellsの使い方|ExcelマクロVBA入門
4.Range以外の指定方法(Cells,Rows,Columns)|ExcelマクロVBA入門
5.徹底解説(VLOOKUP,MATCH,INDEX,OFFSET)|エクセル関数超技
6.変数とデータ型(Dim)|ExcelマクロVBA入門
7.セルの参照範囲を可変にする(OFFSET,COUNTA,MATCH)|エクセル関数超技
8.セルのコピー&値の貼り付け(PasteSpecial)|ExcelマクロVBA入門
9.CSVの読み込み方法|ExcelマクロVBAサンプル集
10.定数と型宣言文字(Const)|ExcelマクロVBA入門



  • >
  • >
  • >
  • 記述による処理速度の違い(GW特別号No2)

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


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




    ↑ PAGE TOP