MS Excel マクロ(VBA) パフォーマンス改善方法 ３つ
今、Excel VBAの仕事をやっていて、パフォーマンス改善ってのがテーマの一つなんですよねぇ。プログラマーの常識的に考えて改善しそうなところを治したり、ネットで見つけた情報でいろいろやってみたりしたんですが、どうしても作った本人が耐えられないレベルの所があってねぇ(笑)。たかだか数百件の処理で5分以上待たされるって納得いかないっす。
そんなわけで今日は今抱えている課題と、たどり着いた解決法を書いてみましょうか。
そうそう。Excel は2003ね。2003。これ大事。
ネットで検索しても、常識的に考えてもまずこれは思い付く。
- 1.再計算の停止
当然です。マクロ流している最中に再計算なんかされたらかないません。
Application.Calculation = xlManual
で停止
Application.Calculation = xlAutomatic
で再開ですね。
これでかなり違います。
- 2.画面描画の抑止
これも当然。マクロを流している最中は画面描画なんてどうでもいいのでね。
Application.ScreenUpdating = False
で停止
Application.ScreenUpdating = True
で再開
他にも、RangeではなくCellsでセルを指定するとかいう情報もネットで見つけたんですが、今回触っているアプリではイマイチ効果が出ず……。
煮詰まったところで思い付いて劇的な効果があったのがこれ
- 3.複数シートを同時に処理しない
いや、わからんですよね。
- 3.詳細は別途
と書いた方がいいでしょうかね(笑)。
なにはともあれ、ここからが本題です。
今回パフォーマンスに問題があるエクセルファイルで何をしているかというとデスねぇ。これも説明めんどいんだよな。業務内容がわからないように、かつ読む人に伝わる様に描かなければならんのでね(笑)。
元シート
| A | B | |
|---|---|---|
| 1 | 表題1 | 中身1-1 |
| 2 | 表題1 | 中身1-2 |
| 3 | 表題1 | 中身1-3 |
| 4 | 表題2 | 中身1-1 |
| 5 | 表題2 | 中身1-2 |
| 6 | 表題2 | 中身1-3 |
っていうシートがあります。この中身を埋めていくのですよ。埋める相手は
素材シート１
| A | B | |
|---|---|---|
| 1 | 表題1 | 中身1-A |
| 2 | 表題2 | 中身2-A |
素材シート２
| A | B | |
|---|---|---|
| 1 | 表題1 | 中身1-B |
| 2 | 表題2 | 中身2-B |
素材シート３
| A | B | |
|---|---|---|
| 1 | 表題1 | 中身1-C |
| 2 | 表題2 | 中身2-C |
恐らく想像つくだろうけれど、欲しい結果はこれです。
結果シート
| A | B | |
|---|---|---|
| 1 | 表題1 | 中身1-A |
| 2 | 表題1 | 中身1-B |
| 3 | 表題1 | 中身1-C |
| 4 | 表題2 | 中身1-A |
| 5 | 表題2 | 中身1-B |
| 6 | 表題2 | 中身1-C |
簡単なんですよね。なのにうまくいかない。いや、うまくいくんだけれど爆裂おそいんですよ。
どういうロジックを組んだかというと
シートのオブジェクトをオブジェクト変数に格納(4枚分)
for i = 1 to 元シートの最大行数
for j = 1 to 素材シート１の最大行数
いろいろと条件判別したりして条件に合致したら素材シート１の該当セルを元シートの該当セルにコピー
next j
for j = 1 to 素材シート２の最大行数
いろいろと条件判別したりして条件に合致したら素材シート２の該当セルを元シートの該当セルにコピー
next j
for j = 1 to 素材シート３の最大行数
いろいろと条件判別したりして条件に合致したら素材シート３の該当セルを元シートの該当セルにコピー
next j
next i
何の問題もない。はず。ものはきちんとできる。なのにとてつもなく遅い。
で、論理的に考えるとやらない方がいいことをやってみた。
シートのオブジェクトをオブジェクト変数に格納(4枚分)
for i = 1 to 元シートの最大行数
for j = 1 to 素材シート１の最大行数
いろいろと条件判別したりして条件に合致したら素材シート１の該当セルを元シートの該当セルにコピー
next j
next i
for i = 1 to 元シートの最大行数
for j = 1 to 素材シート２の最大行数
いろいろと条件判別したりして条件に合致したら素材シート２の該当セルを元シートの該当セルにコピー
next j
next i
for i = 1 to 元シートの最大行数
for j = 1 to 素材シート３の最大行数
いろいろと条件判別したりして条件に合致したら素材シート３の該当セルを元シートの該当セルにコピー
next j
next i
なんのことはない、外側のループを３分割しただけ。
ところが、これでパフォーマンスは5倍になりました！
ここまでを要約すると
- 3.複数シートからデータを大量にコピーする場合は、シートごとに処理を分けるとパフォーマンスが上がる場合がある
となります。
これ、ネットで検索しても裏付けが取れなかったんですよね。けれども体感上も実測上も明らかに次元の違う速度が出るんですなぁ。
さて、Excel VBAってのはプログラマーだけのものではなく、実際になんかしらの業務をやっていて、その業務を効率化するためにやむなく使っている人の物でもあると思います。
この先の説明はプログラマー向けになるので、興味がないひとは読み飛ばしてください。そもそもうまいこと説明できないかも知れませんしねぇ。
この現象、どうして起こるんでしょうねぇ。いろいろ考えてみました。もちろんExcelのバイナリを追ったわけでもなくメモリやレジスタのダンプを取ったわけでもないので全て推測です。
もしかしてさぁ。エクセルってワークシートオブジェクトを一度にメモリ上で管理できる数って少ないんじゃないかなぁ。遅かった例で言うと、４つのワークシートを一度にオープンしていれば速いはずなんですよね。メモリ上で既に展開済みのオブジェクトを舐めていくんでねぇ。
ところがメインループを３分割した方が速いって事は、遅い方の例では子供のループの度にワークシートの情報をメモリに展開しているのかなぁと。つまりは、メインループで１行目を処理した後、２行目の処理をするときにはもう一度コピー元を読み直しているのかなぁと。そうとしか思えないくらい速度差があります。
なんだかとってもわかりづらいですが、この記事は例によって未来の自分に宛てたものです。自分さえわかればそれでよい(笑)。
ちなみに私、Excel VBAはそれほど詳しくありません。経験はある、というレベルです。なので、とっても恥ずかしいことを書いているのかも知れませんね。
この手の記事を書くときにはいつも不安になります。でもさぁ。ネットでひっかからなかったんだよねぇ。探し方が悪いだけかも知れないけど。
