テーブルへの書き込みは書式を解除してから行え

問題点

VBAアセットでは、VBAを使って共同編集を可能にする方法を紹介しています。

OneDriveを使わずにファイルを共同編集する

エクセルのファイルをOneDriveに保存すれば共同編集が可能になりますが、法律事務所の場合、セキュリティ上の懸念があります。そこで、更新したデータをデータごとにテキストファイルで保存することで複数のユーザーが同時に編集できるようにする方法を紹介します。

vba-assets.net

2021.05.05

この機能を実現するためには、テキストファイルのデータをテーブルに書き込む必要があります。ところが、これがめっぽう遅い場合があるのです。

原因

どういった場合に遅くなるのか、最初は良く分からなかったのですが、いろいろと試した結果「折り返して全体を表示する」が原因であることが分かりました。テーブル内のセルにこれが設定されていると、データの書き込み処理が数倍遅くなるのです。

書式は、テーブルだけではなくワークシート自体にも設定できます。テーブルとワークシートの両方に「折り返して全体を表示する」が設定されていると、ダブルで影響を受けることになります。

対策

ということで、テーブルにデータを書き込む前に「折り返して全体を表示する」を解除してやれば、書き込み速度が劇的に改善します。

「数値の表示形式」などの書式も「折り返して全体を表示する」ほどではありませんが影響があるので、データを書き込む前にClearFormatsメソッドを実行し、テーブル全体の書式をすべて解除するのが良さそうです。

'テーブルの書式を解除する
Range("Table").ClearFormats

--データの書込み--

テーブルだけではなく、ワークシートの書式も解除する必要があります。ワークシート全体の書式をすべて解除すると、テーブル見出しの書式まで解除されてしまうので、ワークシートの書式は手動で解除するのが良いでしょう。

どうしても書式を解除したくない場合は、いったんテーブル外のシートにデータを書き込んでからテーブルのセルにコピーしたり、いったんテーブルを解除してからデータを書き込んだりすることでも速度を改善できます。（ただし、ワークシートの書式による影響は残ります。）。

ちなみに、データを配列に入れてから一挙に書き込んでも、この問題は解決しません。テーブルへのデータの書込みの方法ではなく、書込み先のテーブルで行われる処理が原因だからです。

比較

対策の効果を比較するためのマクロを作成しましたので、細部はこちらでご確認下さい。