VBAでCOPYを繰り返すと、処理が途中でアイドルする原因はなんでしょうか

Question

A列からE列までの5列で約3000行のデータがあります。これらのデータは2つのグループのデータが混在しています。それをグループ毎に振り分ける処理を作成しました。処理は正常に動作したのですが、処理に数分程度かかりました。
そこで50行毎の処理時間をセルに表示させるようにしたところ、700行程度までは数秒で処理できていますが、その後1分程度、処理がアイドルしています。その後また700～800行を数秒で処理し、またアイドルするの繰り返しでした。
解決策は見つけましたが、処理がアイドルする原因は何として考えられるか、教えていただけませんか。これまでこのような現象は経験がなく、気になっています。よろしくお願いいたします。

問題のステートメント
Cells(idx, 1).Resize(1, 5).Copy Destination:=Cells(cntA, 7).Resize(1, 5)
Application.CutCopyMode = False　' ← この行は、あってもなくても現象は変わらない

なおCopyメソッドをやめて、地道に転記してみたら10秒程度で全ての処理が終わりました。
Cells(cntA, 7) = Cells(idx, 1)
Cells(cntA, 8) = Cells(idx, 2)
Cells(cntA, 9) = Cells(idx, 3)
Cells(cntA, 10) = Cells(idx, 4)
Cells(cntA, 11) = Cells(idx, 5)

Windows10　Office Professional Plus 2019　です。

fujillin · Accepted Answer

こんばんは

内部処理に関係する話なので、以下は勝手な憶測でしかありませんけれど・・

多分、クリップボードのメモリ割り当てなどに関連するのではないかと推測します。
＞Application.CutCopyMode = False
は、クリップボードをクリアする処理ではありますが、想像するところ管理部分の履歴をクリアしているだけで、割り当てエリアを完全にクリアしているのではないと想像します。
エリアをオーバーした際にはガベージコレクションのようなことが行われているのではないかと想像します。（そのため若干時間がかかる）

ご質問の内容は環境依存ではありますが、以下で簡単なテストを行ってみました。
速度に関しては、絶対値はＰＣ性能に依存しますので、方法の比較としての値と解釈してください。

まず、５列×30000行の値を単純コピーする方法で、コピーメソッドと、値の転記（Range.Valu=Range.Valueによる）で比べてみました。
・1セルずつでは（後述の理由で）時間がかかるので、1行単位で転記します。
・200行の処理毎に所要時間を計測します。

◇ コピーメソッドを利用した場合
全体で490秒ほどかかりますが、200行ごとの処理時間は2.5～5.5秒とかなりバラツキがあります。
途中で「クリップボードエラー」なるものが発生しました。（添付図）
（私も始めて見ましたが、多分これが原因に関係があるものと想像します。）

上記の表示が出ても、処理が止まることはありません。
200行ごとの処理を時間がかかっている3.5秒以上と、それ以下とに分けて集計してみると
　・2.5秒～3.5秒（117回）平均2.95秒
　・3.5秒～　　　（33回）平均4.37秒
となり、明らかに時間がかかる時が33回発生していることがわかります。
（多分、クリップボードのメモリ管理（？）に処理を取られているものと想像）

◇ 値の転記で処理した場合
（ご提示の解決策とほぼ同様の方法です）
1行ずつ30000回の転記ですが、処理時間に多少のバラツキはありますが、クリップボードを介さないこともあり、200行を0.12～0.21秒で処理できます。
全体では、24秒程度で処理が終わりました。

同じ、30000回の処理でクリップボードを介さない方が速いのは、クリップボードの管理以外にも情報量の差が考えられます。
セルのコピーでは、値の他に色や罫線等の書式や関数式、ハイパーリンクや条件付き書式などまでと多くの情報がコピーされますので、「値の転記」が値のみであるのに比べて、遥かに処理するデータ量が多いことが想像できます。

以下は、ご質問の内容からは離れてしまいますが、速度を気になさっているようですので・・
VBAの処理で時間がかかるのは「シートへのアクセス」です。
ですので、1セルずつのアクセスを繰り返すよりも、1行ずつあるいはセル範囲をまとめて処理するほど速度向上が望めます。
また、VBA自体が決して速くはないので、エクセルの種々の機能を利用する方が処理速度は速い場合も多くあります。

以下の3種類の方法を考えて、上記のデータでテストしてみました。
　1）エクセルのフィルター機能でグループ別にフィルターをかけ、
　　全体をコピペ（2回）で処理する。
　2）グループ情報を元にソートし、分割してコピペ
　3）グループ情報が同じセル範囲を一括でコピペ
方法としては、判定用の作業列を追加し、最後にその列を削除するという方法で行なっています。（サンプルとして、単純に奇数行、偶数行で分割）

1）フィルターの非表示行を省いてペーストするのは、コピーメソッドによらざるを得ませんが、実行時の状態によってかなりバラツキがあるようです。
（多分、クリップボードの使用量が多いのと、非表示行を省く処理のためか？）
全体で0.82秒～5.91秒と実行時の状態（？）により処理時間に差がありました。

2）全データをコピペしてから、判別用の関数式をまとめて作業列に設定。
この結果を元にエクセルの機能でソートし、下半分を別の位置にカット＆ペーストという手順で行います。
判別式の設定などは増えますが、データのコピペ（orカット）は2回で済みます。
こちらの場合は、バラツキは少なく、全体をほぼ0.20～0.34秒で処理できます。
（判別式が複雑だと、多少時間が増加する可能性はありますが・・）

3）離れた行でもまとめてコピーすれば詰めてペーストされるという機能（＝フィルター状態のコピペと同様）を利用しようと考えたものです。
少ない行数であれば問題なく速くできますが、さすがに30000行が対象だと（実質は15000行程度）セル範囲の計算でハングアップしてしまいました。
改めて対象を10000行にして行ってみると、98秒程かかりました。
どうやら、セル範囲の計算で使用しているUNIONがメッチャ時間がかかるようですね。
データ数が多い場合には、不適のようです。

番外）VBAのメモリを消費するので、上には示しませんでしたが、
　v = Range.Value
で、まとめて全データ（＝30000行）を読み込んで、グループ別にデータを配列に抜き出して、シートに（まとめて）戻すという方法。
計算は全てメモリ上で行うので、シートへのアクセスは3回で済みます。
事前には速いだろうと予想していましたが、結果は安定の0.34秒でした。

とりあえずの結論としては、0.2～0.3秒の 2）の方法が一番速そうですね。
一番バラツキが少なく安定しているのは、0.34秒の 番外）の方法でした。

以上、回答にはなっていませんけれど、ご参考にでもなれば・・

fujillin · Answer

No3です。

＞「たった3000行の処理に、なぜこんなに時間がかかるのだろう」
あら、3000行でしたかぁ・・
見かけ上停止状態が発生するということだったので、てっきり30000行と読み間違えて、そのまま思い込んでテストしてしまいました。

3000行程度であれば、No3のどの方法でもそれなりの効果はあるものと思います。

ssawatake · Answer

描画= Falseにしてるんですね・・・。

Copyメソッドは問題ないと思います。
.Resize(1, 5)が原因だと思います。

実験するしか有りませんが、ループで毎回広げる訳なので、普通に代入する場合の数十倍程度、copyメソッドでやる場合の数百倍程度の時間が掛ると思います。
(さっき試しましたが、大量ループ内でのResizeは驚く程時間掛りました)

vbaからの指令は連続で出ますが、受け取ったエクセル側に時間が掛るので、vba側が待たされる構造ですね。

ssawatake · Answer

セルに再描画してるからです。
Timer関数などの問題では無くて、結果をセルに入れてるからです。
エクセル仕様として再描画を止める設定をしない限り、結果を再描画するからです。
再描画に大部分の時間が掛ります。
ループで処理してる訳ですから、再描画が全部完了するまで次の処理が待たされます。

試しに、処理時間だけ計測してセルに結果を入れない場合と入れる場合を比較すれば一目瞭然です。

VBAでCOPYを繰り返すと、処理が途中でアイドルする原因はなんでしょうか

こんばんは

No3です。

描画= Falseにしてるんですね・・・。

セルに再描画してるからです。

似たような質問が見つかりました

このQ&Aを見た人はこんなQ&Aも見ています

関連するカテゴリからQ&Aを探す

このQ&Aを見た人がよく見るQ&A

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング