問題点
VBAアセットでは、VBAを使って共同編集を可能にする方法を紹介しています。
この機能を実現するためには、テキストファイルのデータをテーブルに書き込む必要があります。ところが、これがめっぽう遅い場合があるのです。
原因
どういった場合に遅くなるのか、最初は良く分からなかったのですが、いろいろと試した結果「折り返して全体を表示する」が原因であることが分かりました。テーブル内のセルにこれが設定されていると、データの書き込み処理が数倍遅くなるのです。
書式は、テーブルだけではなくワークシート自体にも設定できます。テーブルとワークシートの両方に「折り返して全体を表示する」が設定されていると、ダブルで影響を受けることになります。
対策
ということで、テーブルにデータを書き込む前に「折り返して全体を表示する」を解除してやれば、書き込み速度が劇的に改善します。
「数値の表示形式」などの書式も「折り返して全体を表示する」ほどではありませんが影響があるので、データを書き込む前にClearFormatsメソッドを実行し、テーブル全体の書式をすべて解除するのが良さそうです。
'テーブルの書式を解除する
Range("Table").ClearFormats
--データの書込み--
テーブルだけではなく、ワークシートの書式も解除する必要があります。ワークシート全体の書式をすべて解除すると、テーブル見出しの書式まで解除されてしまうので、ワークシートの書式は手動で解除するのが良いでしょう。
どうしても書式を解除したくない場合は、いったんテーブル外のシートにデータを書き込んでからテーブルのセルにコピーしたり、いったんテーブルを解除してからデータを書き込んだりすることでも速度を改善できます。(ただし、ワークシートの書式による影響は残ります。)。
ちなみに、データを配列に入れてから一挙に書き込んでも、この問題は解決しません。テーブルへのデータの書込みの方法ではなく、書込み先のテーブルで行われる処理が原因だからです。
比較
対策の効果を比較するためのマクロを作成しましたので、細部はこちらでご確認下さい。
コメント
書き込みが遅くなる原因が判明しましたので、記事の内容を変更し、比較用マクロを掲載しました。
配列に関する記述は、本検証と無関係なので削除しました。