前回検証した揮発性関数の再計算時間が長くなる件ですが、もう少しVBAのマクロ処理時間への影響を回避できないか検証してみました。再表示で時間が取られるなら、VBA高速化の検索で１番最初に出てくる”表示処理をしない設定”が効くのではと思ったワケです。

まとめ
  • 揮発性関数 とは、セル入力があると、必ず表示更新の為に再計算する関数のこと。
  • 主な揮発性関数は、NOW、TODAY、RAND、RANDBETWEEN、OFFSET、INDIRECT引数の設定によって揮発性関数となるものは除く！
  • 多用しすぎると、再計算時間が長くなり、表示更新が遅れる。
  • しかし、マクロ処理は表示描写・再計算を停止させるVBAの設定を行えば、遅れはなくせる。

以下、検証結果についてもう少し詳しく書いてみます。

マクロ実行時の環境設定について

マクロはExcelなどのアプリケーションを自動制御する機能であり、特定の操作を記録して再生することで同じ操作を自動的に行えます。マクロの登録方法は"マクロの記録"ボタンを押して手動で特定の操作を行って記録する方法と、VBAで記述する方法があります。

VBA は Visual Basic for Applications の略称で、マイクロソフトが開発した Visual Basic をベースにした、マクロ機能の手順を記述するためのプログラミング言語です。手動でマクロを登録するよりも複雑な操作や条件分岐、ループ処理などをプログラミングできます。

マクロ機能の注意点

便利なマクロ機能ですが、普通のプログラミング言語と異なり、通常の設定では１つ１つの処理を表示しながら進めていきます。会社で誰かの作ったマクロで見たことはないでしょうか？マクロを実行するとExcel上で目まぐるしく表示が変わりながら、値が入力されていく画面を…。

パッと見は、自動処理でスゴイ！と思うのですが、よくよく考えると１つ１つの処理を表示するのって無駄ですよね。１回の表示処理時間はわずかでも、自動処理で多量に繰り返すと馬鹿にならない処理が必要となり、時間の掛かる重たいマクロになってしまいます。

マクロ実行時の環境設定

通常、プログラミング言語の処理はバックグラウンドで行われ、print/log関数などで指定された部分のみが表示されます。ところがVBAはアプリ用なので丁寧に画面表示しながら進めてくれます。そこでVBAも最初と最後に下記設定を加えることでバックグラウンド処理が可能となります。

VBA

' マクロ実行時の環境設定サンプル
Sub Function00_Sample()

    'マクロ開始時の環境設定
    Application.ScreenUpdating = False            '処理負荷軽減の為、処理中の画面描画を停止
    Application.Calculation = xlCalculationManual '処理負荷軽減の為、自動計算を手動に(注意！処理後も設定保持)
    Application.DisplayAlerts = False             '確認メッセージを抑止

    'ここへマクロの内容を記述する


    'マクロ終了時の環境設定戻し
    Application.ScreenUpdating = True                '画面描画を再開
    Application.Calculation = xlCalculationAutomatic '自動計算に戻し(変更があるたびに再計算が実行される)
    Application.DisplayAlerts = True                 '確認メッセージの表示を再開

End Sub

マクロの環境設定で動作遅れを防げるか検証

では、実際にマクロの環境設定で揮発性関数の影響がどのくらい防げるか、検証して見ます。

検証に使ったPCは

再計算時間を計測するPCのスペック
  • 2018年発売のWindows11 ノートPC
  • CPU : Corei5-8265U 1.6GHz 4Core、CPUベンチマークスコア(PassMark) : 5859
  • メモリ : 8GB、ストレージ : SSD 256GB

2018年ごろに買った少し古いPCなので、スペック的にはやや劣り。

検証はVBAでセル入力100繰り返しを計測

評価は前回使ったマクロの始めと終わりにVBAでマクロの環境設定を追記したもので行います。処理内容は変わらず、F列に別シートのデータを取得する揮発性関数（INDIRECT関数、OFFSET関数）・または非揮発性関数のINDEX関数が、それぞれ100個、1,000個、10,000個並んだファイルを作り、その部分には触らずG列に1から100までをVBAのマクロで自動入力させ、処理時間も計測させます。

検証で使ったシートのイメージ

VBAのコードはこちら・・・

VBA

' セル入力100回実行と計測のマクロ
Sub Function02_MeasureInputTime()

    'マクロ開始時の環境設定
    Application.ScreenUpdating = False            '処理負荷軽減の為、処理中の画面描画を停止
    Application.Calculation = xlCalculationManual '処理負荷軽減の為、自動計算を手動に(注意！処理後も設定保持)
    Application.DisplayAlerts = False             '確認メッセージを抑止

    '使用する変数を定義
    Dim startTime As Double
    Dim i As Long
    Dim tmp As Variant
    
    'セル入力100回実行の処理コード
    startTime = Timer               'タイマー関数で開始時間を取得
    tmp = 0                         'セル入力値を0へ初期化

    For i = 1 To 100                '繰り返し処理を100回実施
        tmp = tmp + 1               '現在の値へ1を足す
        Cells(1 + i, 7).Value = tmp '指定セルへ現在の値を入力
    Next                            'Forへ戻る
    
    '終了時刻から開始時刻を引いた処理時間を動作確認ウィンドウへ表示
    Debug.Print "InputTime : " & Timer - startTime

    'マクロ終了時の環境設定戻し
    Application.ScreenUpdating = True                '画面描画を再開
    Application.Calculation = xlCalculationAutomatic '自動計算に戻し(変更があるたびに再計算が実行される)
    Application.DisplayAlerts = True                 '確認メッセージの表示を再開

End Sub

時間計測結果

では、計測結果です。

◆VBAで、表示・再計算を止めて、セル入力を100回繰り返したときの処理時間

Book内の関数の数 100個 1,000個 10,000個
INDIRECT関数 0.008秒 0.008秒 0.008秒
OFFSET関数 0.008秒 0.008秒 0.008秒
INDEX関数 0.008秒 0.008秒 0.008秒

比較として、前回のブログで計測した表示・再計算ありの値も載せてみます。

◆VBAでセル入力を100回繰り返したときの処理時間（表示・再計算あり！）

Book内の関数の数 100個 1,000個 10,000個
INDIRECT関数 1.2秒 10.2秒 72.1秒
OFFSET関数 1.0秒 8.7秒 9.3秒
INDEX関数 0.02秒 0.03秒 0.03秒

おおぉっ！ぜんぜん時間が違いますね。揮発性関数が増えても全てほぼ一瞬で処理は終わっています。これで揮発性関数が入ったExcelファイルでも安心してマクロを組めますね。

また、INDEX関数のほうも、わずかですが、早くなっています。マクロを組むときには、このバックグランドで実行させるVBAのコードを入れておくのがオススメです。今日はここまで。