コピペを多用して動作が重くなってしまったブックをVBAで軽量化した事例

スポンサーリンク

同僚からの動作の重いブックについて相談

私の勤務先の同僚から「原因がよくわからないが、処理が重くなってしまったファイルを改良して使いやすくしたい」と相談を受けました。
その際ちょっと変わった状況になっていたので、備忘録と情報共有の目的で記事にしておきます。

セルの結合があるので動作が重い？

同僚が言うには、セルの結合が入っているので処理が重くなっていて、また修正に手間がかかるのが困るとのことでブックを確認しました。
ざっくりとした内容ですが、1つのブックの中にシートは一つで、その中には3種類の研修について1日のスケジュールが書かれた表になっていました。

5分刻みでスケジュールを調整したいらしく、コマ数に応じて行数を変更してセルを結合する書式で、確かにセルの結合は複数使われていました。
ただ感覚的な判断ですが、100行15列くらいの使用でセル結合を多用しただけで動作が重くなるのは考えにくい気がしました。

以前に新しいブックにシートの全セルをコピーしてそのままペーストすると動作が軽くなったことがあるとも同僚が言っていたこともセル結合が影響していないのではないかと思うきっかけでした。

スポンサーリンク

原因の調査と特定

とりあえず、変なところに情報が残っていてそれが悪さをしている可能性を考慮して使用していない行列をいったん削除してみても動作は変わりませんでした。
また、他のブックへのリンクも多少含まれていたのでリンクを切断してみたものの動作の重さは変わらなかったです。

次に同僚が以前に多少解消できたと話していた通りコピペで新しいシートに貼り付けようとしたら、PCがフリーズして動かなくなりました。
使用しているセルを選択してコピーしたので、フリーズする原因は使用している範囲（Range）上にあるということがわかりました。

そこで、試しにセルに含まれているオブジェクトを確認してみたところ無茶苦茶な数のテキストボックスが含まれていました。
後でVBAで数を確認したところ28,000個くらい配置されていましたw

誰かが何かのタイミングで思いがけず背景色・枠線なしのテキストボックスを作成し、行か列のコピーの際にそのテキストボックスも含めたコピーを繰り返した結果
ゾンビのように増えたテキストボックスが動作を重くしていたようです。

解決方法について

背景色・枠線なしの見えないテキストボックスが悪さをしているのは確認できましたが、選択するだけでフリーズしてしまう状況で、手作業での良い解決方法が思いつかず、VBAを使用してテキストボックスをすべて削除して解決を図りました。

使用したコードは以下の通りです。

Sub シート上のオブジェクトすべて削除()
    
    Dim x As Long: x = ActiveSheet.Shapes.Count
    Dim i As Long: 
For i = x To 1 Step -1
        ActiveSheet.Shapes(i).Delete
    Next i

End Sub

こちらのコードを実行した結果、無事にテキストボックスはすべて削除され、サクサク動作するようになりました。
テキストボックスなどのオブジェクトを見えない状態で作成した際、気づかずに増殖して動作に悪影響を及ぼすことがあるのは新しい発見でした。
共有するファイルの扱いには気を付けようと思いました。

その他の改善点

今回動作が重い原因は隠れたテキストボックスだったため不要なテキストボックスを削除することで動作は軽くなりました。
ただ、コマ数に応じてセルの結合を変更するのはかなり手間そうでした。

そこで以前こちらの記事で紹介したセルの結合を切り替えるVBAコードを組み込んでショートカットを割り当て、スムーズにコマの入力と時間の変更ができるようにして提案しました。

Sub セル結合とセル解除切替()
    
    If Selection.MergeCells = True Then
        Selection.MergeCells = False
    Else
        Selection.MergeCells = True
    End If

End Sub

そもそものフォーマットの問題点と改善方法について

今回のワークシートの使い方については「データの格納場所」と「配布・閲覧資料」を同時に1つのシートで完結させようとしている点が問題だと思います。
またセルの結合自体がそもそもその後の加工に向かないので使わないほうが良いという話もあります。
あるべき使い方としてはデータの格納場所としてテーブルの形式で構造化されたデータを用意し、それとは別に閲覧用シートに出力する形をとるのが望ましいと思います。

ただし、あるべき形でExcelファイルを運用しようとすると、フォーマットの作り方においてほかのセルの値を呼び出したりする知識が必要だったり、構造化データを格納する意識が必要になったり、難易度が上がります。

Excelは手軽に利用できる反面、使用する多くの方が関数などのExcelを便利に使う知識がないケースも多いです。
個人的にはどこまでデータ管理の知識を落とし込めるかは使用者（達）次第になるので今回の運用も致し方ないと思っています。

要するにデータと配布資料が1か所に集まっていて構造化データになっていないし、セルの結合を使用していて編集に時間がかかるフォーマットだったとしてもVBA
を用いてセルの結合などを効率化することで、編集を簡略化してしまえば実際の運用上で問題になることはないと考えています。
特に今回使用したコードはすごく簡単なのでメンテナンスも不要ですし、扱いが楽なのも問題ないと思う理由です。

逆に構造化データの概念とかをいきなり説明してフォーマットを強引に変更したり、運用を強制したりした方が使用者（達）の反感を買ってしまったり、なじめなかったりして実業務に悪影響になることも多い気がします。
（効率的な業務を推進したい身としては悩ましいですが。）

スポンサーリンク

コメント