株式会社SALTO

第一回 現場で困ったVBAの高速化

更新日:2016.11.06
Pocket

できればもっと業務効率したいよな

そんな思いをもっていらっしゃる方のため、
記念すべき技術の第一回ということで
技術としては身近なVBAについて記事を書いてみようと思います。

皆さんはVBAってご存知ですか?

VisualBasic for Applicationのことで
Office製品ならかならず実行環境が用意されていて
「マクロ」と呼称されていたりもします。

操作を記録することで
自動的に操作をなぞるプログラムが組み立てられて
記録されたマクロを実行すれば
同じ操作を自動でやってくれます。

比較的ユーザーに近いところ
かつ上記のような便利機能もあることから
エンドユーザーなどでも利用が多い機能です。

自分はこの半年の間
某企業のVBAで作られたプログラムの
改修と検証を行っていました。

そこで起きた、VBAの問題点と
その解決策のエッセンスをお話ししたいと思います。

事の発端はシステム負荷テスト実施時に
Excelからデータベースにデータを登録する
AccessVBAで作成されたアプリケーションを動作させたところ

15万件のデータ登録に2時間もかかってしまったことでした。

このVBAのプログラムが動作している間は
他の負荷試験を実施することができず
多くの人に待ち時間を作ってしまうなんとも
厄介なアプリケーションです。

結論から先に述べると

15万件の登録に2時間かかっていたアプリケーションを
同じ件数で6分まで短縮することができました。

プログラム上のボトルネックを検証した結果
以下の2か所に大きく時間がかかっていることが分かり
そこを重点的に改修を行いました。

①データのインサートが遅すぎる。
 登録しなければならないデータの件数は上述の通り15万件です。
 つまり、15万回インサート文をデータベースに対して
 アクセスしていることになり、一回にかかる時間が若干遅いことが分かりました。
 そこで、まとめてインサートする機能を利用するようにSQLを書き換えました。
 今まで15万回だったのが、100件ずつ処理するように変更して
 1500回、これでデータベースへのアクセスを減らして
 処理速度が若干向上しました。

②冗長なループとセル読み込み
 こちらの方が改善の効果が大きかったです。
 エクセルのワークシートを行×列分ループするのではなく
 ワークシートの内容をまとめて配列にセットして
 行のインデックスは固定、列インデックスのみ動的にすることで
 余計なループとセルの読み取りを削減し、大幅な処理速度の改善が達成できました。
 プログラムの可読性の観点からすると、確かに行×列でループしている方が
 誰が読んでも分かりやすいため推奨されがちですが
 高速に処理を行う場合にはワークシート全体をメモリの
 決められた場所に展開して、固定のインデックスで読み出す方がはるかに高速です。
 この方式に関しては、ネット上でいろんな方が速度の違いを検証していますので
 一度参照されるとよいと思います。

この改修を行うことで、処理速度を約20倍まで向上させて
めでたしめでたし・・・・。

とは、いきません。

②の改修を行った際に、単純な配列ではうまくいかないことが分かったのです。
上述の件数であれば、問題なく動作しますが、今のOffice2010は、ワークシートの
最大行数が104万行以上あります。
この104万行を一気に配列にセットしようとした場合、メモリエラーが発生します。
古いバージョンのOfficeであれば、6万5千行程度なので配列に難なく入ります。
しかし、その16倍の件数に増えてしまうと、VBAで扱える限界を超えてしまうのです。

そこで、いろいろと調査した結果
業務として扱うデータ量のボーダーラインを10万件と定義し
配列には10万件単位でセットすることとしました。
ループの回数は増えてしまいますが、これでメモリエラーを回避することができます。

現在は高速化したこのアプリケーションを使用してデータを登録することで
試験全体の空き時間を減らすことができました。

VBAは一見すると間口が広く
予備知識が少ない状態でもプログラミングを行えるので
「VBAなんて簡単」と思う方もいるかもしれません。
しかし、いざ自分が作ったものが重くて使い物にならないとき
どうやってボトルネックを解消するのかには、ちょっとした勘所が必要です。

プログラマーにとって、自分が作ったプログラムが意図したとおりに動作することは
仕事をする上でこの上ない喜びです。
だからこそ、

「もっと速く、効率よくならないか?」


常に模索してもらいたいです。

この記事が、VBAの性能劣化に悩む開発者の一助になればと思います。
それでは、また。