【マクロVBA知識高速化②】あまり知られていない？大量データで処理が遅いマクロVBAを配列で高速化

今回はVBAの高速化について説明します。
皆さんは作成したマクロVBAの処理速度が遅くなったことはないでしょうか。

今回はその悩みを解決する第2弾となっています。

前回の内容を合わせると効果はさらに見込めます。

前回の記事はこちら☟

【VBA知識高速化①】画面更新・自動計算・イベントのOFFについて（VBA高速3種の神器） – 独学エクセル塾 (dokugakuexcel.com)

それでは見ていきましょう。

■高速化の内容
■比較用プログラムの説明
■変数内で記録する
■変更前と比較
■高速化①と今回の対策の混合結果
■まとめ

■高速化の内容

今回の解決策はVariant変数を使用し、変数内で集計します。
そして集計結果を最後にまとめて表示する仕組みです。

処理が遅くなる要因としてセルの値を毎回読み取っている場合があります。
そこで変数内で計算させることで
読み込みがなくなり、早くなるというわけです。

人で表すと、いちいち紙に書いて計算している作業を頭で考えてまとめて記入するイメージです。

では実際に検証してみましょう。

■比較用プログラムの説明

今回も高速化①で使用したシートを用いていきます。
高速化①をご覧になられた方は次の目次へどうぞ。

では検証用のプログラムについて説明します。
A・B行に100000行、ランダムなデータがある状態です。

データの処理方法としてA1とB1の数値をかけてC1に出力する仕組みとします。
その処理を100000行繰り返すのにかかった時間を基準としましょう。

用意したプログラムがこちら。

一応、コピーできるように貼っておきます。

Sub 高速化①()
Dim Time1 As Double, Time2 As Double, Result As Double
Dim A As Long
Time1 = Timer
””計算開始
For A = 1 To 100000 Step 1
Cells(A, 3) = Cells(A, 1) * Cells(A, 2)
Next A
””計算終わり
Time2 = Timer
Result = Time2 – Time1
MsgBox Result & “秒で処理しました”
End Sub

では実行してみましょう。

3.62秒かかりましたね。これを元に早くしていきます。

■変数内で記録する

ではA行とB行で計算した値をVariant関数内で記録してみましょう。

一度、Variant関数内に使用したい範囲の空白セルを入れておきます。
この作業をしないとエラーになります。
今回はA行*B行の結果の１列だけでいいので、１列分100000行をVariant内に空白として入れます。

Dim N As Vriant
N = Range(Cells(1, 3), Cells(100000, 3))

今回は３列目を使用して空白を入れてますが、計算結果値を上書きするので何列目でも構いません。

そして計算結果値をVariant内に入れます。

N(A, 1) = Cells(A, 1) * Cells(A, 2)

これを100000行繰り返し、最後に出力する流れです。

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

こちらをプログラムに入れていきます。

■変更前と比較

変更後のプログラムはこちら。

このようになりました。
またまたコピー用載せておきます。

Sub 高速化②()
Dim Time1 As Double, Time2 As Double, Result As Double
Dim A As Long, N As Variant
Time1 = Timer
””計算開始
N = Range(Cells(1, 3), Cells(100000, 3))
For A = 1 To 100000 Step 1
N(A, 1) = Cells(A, 1) * Cells(A, 2)
Next A
Range(Cells(1, 3), Cells(100000, 3)) = N
””計算終わり
Time2 = Timer
Result = Time2 – Time1
MsgBox Result & “秒で処理しました”
End Sub

それでは実行してみましょう。

結果が出ました！

3.62秒から0.78秒と2.84秒短縮しました。
結果的に78%短縮短縮です。
驚異的ですよね…

では高速化①の対策も合わせてみましょう。

■高速化①と今回の対策の混合結果

高速化①で行った、画面更新・自動計算・イベントをOFFも追加してみましょう。

追加したプログラムがこちら。

Sub 高速化①②()
Application.ScreenUpdating = False ”画面更新をOFF
Application.Calculation = xlManual ”自動計算をOF
Application.EnableEvents = False ”イベントをOFF
Dim Time1 As Double, Time2 As Double, Result As Double
Dim A As Long, N As Variant
Time1 = Timer
””計算開始
　　　N = Range(Cells(1, 3), Cells(100000, 3))
For A = 1 To 100000 Step 1
　　　N(A, 1) = Cells(A, 1) * Cells(A, 2)
Next A
　　　Range(Cells(1, 3), Cells(100000, 3)) = N
””計算終わり
Time2 = Timer
Result = Time2 – Time1
MsgBox Result & “秒で処理しました”
Application.ScreenUpdating = True ”画面更新をON
Application.Calculation = xlAutomatic ”自動計算をON
Application.EnableEvents = True ”イベントをON
End Sub