エクセルVBA コードが同じでもファイルによって処理速度が大きく変わるのはなぜ

テキストエディタに保存してあるVBAのコードを、必要に応じてエクセルファイルにコピペ、対象の行数など細かい点を修正して使いまわしています。
繰り返し処理を行う行数はファイルによって異なりますが、数としてはそれほど変わりません。
しかしファイルごとに処理完了までのスピードが大きく異なります。
早ければ10～20秒ほどで終わりますが、ファイルによっては何分も待ちます。
その理由が知りたいです。

VBAを走らせるPCも、使用しているエクセルのバージョンも同じです。
バックグラウンドで起動しているソフトも、だいたいいつもメーラーとブラウザです。

ひとつ思い当る相違点は処理をする列「以外」の列のデータ量ですが、作業をする列以外のデータ量は処理速度に影響しますか？

質問者からの補足コメント

• データはセル内の文字・数字・数式のみで図形や画像はありません。

    補足日時：2022/11/06 21:49

No.5 回答者： めぐみん_ 回答日時： 2022/11/07 06:11

処理を行ったＢｏｏｋが何度も上書き保存されてたとして、各ブックが全て同じ回数かどうかでもBookの肥大化で差が出るかもですね。

あとは保存等の繰り返しによるストレージの断片化が影響しているのかも？
⇒ＳＳＤでの速度低下については使用していないので不明ですけど。

他で言えばメモリを多く載せてもＥｘｃｅｌアプリケーションが管理できる容量には限度があると思いますので、もし連続で処理をさせていての処理時間の違いをご指摘ならば、使用できるメモリの空きが少なくなって要領確保の処理をしているのかもですね。

No.4 回答者： tatsumaru77 回答日時： 2022/11/06 22:32

＞処理対象の列にはないのですが、シート単位で再計算が行われているということでしょうか。

処理対象の列を参照している個所は全て再計算が行われます。
処理対象の列を参照していない箇所が再計算が行われるかどうかは私はわかりません。

マクロの最初のほうで、画面表示更新抑止、自動再計算抑止を行い、
マクロの最後のほうで、上記をもとに戻せば、速くなります。


'画面表示更新抑止、自動再計算抑止
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
・・・処理・・・・
'自動再計算、画面表示更新
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

この回答へのお礼

何度もありがとうございます、試してみます。

お礼日時：2022/11/06 23:24

No.3 回答者： tatsumaru77 回答日時： 2022/11/06 22:07

処理対象となっているシートに関数式が大量に埋め込まれていませんでしょうか。

セルの内容が変わると、その都度、再計算を行うため、関数式が大量にあると劇的に遅くなります。
再計算を抑止して、処理を行うようにすれば、その状況は改善可能です。

この回答へのお礼

ご回答ありがとうございます。
確かに今回処理が遅かったシートには関数があります。
処理対象の列にはないのですが、シート単位で再計算が行われているということでしょうか。

お礼日時：2022/11/06 22:14

No.2 回答者： hgfy76 回答日時： 2022/11/06 21:58

関係ないかもしれませんが、大昔、何万件のエクセルデータをVBAで処理してた時、同じファイル（データ）なのに、何かの操作の拍子で桁違いに重くなる時がありましたね。

あれこれ原因を考えるのですが、結局、結論が出ず、新規作成でエクセルファイルを新しく作り、そこにデータを全コピー＆ペーストしたらあっさり解決したことが多々ありました。

同様の質問をここで見たことありますが、やっぱり新規ファイルでエクセルファイルを作ってコピペしてあっさり解決したようですよ。
※原因は不明なんですが、経験則でいつもそうやって回避してました。

この回答へのお礼

ご回答ありがとうございます。
言われてみれば、確かに新規作成したファイルは処理が早かったように思います。

お礼日時：2022/11/06 22:15

No.1 回答者： t_fumiaki 回答日時： 2022/11/06 21:45

エクセルは中身を修正すると、表示データを全部”再描画”します。

この関係では無いですか？

また、そのシートにシェイプ(線画、図形）が有ると、輪を掛けて遅くなります。

シェイプの入ったシートを使い回して、「行や列毎に一機に削除してる」と油断してると、行・列・セルを削除しても一見シェイプは表示されなくなりますが、シェイプ自体は削除されません。
行・列・セルのコピーではシェイプはコピーされます。
結果としてシェイプが増殖されてしまいます。

この回答へのお礼

早速のご回答ありがとうございます。
説明が足りませんでした、データはセル内の文字・数字・数式のみで図形や画像はありません。

お礼日時：2022/11/06 21:48