Mutable_Yunの業務改善ブログ

業務改善や生産性向上のブログです。自動化の手段として、VBAやRPAの勉強に役立つ解説をしています。

VBA 上級者から初段へ④ 高速化を目指す上で大切なこと

<文言修正&誤字修正&[画面の更新を止める]に注意事項を追記 2019/11/02>

VBAのプログラミングで上級を卒業して初段の仲間入りを目指すシリーズの4回目です。今回はマクロの実行開始から作業完了までの時間にこだわることについて解説していきます。

この記事は上級です。
レベルについてはExcel VBAの実力(レベル)を定義してみる 初心者~三段をご参照ください。

エクセルマクロがよく遅いと言われていますが、それはVBAを使いこなしていない事に原因があります。エクセルマクロが遅いと言われている原因については遅い、重いは勘違い。VBA はエクセルさんの気持ちになって高速化するをご覧下さい。

配列を使ってセルへの記入を減らす方法についてはVBAで作ったマクロの高速化① 配列を使うで具体的な方法を解説しています。

エクセルアプリケーション自体が重いので、エクセルブックでは無くテキストを開くことで高速化を図りたい方へは
VBAで作ったマクロの高速化②改 内部的に開く、閉じる ⇒ 実務作業の具体的手順(csvファイルとして用意しておき、集約するという事前作業)で方法を解説しています。

VBAによるプログラムの高速化についての具体的な手法については、これまでのようにそれぞれの手法ごとに解説記事をUPしていきます。高速化の記事はVBA高速化の記事一覧にまとめています。

目次

高速化はユーザビリティ向上の一環

この記事に目を通して下さっている方は、処理を高速化したい、高速化の必要性を感じていると思います。ある程度の処理量があると数秒程度の作業には収まらず、数分、場合によっては数十分もの時間が掛かることがあります。

マクロの実行中は通常のエクセル作業ができないため、数分以上のマクロは気分的な問題だけで無く、その他の作業に影響を与えてしまいます。つまり、ユーザービリティが低いと言えます。

知ってさえいればできる簡単な自動化と本質的な自動化

知ってさえいればできる自動化はさっさとやってしまいましょう。一方、高速化の本質は余計な作業をさせないことです。つまり目的を達成するのに必要な部分だけを行うようにすることです。プログラミング技術そのものと言うよりも、自動化する作業の目的を熟知することの方が大事です。まずは、高速化の前提である時間で計る方法をさっと確認しておきましょう。

時間を計る

高速化が目的なので、時間を計るようにします。高速化の効果を図るだけで無く、どの部分に時間が掛かっているのかを把握する目的があります。問題点を見つけることが修正への第一歩です。

エクセル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で作ったマクロの高速化① 配列を使うをご参照下さい。

高速化の本質は処理の流れを効率化し、無駄な作業をさせないこと

ここは具体的なサンプルプログラムが提示できませんので考え方だけ述べていきます。

余計なことをさせない

先日、自動化ツールを作成している人のコードを見る機会がありました。その人は手作業で行うのと同じ内容をそのままコードに落とし込んでいました。具体的には下記のような手順です。

無駄の多い手順
  1. 注文の伝票番号と商品番号と数量をアンダーバーで区切った文字列を作ります(イメージ的にはa1001_abcde_5のような感じです。)
  2. 別のブックでも同じ文字列を作ります。
  3. VLOOKUPで当てて何らかの値をセルに書き込んでいきます
  4. VLOOKUPで引っ張ってきた値に対して条件を満たせばその列の右に◎をつけていきます
  5. ◎でフィルターを書けて別シートに転記します

要するに、注文書_商品番号_数量で一意な検索用の値を作って別のデータから検索して値を持ってくる。その値が条件に合っていればそれを抽出すると言う流れです。手作業によるエクセル操作ならありがちな手順だと思います。しかし、うえの1~5の作業すべてにムダが含まれています。

  • VLOOKUPを当てるために作った検索用の値をセルに書き込むのがムダ
  • 一旦VLOOKUPでセルに書き込むのがムダ
  • 条件を満たす行のセルに◎を書き込むのがムダ
  • オートフィルタを書けるのがムダ
ムダの無い手順
  1. For~Next文で検索する
  2. 検索用の値が一致する場合のみ、条件を満たすかどうかを判定
  3. 条件を満たす場合のみ動的2次元配列の末尾に必要なデータを格納
  4. 必要に応じて2次元配列を縦横変換してシートに貼り付け

このように書くと、ムダの無い手順の方が面倒なステップのように感じますが、実際はセルに一度も書き込んでいませんので、かなりの高速化が図れます。条件を満たすかどうかの判定も検索用の値が一致する場合のみしかしませんし、配列への格納も、条件を満たす場合しか行いません。条件分岐を使って余計な作業をしないようにしているんですね。

このようにやらなくていい作業を見極めてやらせないように処理の流れを作っていくことが高速化の本質だと思います。

それでは、皆さんもVBAプログラムの高速化を図って、コードの改善を図りましょう。