<文言修正&誤字修正&[画面の更新を止める]に注意事項を追記 2019/11/02>
本質的な自動化について大幅加筆。小手先の簡単にできる高速化について加筆修正 2019/12/03
VBAで作成したマクロの高速化を目指す上で大切なことを解説します。
目次[:contents]
高速化を目指す上で大切なこと
高速化はユーザビリティ向上の一環
この記事に目を通して下さっている方は、処理を高速化したい、高速化の必要性を感じていると思います。ある程度の処理量があると数秒程度の作業には収まらず、数分、場合によっては数十分もの時間が掛かることがあります。
マクロの実行中は通常のエクセル作業ができないため、数分以上のマクロは気分的な問題だけで無く、その他の作業に影響を与えてしまいます。つまり、ユーザービリティが低いと言えます。
知ってさえいればできる簡単な小手先の高速化と本質的な高速化
知ってさえいればできる自動化はさっさとやってしまいましょう。一方、高速化の本質は余計な作業をさせないことです。つまり目的を達成するのに必要な部分だけを行うようにすることです。プログラミング技術そのものと言うよりも、自動化する作業の目的を熟知することの方が大事です。まずは、高速化の前提である時間で計る方法をさっと確認しておきましょう。
「処理時間を計る」は小手先の高速化と本質的な高速化の両方に効く
まず、小手先の高速化と本質的な高速化の両方に必要なアプローチから解説します。高速化が目的なので、時間を計るようにします。どの部分に時間が掛かっているのかを把握する目的があります。問題点を見つけることが修正への第一歩です。
エクセルVBA 上級者から初段へのステップアップ② 標準モジュールの役割を理解して処理の流れにこだわるで解説したように、作業の塊ごとのPrivate Subのプロシジャを順に呼び出していく、メインのプロシジャをイメージしたサンプルプログラムで説明します。
Sub time_watch() Debug.Print Time Call procedure1 Debug.Print Time Call procedure2 Debug.Print Time Call procedure3 Debug.Print Time Call procedure4 Debug.Print Time End Sub
Debug.Print Timeはイミディエイトウィンドウにその時の時間を表示します。このように作業の塊ごとにDebug.Print Timeを記述する事により、どのプロシジャで時間が掛かっているのかを把握することができます。一番時間が掛かっている場所が分かれば、今度はそのプロシジャ内で同様に複数のDebug.Print Timeを記述して、どの部分に時間が掛かっているかを探っていきます。これで高速化のための、時間が掛かっている場所を特定する準備ができました。
小手先の高速化
画面の更新を止める「Application.ScreenUpdating = False」
知ってさえいればできる簡単な自動化の代表格です。本来は高速化の為の命令では無く、画面の再描画を止めることによって画面のちらつきを押さえ、ユーザーが不快に思わないようにするための記述です。
用意したサンプルプログラムはこちらです。
Sub stop_screenupdating1() Dim i As Long, j As Long Debug.Print Time For i = 1 To 100 For j = 1 To 100 Cells(i, j) = i + j Next j Next i Debug.Print Time End Sub
For~Nextを入れ子にして100 x 100のかけ算表を作成しました。私の実行環境では6秒掛かりました。次に、画面の更新を止めます。
Sub stop_screenupdating2() Dim i As Long, j As Long Debug.Print Time Application.ScreenUpdating = False For i = 1 To 100 For j = 1 To 100 Cells(i, j) = i * j Next j Next i Application.ScreenUpdating = True Debug.Print Time End Sub
私の実行環境では5秒でした。1秒しか変わらないように思いますが、この規模感の転記が複数あったり、シートを切り替えたりと言ったような作業が増えていけば、ScreenUpdatingをFalseにする事のメリットが大きくなっていくでしょう。(このサンプルプログラムはScreenUpdatingがTrueの状態でやった方が面白いですけどね)
あと、作業の終了時にはSceenUpdatingをTrueに戻しておきます。エクセルが閉じられるまで、Falseの状態が続いてしまうためです。その後、エクセル開いたまま作業が続くことを考慮して、必ずTrueに戻します。
静的2次元配列を使う
エクセルのシートはたくさんプロパティやメソッドを持っているため、セルに値を書き込むこと自体が実は大変重たい作業です。なので、計算自体は配列の中でやってしまい、最後にシートにペタッと貼り付ける方法が高速化に適しています。上級の人であれば配列は既になじみがあると思いますが、高速化に役立つことも意識しておきましょう。
配列を使ってセルへの書き込みの速度を上げる方法については、VBAで作ったマクロの高速化① 配列を使うをご参照下さい。(Ctrlを押しながらクリックすると別タブで開くことができます)
高速化の本質は処理の流れを効率化し、無駄な作業をさせないこと
ここからは具体的なサンプルプログラムが提示できませんので考え方だけ述べていきます。先日、自動化ツールを作成している人のコードを見る機会がありました。その人は手作業で行うのと同じ内容をそのままコードに落とし込んでいました。具体的には下記のような手順です。
無駄の多い手順の例
- 注文の伝票番号と商品番号と数量をアンダーバーで区切った文字列を作ります(イメージ的にはa1001_abcde_5のような感じです。)
- 別のブックでも同じ文字列を作ります。
- VLOOKUPで当てて何らかの値をセルに書き込んでいきます
- VLOOKUPで引っ張ってきた値に対して条件を満たせばその列の右に◎をつけていきます
- ◎でフィルターを書けて別シートに転記します
要するに、注文書_商品番号_数量で一意な検索用の値を作って別のデータから検索して値を持ってくる。その値が条件に合っていればそれを抽出すると言う流れです。手作業によるエクセル操作ならありがちな手順だと思います。しかし、うえの1~5の作業すべてにムダが含まれています。
- VLOOKUPを当てるために作った検索用の値をセルに書き込むのがムダ
- 一旦VLOOKUPでセルに書き込むのがムダ
- 条件を満たす行のセルに◎を書き込むのがムダ
- オートフィルタを書けるのがムダ
ムダの無い手順に修正
- For~Next文で検索する
- 検索用の値が一致する場合のみ、条件を満たすかどうかを判定
- 条件を満たす場合のみ動的2次元配列の末尾に必要なデータを格納
- 必要に応じて2次元配列を縦横変換してシートに貼り付け
このように書くと、ムダの無い手順の方が面倒なステップのように感じますが、実際はセルに一度も書き込んでいませんので、かなりの高速化が図れます。条件を満たすかどうかの判定も検索用の値が一致する場合のみしかしませんし、配列への格納も、条件を満たす場合しか行いません。条件分岐を使って余計な作業をしないようにしてます。
このようにやらなくていい作業を見極めてやらせないように処理の流れを作っていくことが高速化の本質です。
【高速化の本質】もっと作業量を削ることができる
実はもっとエクセルさん(本当はエクセルさんではなくパソコンさん)が考える量を減らすことができます。If文やSelect Case文を使って、検索しなくて良い場合分けを細かく行う事です。プログラムのコード字体は増えますが高速になります。
今まで、手作業でやっていたことをコードに落とし込む今年かやっていないと、プログラムのコードが長くなれば実行時間も長くなるイメージがあるかも知れません。
逆です。やらなくていい場合を丁寧に記述する事が高速化につながります。
・・・
ということは、本気で高速化を目指すなら実際には実行されないムダかも知れない条件分岐をひたすら書いていくのが高速化につながります!ここまでくれば、あとはプログラミングの工数と使ってもらう頻度と削減時間の兼ね合いになってきます。
プログラムの高速化の本質は、「余計なことをしなくていいよ」の「余計なこと」をどれだけ検出するプログラムを書けるかです。あとは条件分岐の手間と削減できる時間との兼ね合いです。
それでは、皆さんもVBAプログラムの高速化を図って、コードの改善を図りましょう。
<関連記事>
◆高速化
◆上級から初段へのステップアップシリーズ
- VBA 上級者から初段へ① 上級を卒業する手順
- VBA 上級者から初段へ② 標準モジュールの役割と理想的な処理の流れ
- VBA 上級者から初段へ③ 変数名などの簡単な命名規則
- VBA 上級者から初段へ⑤ 初段以上は経験と実力が比例しない事と、その解決策
<ゆんの電子書籍>
ゆんの電子書籍はすべてkindle unlimitedで読み放題です!