VBAで処理すると固まってしまう

Question

###前提・実現したいこと
処理のあとにExcelが固まってしまいます。
3,000件くらいの1つのセルに30文字くらいのテキストを入れていく処理です。これを固まらないように、安定して稼働させたいです。
1件1件saveしようとしても固まってしまい、無理でした。

重いデータを処理する上で、どのようなところに気を付けたらよいでしょうか。

###発生している問題・エラーメッセージ
固まる、強制終了となる。

###試したこと
課題に対してアプローチしたことを記載してください
処理を小分けにする。
1件づつセーブする。

どちらにせよ固まる。

Comments

[coco_bauer]

VBAのコードと、処理しているテキストのサンプルを質問に追加してください。また、「固まる」というのはExcelがCPUを使っている状態なのかも教えてください。

Answer 1

セルへの出力は色々イベントも起きるし重いから自分はVariant使って逃げたかなぁ

' とりあえず実質セルを複製した2次元配列を用意する
Dim Lists As Variant
' VBだとIndexの範囲を自由に決めることができるので実際のセル座標に合わせるとミスが少ない
ReDim Lists(StartRow To LastRow, StartCol To LastCol)

' なんかしら値をセットする処理
Lists(iRow, iCol) = 入れたいデータ

' Variantに格納完了したら最後はセルへ一気に出力
Worksheets("Sheet1").Range(Cells(StartRow, StartCol), Cells(LastRow, LastCol)).Value = Lists

これで１０時間以上かかった処理が数分で終わるようになりました。

Comments

[kazupop]

これすごいですね。処理が速いです。

Answer 2

「固まる」というのは本当に固まって動かないのでしょうか？
時間がかかっているだけなのでは？
VBAに限らずイベントドリブンな言語の場合、関数を抜けずにずっと処理し続けると、他のイベント処理が実行できずに固まったように見えます。
これを防ぐには、VBAの場合、DoEventsという関数をループ内などに設置すると防ぐことができます。
例

Do
  処理
  DoEvents
Loop

その他、処理速度を速めるためにzohnamさんが回答しているような手段も有効ですが、処理時間が長ければ結果は同じですので、どうしても短時間で終わらないような処理の場合はDoEventsを入れてみてください。

Answer 3

どこまで何を処理したか、Excelを強制終了させても状況を追えるように、
例えばテキストファイルに日時と何の処理をやったかログ出力したら良いと思います。

あるいは、ステップ実行して、
Debug.Printさせてもよいのですが、
処理件数が膨大な印象を受けましたので時間の無駄になりそうです。

であれば、Excelの外（テキストファイル）にログ出力する関数を作って、
文字列を渡せば固定パス上のテキストファイルに追記して処理を戻すだけにすれば、
落ちたり固まったりする場面で何が起きているのかを追いやすくなります。

Answer 4

無駄な表示を止める
ということは行っていますか？

Application.ScreenUpdating = False
(重い処理)
Application.ScreenUpdating = True

重い処理中は、いちいち画面に表示を反映させないようにすることで高速化するテクニックです。

Comments

[kazupop]

やってみます。ありがとうございます。

[kazupop]

速くなりました。ありがとうございます。