エクセル顧客管理 | 第16回.処理速度の向上はどこまでやれば良い(GW特別号No3) | Excelマクロを駆使したカスタマイズ可能なエクセル顧客管理、エクセルVBAの学習教材



最終更新日:2014-11-11

第16回.処理速度の向上はどこまでやれば良い(GW特別号No3)


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


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


前回の「記述による処理速度の違い」が好評(本当に?)のようでしたので、


再度、処理速度に関する内容をお届けします。



実際に、作成中の顧客管理で処理速度対策を施してみます。



具体的にどの処理を対策するかですが、


以前から気になっていたのですが、「顧客一覧」でF1を押して、「顧客登録」に表示する際に、


私の古いノートPCでは、カーソルが砂時計になるのが、完全に目視出来ます。


処理内容からいって、これは明らかに遅いと感じます。(この感じるは、経験上からです)


では、この処理について、処理速度対策を施してみます。



タイム測定の為に、各モジュールの先頭と最後に、Debug.Printで、Timer関数を表示します。


処理時間が短いので、ミリ秒を測定する為に、Timer関数を使用します。


Timer関数は0時0分0秒から現在までの経過秒数を、小数2桁で返します。


全処理所要時間は、0.23秒でした。



その結果判明したのは、Subモジュール「顧客一覧より取得」が2度起動されている事です。


実は、事前に把握していたのですが、機会があったらイベント処理の注意として説明するつもりでした。


なぜ2度起動されるかの理由は、ごく簡単に説明します。(詳細には説明しきれませんので)


まず、シート「顧客登録」に、Worksheet_Changeのイベントを作成しました。

Subモジュール「マクロ開始処理」で、イベントの発生を停止しているのですが、

同じモジュール内で「マクロ終了処理」も実行している為、

モジュール終了後に、イベントが処理されてしまっていました。

これは、他のSubモジュールからCallしている為に発生していると思われます。


そこで、まずこの、2重起動しないように修正します。


各Subモジュールに以下の変更を加えます。

対象モジュールは、

「顧客登録シート作成」「顧客一覧より取得」「顧客一覧へ登録」の3つです。


Sub モジュール名(Optional ByVal blnEvent As Boolean = False)

If blnEvent = False Then Call マクロ開始処理

If blnEvent = False Then Call マクロ終了処理

太字部分が修正箇所になります。


(Optional ByVal blnEvent As Boolean = False)は、

Optionalが付いた引数は、省略可能な引数で、省略された場合は、この値が引数に設定されます。

つまり、引数が省略された場合は、いままで通りに動作させ、

引数にTrueが指定された場合は、マクロ開始処理、マクロ終了処理を行わないようにしました。


次に、Subモジュール「ファンクションF1」を以下に変更します。


Case ActiveSheet Is シート取得("顧客一覧")
  If Not IsEmpty(Cells(ActiveCell.Row, 開始セル取得("顧客一覧").Column)) And _
    ActiveCell.Row > 開始セル取得("顧客一覧").Row Then
    マクロ開始処理
    strWork = Cells(ActiveCell.Row, 開始セル取得("顧客一覧").Column)
    Call 顧客登録シート作成(True)
    開始セル取得("顧客登録").Offset(0, 1) = strWork
    Call 顧客一覧より取得(True)
    マクロ終了処理


ここは、前述の修正を受けて、マクロ開始処理、マクロ終了処理を組み込み、

各モジュールは、(false)を付けて呼ぶことで、マクロ開始処理、マクロ終了処理を止めています。


各Subモジュールから、マクロ開始処理、マクロ終了処理を消してしまっても良いのですが、

単独使用の余地を残すことと、Optionalの説明もできますので。


また、イベントの発生を期待して、「顧客一覧より取得」をCallしない方法もありますが、

他人任せはキライなので(笑)


この結果の所要時間は、0.16秒でした。


この程度の時間短縮でも、砂時計を目視するのは困難になりました。


普通に考えれば、この程度で十分です。


重要な事は、変なテクニックを使う事より、正攻法で問題点を解決することです。



しかし、それではブログとしてはつまらないので、どこまで早くなるかやってみます。


既に処理時間が、計測可能範囲ぎりぎりですので、少し処理時間がかかるようにします。


「顧客一覧」の項目を、200項目に増やします。


この時点での、所要時間は、0.868秒でした。


百分の一秒の争い(オリンピック並み)なので、5回の実行の平均を算出しています。



では、処理速度対策をします。


まず気になるのが、セルの結合、罫線等のセルに対する操作が、列数分行われている事です。


これを全部、ひとまとめにしてしまいます。


Sub 顧客登録シート作成(Optional ByVal blnEvent As Boolean = False)
  Dim r1 As Long, c1 As Long
  Dim r2 As Long, c2 As Long
  Dim intW As Integer
  Dim rng1 As Range, rng2 As Range
  If blnEvent = False Then Call マクロ開始処理

  r1 = 開始セル取得("顧客一覧").Row
  c1 = 開始セル取得("顧客一覧").Column
  r2 = 開始セル取得("顧客登録").Row
  c2 = 開始セル取得("顧客登録").Column

  With シート取得("顧客登録")
    .Unprotect
    .UsedRange.Clear
    
    Do Until IsEmpty(シート取得("顧客一覧").Cells(r1, c1))
      シート取得("顧客一覧").Cells(r1, c1).Copy .Cells(r2, c2)
      シート取得("顧客一覧").Cells(r1 + 1, c1).Copy .Cells(r2, c2 + 1)
      intW = Round(シート取得("顧客一覧").Columns(c1).Width / .Columns(c2 + 1).Width, 0)
      
      If rng1 Is Nothing Then
        Set rng1 = .Range(.Cells(r2, c2), .Cells(r2, c2 + 1 + intW))
        Set rng2 = .Range(.Cells(r2, c2 + 1), .Cells(r2, c2 + 1 + intW))
      Else
        Set rng1 = Union(rng1, .Range(.Cells(r2, c2), .Cells(r2, c2 + 1 + intW)))
        Set rng2 = Union(rng2, .Range(.Cells(r2, c2 + 1), .Cells(r2, c2 + 1 + intW)))
      End If
      
      c1 = c1 + 1
      r2 = r2 + 2
    Loop
    
    rng2.ClearContents
    rng2.MergeCells = True
    rng2.Locked = False
    rng1.Borders.LineStyle = xlContinuous
    
    .EnableSelection = xlUnlockedCells
    .Protect DrawingObjects:=False, Contents:=True, Scenarios:=False
    .Select
    開始セル取得("顧客登録").Offset(0, 1).Select
  End With
  
  With ActiveWindow
    .DisplayGridlines = False
    .DisplayHeadings = False
  End With
  
  If blnEvent = False Then Call マクロ終了処理
End Sub

プログラム内のコメントは消しました。太字が主な変更箇所です。


操作対象となるセル範囲を、Rangeに全て入れてしまいます。


rng1は、見出し+データ入力セル

rng2は、データ入力セル

を入れます。

Unionは、複数のRangeを1つのRangeにする事が出来ます。


全ての行の処理が終了した後、rng1とrng2に対し、

クリア、マージ、ロック解除、罫線の処理を施しています。


この結果は、所要時間は、0.624秒でした。


確実に早くはなりました。



これで限界でしょうか、もう少しやってみましょう。


とにかく、少しでも早くなりそうな記述を全て修正してみました。


Sub 顧客登録シート作成3(Optional ByVal blnEvent As Boolean = False)
  Dim r1 As Long, c1 As Long
  Dim r2 As Long, c2 As Long
  Dim intW As Integer
  
  Dim sht一覧 As Worksheet, sht登録 As Worksheet
  Dim rng1 As Range, rng2 As Range
  Dim var As Variant
  Dim i1 As Long, i2 As Long
  
  If blnEvent = False Then Call マクロ開始処理

  r1 = 開始セル取得("顧客一覧").Row
  c1 = 開始セル取得("顧客一覧").Column
  r2 = 開始セル取得("顧客登録").Row
  c2 = 開始セル取得("顧客登録").Column

  Set sht一覧 = シート取得("顧客一覧")
  Set sht登録 = シート取得("顧客登録")
  var = sht一覧.Range(sht一覧.Cells(r1, 1), sht一覧.Cells(r1, sht一覧.Cells.SpecialCells(xlLastCell).Column))
  i2 = r2
  With sht登録
    .Unprotect
    .UsedRange.Clear

    For i1 = c1 To UBound(var, 2)
      If var(1, i1) = "" Then
        Exit For
      End If
      sht一覧.Cells(r1, i1).Copy .Cells(i2, c2)
      sht一覧.Cells(r1 + 1, i1).Copy .Cells(i2, c2 + 1)
      intW = Round(sht一覧.Columns(c1).Width / .Columns(c2 + 1).Width, 0)
      
      If rng1 Is Nothing Then
        Set rng1 = .Range(.Cells(i2, c2), .Cells(r2, c2 + 1 + intW))
        Set rng2 = .Range(.Cells(i2, c2 + 1), .Cells(i2, c2 + 1 + intW))
      Else
        Set rng1 = Union(rng1, .Range(.Cells(i2, c2), .Cells(i2, c2 + 1 + intW)))
        Set rng2 = Union(rng2, .Range(.Cells(i2, c2 + 1), .Cells(i2, c2 + 1 + intW)))
      End If
      i2 = i2 + 2
    Next
    
    rng2.ClearContents
    rng2.MergeCells = True
    rng2.Locked = False
    rng1.Borders.LineStyle = xlContinuous
    
    .EnableSelection = xlUnlockedCells
    .Protect DrawingObjects:=False, Contents:=True, Scenarios:=False
    .Select
    .Cells(r2, c2 + 1).Select
  End With
  
  With ActiveWindow
    .DisplayGridlines = False
    .DisplayHeadings = False
  End With
  
  If blnEvent = False Then Call マクロ終了処理
End Sub

プログラム内のコメントは消しました。太字が主な変更箇所です。


変更点は以下

1.毎回シート取得のFunctionを呼ばず、先頭で変数へ保存し、それを使用する。

2.セルの終了判定を、配列を使用する

3.Do Loop を For Next に変更


この結果は、所要時間は、0.562秒でした。


とにかく、少しは早くなりました。


さすがに、もうこれ以上は、あまり意味がないので、終わりにします。



最初の二重起動については、バグです。


実害はありませんが、私は、このような場合はバグと認識しています。


大きな時間差ではありませんが、プログラムを拡張していく過程で、思わぬバグを招く可能もあります。


そして、その後、項目数を増やしての挑戦は、


0.868秒→0.624秒→0.562秒


まあ、確実に速度は向上してはいます。


特に、Unionを使って、Rangeをまとめて処理するようにした場合の効果は大きいです。


しかし、私は、このコーディングを是とはしません。

非常に処理時間がかかっており、少しでも早くしたいとの明確な理由があれば、

このようなテクニックも止むを得ないないでしょうが、通常はここまでやる必要はないと思います。

つまり、「可読性」「保守性」「開発速度」において、どうしても先のプログラムより劣るからです。

特殊な命令や、多数の変数を使う事は、確実に、「可読性」「保守性」を落とします。

数秒(ここでは1秒未満)の処理を半減したからと言って、何の意味があるかが問題です。

例えば、1日に数百回と繰り返し処理するなら、5秒が3秒になるとしても価値はあるかも知れません。

しかし、1日数回、数十回程度の処理では、あまり意味を見出せません。


上記理由において、本ソフトにおいては、


現時点で、二重起動を防止した時点のプログラムを採用します。






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

第17回.商品マスタを作成、2段階の可変リスト
第18回.納品書を作成、顧客情報を取得(1)
第19回.納品書を作成、顧客情報を取得(2)
第20回.納品書を作成、顧客情報を取得(3)
第21回.イベント処理について
第22回.コントールについて
第23回.納品書を作成、商品情報を取得(1)

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

空白セルを正しく判定する方法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特別号No3)

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


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




    ↑ PAGE TOP