更新日：2022/06/06、作成日：2013/05/05

VBA の処理を高速化する

はじめに

Excel VBA マクロの処理を高速化する方法を紹介します。

次の方法で VBA マクロの処理を高速化できます。

セルを配列化する：セルの範囲を二次元配列に変換します。
描画を停止する：Application.ScreenUpdating = False のようにして、描画を止めます。
手動計算する：Application.Calculation = xlCalculationManual のようにして、自動計算を止めます。
その他：コードを減らしたり Variant 型を使わないようにします。

「Timer 関数」から、処理時間を計測できます。

「DoEvents 関数」から、応答なしを回避したり処理をキャンセルできます。

Beep から、音を鳴らして処理が終了したことを知らせられます。

セルを配列化する

セルの値を取得する、セルに値を入力するのは遅い処理です。

高速化するには、セルの範囲をまとめて配列に入れます。その配列の中で処理をしてから、最後に配列をまとめてセルの範囲に入力します。

これでセルの値を取得または入力する回数を 1 回にできます。特にセルに値を入力する処理が遅いので、出来るだけその回数を減らします。

Dim 二次元配列 As Variant
二次元配列 = Range("A1:C2").Value ' A1 ～ C2 の Value が二次元配列になる

' 二次元配列に対して処理をする
二次元配列(1, 1) = "A1" ' インデックスは 1 から始まる
二次元配列(1, 2) = "B1"
二次元配列(1, 3) = "C1"
二次元配列(2, 1) = "A2" 
二次元配列(2, 2) = "B2"
二次元配列(2, 3) = "C2"

Range("A1:C2").Value = 二次元配列 ' 配列をセルの範囲に入力

Range("A1:C2") だと二次元配列(1 To 2, 1 To 3) の配列になります。

読み込み時と書き込み時のセルの範囲は、違う場所でも設定できます。セルの範囲と配列の要素数が違ってもその分だけ設定されます。

二次元配列について詳しくは「二次元配列」をご覧ください。

書式設定

フォントや背景色などの書式は配列化できませんが、セルの範囲に対してまとめて設定できます。

Union を使用すると、指定した 2 つのセルを組み合わせた範囲を取得できます。

同じ書式を設定するなら、Union を使用してすべてのセルの範囲をまとめてから設定します。

' 通常のセルの範囲
With Range("A1:C2")
    .Font.Color = RGB(255, 0, 0)   ' 文字色
    .Font.Name = "ＭＳ Ｐゴシック" ' 名前
    .Font.Size = 11                ' サイズ
    .Font.Bold = True              ' 太字
End With

' 複数のセルの範囲をまとめた範囲
Dim r As Range
Set r = Range("A1:A2")
Set r = Union(r, Range("B1:B2"))
Set r = Union(r, Range("C1:C2"))
With r ' A1 ～ C2
    .Font.Color = RGB(255, 0, 0)   ' 文字色
    .Font.Name = "ＭＳ Ｐゴシック" ' 名前
    .Font.Size = 11                ' サイズ
    .Font.Bold = True              ' 太字
End With

セルの範囲の取得について詳しくは「セルを範囲指定して取得する」をご覧ください。

描画を停止する

画面を描画するのは遅い処理です。セルに値を入力したり書式を変更すると描画されるので、その処理は遅い処理になります。

高速化するには、処理を開始したら描画を停止します。

上記の「セルを配列化する」方法を使用するとセルに値を入力する回数が減るので、描画を停止しても効果は少しです。しかし、書式を設定する回数を減らせないときは、描画を停止すると大幅に高速化されます。

スクロールしないと見えないセルついては、何もしなくても描画されないので高速です。

Excel の画面を最小化していると、何もしなくても描画されないので高速です。

描画を停止するには Application.ScreenUpdating = False を入力します。

Application.ScreenUpdating = False ' 描画を停止する

' 時間のかかる処理
Dim i As Long
For i = 1 To 10000
    Range("A1").Value = i
Next

Application.ScreenUpdating = True  ' 描画を再開する

処理の最後に Application.ScreenUpdating = True を入力してしなくても、マクロの処理が終了すると描画が再開されます。

処理中に一時的に描画したいときは次のようにします。

Application.ScreenUpdating = False

' 時間のかかる処理
Dim i As Long
For i = 1 To 10000
    Range("A1").Value = i
    
    ' 100 回毎に描画
    If i Mod 100 = 0 Then
        Application.ScreenUpdating = True
        ' Application.ScreenUpdating を True にしている間に編集されたセルだけ描画される
        Range("A1").Value = Range("A1").Value ' セル A1 を編集すると、セル A1 だけ描画される
        DoEvents ' すべてのセルを描画
        Application.ScreenUpdating = False
    End If
Next

Application.ScreenUpdating = True

処理中に Application.ScreenUpdating = True に設定し、その後に編集されたセルだけが描画されます。

すべてのセルを描画するには「DoEvents 関数」を使用します。ただし、これには問題もあります。詳しくは下記の「応答なしを回避する、処理をキャンセルする、非同期処理をする」をご覧ください。

手動計算する

エクセルの計算方法は通常だと「自動」になっています。これはセルの値を変更したときに、数式を使用している他のすべてのセルが再計算されます。そのためセルの値を変更する回数が多ければ多いほど遅くなります。

上記の「セルを配列化する」方法を使用するとセルに値を入力する回数が減るので、再計算する回数も減り効果は少しです。しかし、すべてのセルを配列化できないときは、それだけ高速化されます。

手動計算するには Application.Calculation = xlCalculationManual を入力します。

Application.Calculation = xlCalculationManual    ' 手動計算にする

' この処理は再計算されない

Application.Calculation = xlCalculationAutomatic ' 自動計算にする

処理の最後に Application.Calculation = xlCalculationAutomatic に設定して、自動計算を有効にします。このタイミングでセルの再計算が行われます。

手動計算にすると、セルの値を変更してもそれを参照しているセルの値が変更されないことに注意します。

その他の高速化する方法

効果は微妙かもしれませんが高速化する方法を紹介します。

コードを短くする

コードの量が多ければ多いほど処理が多くなるのでその分遅くなります。関数を呼び出すだけでも処理に時間がかかります。あえて関数にしないで直接コードを入力した方が高速化します。

Variant 型を使わない

Variant 型で処理をするより、Integer や Long など型を指定した方が高速化します。

Object 型を使わない

Variant 型を使わない理由と同じです。参照設定できるものは参照設定して、その型を使用します。

処理時間を計測する

「Timer 関数」を使用して、処理時間を計測できます。

Public Sub WatchTime
    Dim t As Double
    t = Timer

    ' 時間のかかる処理

    Debug.Print(Timer - t) ' 1.23 などの秒数
End Sub

1 ミリ秒もかからないような処理は 0 秒となり計測できません。同じ処理を 1000 回以上繰り返せば、計測できるようになります。

Public Sub WatchTime
    Dim startTime As Double
    Dim stopTime As Double
    startTime = Timer ' 処理開始

    Dim i As Long
    For i = 1 To 1000
        ' 時間のかかる処理
    Next

    stopTime = Timer ' 処理終了
    Debug.Print(stopTime - startTime) ' 1000 回分の経過時間
    ' 1000 で割ると 1 回の時間になる
End Sub

「Timer 関数」の精度は約 15 ミリ秒なので、それより短い時間は計測できません。

進捗を表示する

処理中の進捗率を表示するのにステータスバーを使用できます。

描画を停止していてもステータスバーは描画されます。ただし、背景色が変わって見づらくなります。

Application.ScreenUpdating = False ' 描画を停止する

Dim max As Long
max = 10000 ' 最大件数

' 時間のかかる処理
Dim i As Long
For i = 1 To 10000
    Range("A1").Value = i

    ' 100 回毎に進捗を更新
    If i Mod 100 = 0 Then
        Application.StatusBar = Format(i / max, "0%") ' 0%
    End If
Next

Application.ScreenUpdating = True  ' 描画を再開する
Application.StatusBar = False ' ステータスバーを元の表示に戻す