Kの備忘録（仮）

Python、VBA、Excelを中心に記事を投稿

Excel VBAでマクロが遅くなる3つの原因と解決方法

VBA マクロが遅くなる原因 配列

はじめに

Excel VBAのマクロを実行したときに、「処理に時間がかかる」「処理の途中でフリーズしてしまった」ということはありませんか？

処理に時間がかかる原因は、マクロの処理が多いからでしょうか。確かにそれも一理ありますが、実はもっと具体的な原因があります。

主な原因は以下の3つです。

  1. ワークシート関数の自動計算
  2. 画面表示の更新
  3. セルの選択回数

なぜこの3つが原因で処理が遅くなるのでしょうか。ここの記事では理由と、解決方法について説明します。

◆目次◆

原因1：ワークシート関数の自動計算

Excelは「表計算ソフト」です。よって、Excelファイルには、ワークシート関数（SUM関数やIF関数など）が記載されていることが多々あります。
実は、ワークシート関数は、Excel VBAのマクロを実行すると再計算されてしまう仕組みになっています。その再計算によってマクロの処理が遅くなります。

  • 解決方法
    ワークシート関数を一時的に「手動計算」に設定して自動計算を止める

プロシージャの最初と最後に以下のコードを加えてください。
たった2行のコードですが、マクロ実行時にワークシート関数の再計算がなくなり、問題は解決します。

Sub test1()
' ワークシート関数を手動計算に設定
    Application.Calculation = xlCalculationManua

' ・・・処理・・・

' ワークシート関数を自動計算に設定
    Application.Calculation = xlCalculationAutomatic
End Sub

原因2：画面表示の更新

マクロを実行すると、マクロの処理過程が画面上にリアルタイムで表示されます。この時の画面表示の更新で処理が遅くなります。

  • 解決方法
    画面表示の更新を一時的に停止する

プロシージャの最初と最後に以下のコードを加えてください。 こちらも2行のコードですが、マクロ実行時に画面表示の更新がなくなり、問題が解決します。

Sub test2()
' 画面表示の更新を無効に設定
Application.ScreenUpdating = False

' ・・・処理・・・

' 画面表示の更新を有効に設定
Application.ScreenUpdating = True
End Sub

原因3：セルの選択回数

Excel VBAでは基本的にブック・シート・セルの値やプロパティを使って操作を行います。そのときに、セルの選択回数が多いと処理に時間がかかります。

  • 解決方法 セルの選択回数を減らす

少し長くなりますが、どうすればセルの選択回数を減らすことができるのかを説明します。

たとえば以下のコードをてください。 これは「シート1のセルA1からセルJ10の中身を、シート2へ転記する」というコードです。

Sub test3()

    Dim i As Long
    Dim j As Long
   
    For i = 1 To 10
        For j = 1 To 10
            Sheet1.Cells(i, j) = Sheet2.Cells(i, j)
        Next j
    Next i

End Sub

このコードでは、Sheet1の「セルA10からセルJ10」までの範囲を、ループを使ってSheet2へ転記します。セルの選択は「シート1のセルの参照」と「シート2への転記」のときに行われるので、合計200回セルを選択します。

200回くらいであれば処理速度に大きな影響はありません。しかし、10列×10000行といった表で同じ操作を行うとどうでしょうか。200000回もセルの参照を行うので、処理速度に影響が出てきます。

セルの選択回数を減らしたい場合はどうしたらいいのでしょうか。

たとえばこんなコードではどうでしょうか。

Sub test4()

    Sheet1.Range("A1:J10000").Copy Sheet2.Range("A1")

End Sub

このコードであればセルの選択回数は2回で済みます。よって、処理の時間も短いです。

しかし、実際にExcel VBAを使う現場では、もっといろんな処理を自動化したいです。 そして、「セルの値を使って計算をしたい」「条件に一致した情報だけを抽出したい」などの処理をするときは、セルの値を参照して処理をしなければいけません。

そこで、配列の出番です！

配列を使うことによって、セルの選択回数を減らすことが可能です。 「セルの値を使って計算をしたい」「条件に一致した情報だけを抽出したい」といった処理もできます。

しかし、配列を使うには1つ大きな問題があります。それは、「Excel VBAの配列は制約が多い」ということです。正直、配列の作法を覚えるのが大変です。

配列を覚える労力は報われるのか・・・。それは、次回説明します。