こんにちは。Excel開発15年、セルネッツの竹本です。
今回は「99%が知らないExcel応答なしを回避する設計方法」についてお話をしていきます。
まずExcelが応答なしになるケースです。
それぞれ理由があっての応答なしになりますが、原因をまず突き止める必要があります。
よくあるのがサイズが大きいことで保存時の数式の計算をしているときに応答なしになったりとか、それからファイルがない、あるべき場所にあるべきファイルがなかったときにプログラムはその場所を探すことがあるので、ファイルがなかったとわかるまでの時間が応答なしになることがあります。
また、ユーザーフォームのDoイベントを入れていないケースでは、データの量が大量にあったりすると応答なしになったりします。
それ以外にもExcelが応答なしになる要因はいろいろありますが、今回はこの2番目、列幅の自動調整についてお話をしていきます。
皆さんは列幅の自動調整をどういうふうにやっていますか？
たとえばプログラム上で無条件でCells.EntireColumn.AutoFitをやってしまうと、データの量が多かったりすると応答なしになってしまうというお話をしていきます。
今回のデモはこちらの会員DBC、10万件あります。
このシート、列幅が狭い状態から3つの例をご紹介していきます。
まずはじめに遅いパターンをやってみます。このバツです、真ん中の赤いボタンを先にやってみます。
列幅調整AutoFit。今プログラムが動き始めています。
先ほどは22秒で終わりましたが、何度でも再現します。
ぐるぐる回っているところでもう1回押すとこの状態になります。今度は13秒で処理が完了しました。
実際の結果を見てみましょう。会員DB、きれいに列幅が調整されていました。
今度は速いほうです。青いボタンでやってみたいと思います。
結果はGの1に秒数が入ってきます。0秒ですね。
この差はコーディングによるもの、記述の仕方です。98書式から会員DBを遷移しましたが、列幅がきれいになっています。
まずバツだったほう、赤いほうです。なにがいけなかったのか解説をします。
まずタイマーの計測がここからスタートして、ここで会員データベースをCells.EntireColumn.AutoFitですね。
応答なしになってしまうので絶対にダメと書いていますが、これは件数によります。
たとえばテストのときは少ない件数でやりますが、大量データが想定される場合はなんとかしなければならないという気づきがあったと思います。
無条件でExcel任せの列幅自動調整はやらないようにしてください。
それが遅い理由です。13秒かかりました。
マルのほうですが、ここではタイマーを計測、ここでスタートします。
事前に対象シートの列番号、必要な列数分を指定された列幅にするということです。
ここの11シートの列数分をループさせています。
この11Fin列は直前で取っていますが、「98シート」のDの2に列数を書いています。
Dの2と言っているのはここです。
Dの2はアルファベット表記とした場合の最終列、数値形式にしたときの最終列、12列分をそれぞれ指定した列幅にするだけです。
6行目の今読み込んだ列をその列幅にするだけです。
6行目、6、7、7など、ここの数値を変えると列幅が変わります。
20だとわかりづらいので50に変えます。
試してみます。終わりました。
ここが50、今の数値の幅に変わっていることがわかったと思います。
A列が終わって、B列が終わって、次がC列と順番にやっていく必要がありますが、マクロ記録をするとアルファベット表記になってしまって回せないので、数値に変えないといけません。取ってきた列幅が適用されるようになります。
11列しかないので一瞬でしたが、列がたくさんあったとしても1列ずつは高速にデータの件数を問わずその列幅にするので、あとで変更することも可能ですし、表示したくない列があれば0に数値を変えることでその列は削除ではなく非表示になり、目視上は見えない状態になります。
実測がここにセットされて終わりました。これが0秒の早いほうです。
一番ダメな記述ということで、実際にはやりませんが説明だけします。
マクロ記録で普通にやった場合、まず会員DBにシートが遷移します。
次にすべてのセルを選択して、AutoFitして、カーソルをいったん外して、自身シートに戻ってカーソルをセットする。
速度としては真ん中の遅かったものとほとんど変わりないのですが、マクロ記録そのままというのが良くないです。
このシートを遷移するというところ、select構文が入っていますが、上の2つはシート遷移をしていません。シート遷移をしていないのでそのぶん早くなります。
ここで一番重要なところは、せっかくSQL構文を使ってデータベース、AccessでもOracleでも良いですが、高速でデータを取得したとしても、ここが遅いと高度な技術も台無しですので気をつけてください。
最後、まとめに入ります。
1番、応答なし。
理由はともかく応答なしが起きない作り方を心掛けたいものです。
実務レベルでは自分が作って自分がやるだけなので許容できると思いますが、製品として納入して動作を保証するとなると、プロテスト不合格となりますので気をつけてください。
2番目、やりがちな列幅の自動調整。
これはやりがちですよね。データの件数が少ないときは大丈夫ですが、有効データ範囲が大量にあると応答なしになってしまいますので気をつけてください。
今の「98書式」の中に列番号を固定で入れると、Excelのコマンドボタンが実行されてから処理が終わるまで列数が変わらないので、各モジュール内、各ファンクション、各プロシージャーの中で対象シートの列数を毎回求める記述は一切必要ありません。1箇所に集約するということで「98書式」に固定で書いてしまっています。
もし列数が変わったときはそこを直す。直したとしてもここが可視化されているので、どう直したのか、今どういう列数だと認識して動かしているのかがわかるようにしています。
ということで、これが表計算ソフトならではのメリットになります。
せっかくのExcelVBAですので、Excelの機能、表計算ソフト、二次元というワークシートを上手に使うスキルを磨いてください。
製品である以上ホームポジションというところも意識をしていただきたいと思います。ユーザビリティです。
ここをダブルクリックで列幅を指定したあとにこういう状態になっていたり、下のほうにカーソルが行っていたりということは避けたいものです。
ということで今回の解説は以上となります。少しでも参考になったかたはぜひチャンネル登録・高評価をお願いいたします。
毎週金曜日夜9時に投稿しています。ありがとうございました。
タケモ塾では、今後も皆さんのVBA学習に役立つコンテンツを作成してまいります。
ブログ記事、Youtubeチャンネルのご質問・ご感想・ご要望などお気軽にお問合せください。
お問合せはこちらから ✉
タケモ塾運営：株式会社セルネッツ
