Excelで謎の肥大化

TL;DR

Excelの容量肥大化では、以下の順に切り分けましょう
1. 見た目で明らかにオブジェクトが詰め込まれていないか
2. Ctrl+Endで、想定以上に広い範囲が指定されていないか
3. [ホーム]-[選択]-[オブジェクトの選択と表示]で、想定以上のオブジェクトが表示されないか
ちょっとしたプログラムをぱっと作るのにChatGPTは便利

経緯

あるExcelファイルが開くと動作が遅いのでなんとかならないかと問い合わせがありました。とりあえずファイル送ってもらい、調べました。

１ファイルで容量が3MBを超えており、かなり大きいです。数式が重いか、データが重いかで原因を考えていましたが、少なくとも後者は原因になりそうです。

シート数は2つ。行は1000近くまで行っているので小さくはないがそれほどでもない感じ。中にあるのは工程表で、罫線の表に線オブジェクトが多数という感じでむちゃくちゃ多いというほどでもない感じ。

この手の肥大化でよくあるのは、利用者が気づかないうちにデータが入力されているセルの範囲が広がってしまっているケース。この場合はスクロールバーがパット見の工程表の範囲よりも広く設定されいるかどうかでわかりますが、どうもそうではなさそうでしｇた。

一方のシートが見た目上データが多く、こちらのシートが原因かなと思い、少しずつ行を削除して切り分けていこうと考えました。が、とにかく動作が重い。別の画面にフォーカスを移して戻ってくるだけでマウスがくるくる回ります… 上下にスクロールするのもしんどい感じ。

なんとか頑張って、複数ある工程表のうち、最初のものだけ残して他を削除しましたが、容量は大きいまま。で、この表を削除してもまだ容量は大きいままでした。オブジェクトの範囲選択で怪しそうな場所を選択しても、それらしいオブジェクトは無し。

そこで改めて[ホーム]-[選択]-[オブジェクトの選択と表示]を見ようとしたところ、表示までに5-10分かかり、大量のテキストボックスが表示されました、これだ… 通常であればここで1つを選択した状態でCtrl+Aですべての図形を選択できるようなのですが、重すぎていつまで立っても選択が終わりません…

色々試行錯誤したのですが、VBAでテキストボックスを削除してしまうのが手っ取り早そうだと考えました。こういったときに使ってみようと思ったのがChatGPT。Bingで使えるようになっているので、すべてのテキストボックスのオブジェクトを削除するVBAを聞いて返ってきた内容を微調整したのが以下

Sub DeleteTextBoxes()
    Dim ws As Worksheet
    Dim shp As Shape
    Dim n As Long

    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual 
    For Each ws In ThisWorkbook.Worksheets
        For Each shp In ws.Shapes
            Debug.Print "[" &ws.Name & "シートの処理を開始" & "]"
            If shp.Type = msoTextBox Then
                shp.Delete
                n = n + 1
                Debug.Print n & "個目のテキストボックスを削除しました"
            Else
                Debug.Print "テキストボックスではないためスキップ"
            End If
        Next shp
    Next ws

    Application.Calculation = xlCalculationAutomatic 

End Sub

ScreeenUpdatingとCalculationを追加しただけです。いや、ChatGPTすごいな。もちろんGoogle検索してもそれっぽいのにヒットするだろうけど、それっぽいのを見つけて今回のケースに合うように修正して…とかの手間を考えると、すごく時間を節約できました。

上記VBAを実行。最初のDebug出力が出てくるまでに10-20分待たされましたが、出力され始めたら瞬時に終了。表示されたテキストボックスの数は100万を超えていました。そりゃ重いわ。なんでこんな事になったのかはもはやわかりません。

なにせExcelの動作が遅かったこともありますが、切り分けに時間がかかってしまいました。Excelの肥大化はTL;DRの手順でだいたい切り分けられると思うので、次回はもっと早く処理できるはず。

TL;DR#

経緯#

TL;DR

経緯