第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日数回、数十回程度の処理では、あまり意味を見出せません。
上記理由において、本ソフトにおいては、
現時点で、二重起動を防止した時点のプログラムを採用します。
同じテーマ「エクセル顧客管理」の記事
第13回.コントロールのボタンを配置
第14回.オブジェクトとプロパティの真実(GW特別号No1)
第15回.記述による処理速度の違い(GW特別号No2)
第16回.処理速度の向上はどこまでやれば良い(GW特別号No3)
第17回.商品マスタを作成、2段階の可変リスト
第18回.納品書を作成、顧客情報を取得(1)
第19回.納品書を作成、顧客情報を取得(2)
第20回.納品書を作成、顧客情報を取得(3)
第21回.イベント処理について
第22回.コントールについて
第23回.納品書を作成、商品情報を取得(1)
新着記事NEW ・・・新着記事一覧を見る
エクセル試験3:月間の所定労働時間|エクセル練習問題(2023-10-04)
エクセル試験2:所得税の計算|エクセル練習問題(2023-10-04)
エクセル試験1:曜日別の平均客単価|エクセル練習問題(2023-10-04)
列全体を指定する時のRangeとColumnsの違い|ツイッター出題回答 (2023-09-24)
シートのActiveXチェックボックスの指定方法|ツイッター出題回答 (2023-09-24)
ByRef引数の型が一致しません。|ツイッター出題回答 (2023-09-22)
シートコピー後のアクティブシートは何か|ツイッター出題回答 (2023-09-19)
Excel関数の引数を省略した場合について|ツイッター出題回答 (2023-09-14)
セル個数を返すRange.CountLargeプロパティとは|VBA技術解説(2023-09-08)
記号を繰り返してグラフ作成(10単位で折り返す)|ツイッター出題回答 (2023-08-28)
アクセスランキング ・・・ ランキング一覧を見る
1.最終行の取得(End,Rows.Count)|VBA入門
2.RangeとCellsの使い方|VBA入門
3.繰り返し処理(For Next)|VBA入門
4.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
5.変数宣言のDimとデータ型|VBA入門
6.マクロとは?VBAとは?VBAでできること|VBA入門
7.ブックを閉じる・保存(Close,Save,SaveAs)|VBA入門
8.条件分岐(IF)|VBA入門
9.並べ替え(Sort)|VBA入門
10.Range以外の指定方法(Cells,Rows,Columns)|VBA入門
このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。
記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。