エクセル VBAの処理速度について

エクセル VBAの処理速度について、ご存じでしたら教えてください。

エクセルVBA内でLoopで回してセルの値を連続で参照、設定する場合、
一度配列に格納して一括で処理した方が早いのは一般的に知られていると思いますが、
以下のようにセルからセルに値をコピーする場合も、

　 Range(A1:B3).value = Range(D1:E3).value

以下の一旦配列を経由させた方が処理速度は速いのでしょうか？

　 配列 = Range(D1:E3)
　Range(A1:B3) = 配列

上記はあくまでサンプルなので数セルのコピーですが、
実際は数万セルのコピーを数回実施する予定です。

No.2 ベストアンサー 回答者： Qchan1962 回答日時： 2020/05/29 18:00

こんにちは、

＞一度配列に格納して一括で処理した方が早い
つまり、一括での場合となります。

ご質問の場合、条件による抽出などないのであれば、
範囲＝範囲の方がわずかに早いかと思います。（配列に代入する処理分）

Excelは、レンジオブジェクトを変更する時などに多くの処理時間がかかります。

同じループ内に上記比較コードを入れた場合、書き出す命令は同じ回数行われますので
処理速度の差異は、ほぼないかと思います。（範囲＝範囲の方がわずかに早いかと思います。（配列に代入する処理分））

しかし、条件などでデータをいじる場合、配列はメモリーに一時記録できるので
ループを抜けてからまとめてRangeオブジェクトに書き込みが出来ます。

　 条件処理
　　i=i+1
　Loop
　Range("A1").Resize(UBound(配列),2) = 配列
結果、早く処理ができます。

関数、Excel機能の場合、その多くは起動時にメモリ確保されているので、メモリー上で計算され
配列同様高速ですが、書き込みについては、同様だと思います。
また、データの種類にもよりますが、他のインスタンスを利用する事で高速に処理が出来る場合もあります。
VBAは、遅い言語と私的に思っています。
この辺りは、詳しくないので100％信じる事なく参考程度にしてください。

処理時間に関しては、ご自身で検証する事を進めます。

Dim startTime As Double
Dim endTime As Double
Dim processTime As Double
'開始時間取得
startTime = Timer

'メイン処理

'終了時間取得
endTime = Timer
'処理時間計算
processTime = endTime - startTime
　Debug.Print processTime

参考、抜粋：【ExcelVBA入門】処理時間を計測するためのTimer関数の使い方とは
https://www.sejuku.net/blog/69319

この回答へのお礼

回答有難うございます。
コピー元とコピー先のRangeの開始位置と終了位置は可変ですが、条件による抽出はありません。
また、今回質問させて頂いた処理は各シートで計算・加工したデータを規定のフォーマットに埋め込んで行く
最後の処理ですのでLoop後の処理はありません。

今回は時間が無いので確認出来ませんが、落ち着いたタイミングで確認してみたいと思います。

お礼日時：2020/05/29 19:09

No.3 回答者： 銀鱗 回答日時： 2020/05/29 19:08

一般論ならVBAでは配列にしたほうが処理は早い。

しかし配列を積み過ぎれば当然遅くなる。

どこが境なのかは分かりません。

・・・
数式でも、作業列を設けて処理を分けて行ったほうが速い場合があります。
（行数が万単位であっても、[名前ボックス] で範囲を指定して [Ctrl]+[D] のショートカットキーを使えば一瞬で数式を範囲に複製できるので面倒なことは無い）

No.1 回答者： 銀鱗 回答日時： 2020/05/29 15:45

実際に試して比較して見れば良いと思います。

その結果をもとにどうするかを判断されてはいかがでしょう。

・・・余談・・・
自分はそんな簡単な処理は数式でやっちゃうのでVBAでどうなるのかは分かりません。
（セキュリティの面からマクロ禁止のユーザーもいるからねえ。可能なら数式だけで処理を終わらせるようにしています）

数式では、配列数式は比較的処理が遅いから、自分は滅多に使わない。
（数式の配列とVBAの配列は意味が違うけどね）

この回答へのお礼

回答有難うございます。
ＶＢＡが複雑で時間を掛ければ両方試すことは可能ですが、一般論としてどちらかが速いか知りたいのです。

サンプルは回答者の皆様がイメージしやすいように簡単な記述にしています。
実際はループ処理内でコピー元のRangeの開始位置、終了位置を計算し、コピー先の出力位置も計算しているため数式での対応が不可能です。
また、仮に数式で出来たとしても、ベースとなるデータは数万レコードが数個あり、
現状ピボットテーブルが数個、数式も既に数千個レベルで埋め込まれており、現時点でも既にエクセルが計算のために固まる状況です。

お礼日時：2020/05/29 18:44