Excel VBA 処理速度を高速にする方法と速度比較

今回は処理速度の改善や、高速化方法についてご説明します。

いくつか方法がありますので効果の大きさと、難易度から優先順位を付けて順番にご説明します。

紹介する方法はコーディング次第では、不要になるものもあります。

現在書けるコードに適した方法で、高速化を試してみてください。

また、Excel関数で処理の重たい「VLOOKUP関数」や「COUNTIF関数」、「SUMIF関数」などを高速化した記事は次の記事をご覧ください。

1.高速化の方法
2.描画をとめる
3.自動計算をとめる
4.選択(Select)をやめる
5.配列で処理する
6.IO(インプット/アウトプット)の回数を減らす
7.ロジックや設計で高速化する
8.処理速度計測結果とまとめ

1.高速化の方法

まずは、高速化する方法です。

難易度の低い順に記載してます。(あくまで個人的主観です。)

誤解がないように書きますが、下記の方法を使用すれば必ず高速化されるわけではありません。

「Application.ScreenUpdating = False」で描画をとめる
自動計算を「Application.Calculation = xlCalculationManua」で適宜とめる
セル、シート、ブックなどのSelectや、Selectionを極力やめる
配列に入れて処理する
セルへのIO(インプット/アウトプット)の回数を減らす
ロジックや設計で高速化する

下に行くほど難易度が高く、経験や知識が必要になるかと思います。

「配列」と「IOの回数」の順番は、一見「IOの回数」の方が簡単そうに見えましたが、そもそも配列を使う前提のケースが多いかな？と思い、この順番です。

基本的にExcelの処理を遅くする原因は、描画とIOと選択(Select)にあると思います。

リストの方法は、結局のところ共通して描画、IOと選択(Select)をいかに減らすかという事です。

2.描画をとめる

まずもっとも簡単な「Application.ScreenUpdating = False」で描画をとめるです。

ExcelはIOもそうですが、表示の切り替えが非常に遅いです。

そのため、適宜描画を止める事で処理速度がとても向上します。

表示の切り替えが多いコードに対して有効な方法です。

詳細は「描画を停止する、画面を更新しない方法」をご覧下さい。

Application.ScreenUpdating = False'描画停止
'処理
Application.ScreenUpdating = True'描画開始

まず、Sample1で描画止めずに10,000回、Sheet1とSheet2のセルに文字を入力してみます。

画面更新を止めないコード

Sub Sample1()

Dim i As Long

For i = 1 To 10000

    Worksheets("Sheet1").Select
    Cells(i, 1) = "ABCDEF" & i
    
    Worksheets("Sheet2").Select
    Cells(i, 1) = "ABCDEF" & i

Next i

End Sub

「20.625秒」かかりました。

画面更新を止めたコード

次は描画をとめたコードです。

Sub Sample2()

Dim i As Long

Application.ScreenUpdating = False

For i = 1 To 10000

    Worksheets("Sheet1").Select
    Cells(i, 1) = "ABCDEF" & i
    
    Worksheets("Sheet2").Select
    Cells(i, 1) = "ABCDEF" & i

Next i

Application.ScreenUpdating = True

End Sub

「11.093秒」で半分の処理速度になりました。

3.自動計算をとめる

VBAに限らず、Excelを使用していると大量の数式を入力されていると再計算で「応答なし」になると思います。

この再計算が多いケースのコードに対して有効です。

また再計算は、以下の処理がトリガーとなり発生します。

新しいデータを入力
メニューから再計算をする
行列の追加/削除をする
行の表示/非表示を切り替える
ファイルを保存する
オートフィルタを実行する
ワークシート名を変更する
別ワークシートとの相対位置が変更される。

これで全てではないかもしれませんが、これらの処理が実行される度に、再計算が発生します。

実際には最後の1回計算が実行されたら大丈夫、という処理は「Application.Calculation = xlCalculationManual」で適宜自動計算をとめる事で、無駄な再計算を回避出来ます。

注意として、「Application.Calculation = xlCalculationManual」はExcelの「計算方法の設定」を変えてしまうため、プロシージャを抜けても自動には戻りません。

必ず自動計算に戻しましょう。

詳細は「手動計算・自動計算の指定と再計算を停止する方法」をご覧下さい。

Application.Calculation = xlCalculationManual '自動計算停止(手動計算)
'処理
Application.Calculation = xlCalculationAutomatic '自動計算開始

次のコードは検索値A列に2,000行とB列にVlookup関数が入力されていて、参照データDE列に2,000行あるデータあります。

こちらのE列のデータを変更します。

自動計算のままのコード

Sub Sample3()

Dim i       As Long

For i = 2 To 2001

    Cells(i, 5) = Cells(i, 5) * 2

Next i

End Sub

「269.007秒」でした。

手動計算のコード

上記のコードを自動計算を停止して処理してみます。

Sub Sample4()

Dim i       As Long
Application.Calculation = xlCalculationManual

For i = 2 To 2001

    Cells(i, 5) = Cells(i, 5) * 2

Next i

Application.Calculation = xlCalculationAutomatic

End Sub

こちらは「0.515秒」でした、かなり早くなりました。

4.選択(Select)をやめる

VBA初心者にありがちなコードですが、操作したいオブジェクトを毎回選択(Select/Selection)して、処理をしようとします。

こちらは処理速度が遅くなる原因なので、極力やめましょう。

ですが、中にはExcelの機能を扱うにあたりワークシートを選択する必要がありますので、適宜使い分けが大事です。

次のコードは、Sample1のコードをSelectしないで処理したコードです。

Sample1では「20.625秒」かかり、描画を停止した場合で「11.093秒」でした。

Selectをやめたコード

Sub Sample5()

Dim i As Long

For i = 1 To 10000
    
    Worksheets("Sheet1").Cells(i, 1) = "ABCDEF" & i
    
    Worksheets("Sheet2").Cells(i, 1) = "ABCDEF" & i

Next i

End Sub

「3.359秒」でした。

描画停止もしていませんが、さらに高速になっています。

いかにSelectが遅いかというのがわかる結果ですね。

5.配列で処理する

ある程度VBAを覚えてくると、配列を使用して処理する事に行き着くかと思います。

配列の操作は慣れるとさほど難しくありませんが、慣れるまで非常に難易度が高く感じます。

配列を使用する目的の１つが「高速化」だと思います。

ですが、配列はインプットのみでは高速化の効果は大きくありません。

配列を使用した高速化は、次の項目にあるIOの回数とセットで始めて効果が最大化されます。

配列の基本的な使い方は「Array・配列の使い方(基礎編)」を、応用については「Array・配列の使い方(応用編)」をご覧下さい。

次のコードは、A列とB列に数値が入力されたデータが100,000行あります。

単純な足し算を直接セル参照して、結果を都度セルに出力します。

配列を使わないコード

Sub Sample6()

Dim i As Long

For i = 1 To 100000
    
    Cells(i, 3) = Cells(i, 1) + Cells(i, 2)

Next i

End Sub

「15.367秒」でした。

配列に格納したコード

この処理をインプットのみ配列を使用して、アウトプットは都度結果をセルに出力する処理をしてみます。

Sub Sample7()

Dim i As Long
Dim myArray As Variant

myArray = Range(Cells(1, 1), Cells(100000, 2))

For i = 1 To 100000
    
    Cells(i, 3) = myArray(i, 1) + myArray(i, 2)

Next i

End Sub

「14.5秒」でした、1秒も短縮できませんでした。

配列に代入すれば、使えば速い訳ではないのがよくわかります。

6.IO(インプット/アウトプット)の回数を減らす

上でも記載していますが、セルのインプット/アウトプットは処理速度がとても遅いです。

そのため、配列に代入して極力配列内で処理をしてから、最後に結果のみをセルに出力します。

配列に格納して出力を1回にしたコード

次のコードは、全項目で使用したSample7の出力を最後の1回のみにしてみます。

Sub Sample8()

Dim i As Long
Dim myArray As Variant

myArray = Range(Cells(1, 1), Cells(100000, 3))

For i = 1 To 100000
    
    myArray(i, 3) = myArray(i, 1) + myArray(i, 2)

Next i

Range(Cells(1, 1), Cells(100000, 3)) = myArray

End Sub

「0.75秒」でした。

やはりIO(インプット/アウトプット)の回数は処理速度に大きく影響を与えます。

7.ロジックや設計で高速化する

処理自体は7項目で高速化出来ました。

最後はロジック、設計やプロセスを工夫する事で、高速化します。

この項は「この構文」、「この方法」を使用したら高速になるという内容ではありません。

とはいえ、それではどうしたら良いかわからないので個人的によく使用する方法をいくつか挙げたいと思います。

Excelのソート機能を使用して事前に並べ替えをする
不要行、列を事前に削除してループ回数を減らす
同じループ内で処理できる処理は極力同一ループ内で処理する
数式埋め込みをせず極力算出結果(値)のみ出力する
参照関数(VLOOKUP/COUNTIFなど)をDictionary(連想配列)を使用して自作関数で処理する

他にも色々あると思いますが、工夫次第で非常に高速化出来ると思います。

また、これらの方法(Dictionary以外)は各高速化の方法全てに共通する内容かと思います。

参考までに30分かかるVLOOKUP関数を、Dictionaryを使用して高速VLOOKUPで2秒にした記事がこちらです。

「Excel VBA VLOOKUPの高速化」

そのほかCOUNTIF関数、SUMIF関数、複数条件のVLOOKUP関数等も高速化した記事があります。

ロジックはバカの一つ覚えとなっていますが、サンプルファイルもDLできますので、参考にしてみてください。

8.処理速度計測結果とまとめ

最後に今回行ったサンプルの処理速度比較をまとめました。

処理方法	改善前	改善後
描画を止める	20.625秒	11.093秒
自動計算をとめる	269.007秒	0.515秒
選択(Select)をやめる	20.625秒	3.359秒
配列で処理して都度出力する	15.367秒	14.5秒
配列で処理してIOの回数を減らす	15.367秒	0.75秒