【VBA】大量データの処理を高速化する方法
VBAでマクロを作っても、データ量が１万行以上となると処理時間がかなり長くなることがあります。
あまりに時間がかかるとExcelがフリーズしたのか分かりづらく、使い辛いマクロになってしまいます。
そこで、今回は大量データを取り扱うときに使いたい高速化テクニックを紹介します。
から大量データの処理を高速化する方法(配列に取り込む)
大量データの処理に適した方法は、何となっても配列の使用です。
配列を使えば、まず間違いなく速くなります。
例えば、このようなデータがあって、マクロでD列に税込み価格をアウトプットしたいとします。
配列を使わず普通に書くと、このようなコードになります。
Sub 普通のコード()
Dim end_row As Long
Dim row_i As Long
end_row = Cells(Rows.Count, 1).End(xlUp).Row
For row_i = 2 To end_row
' シートに何回もアクセスする
Cells(row_i, 4).Value = Cells(row_i, 2).Value * Cells(row_i, 3).Value
Next
End Sub大量のデータでこのマクロを実行すると結構な時間がかかります。試しに、１万行のデータで私のPCで実行すると40.6秒かかりました。
このマクロでは、For文でループしていて、各行でCellsを使ってシート上のデータにアクセスしています。
時間がかかるのは１行ごとに毎回アクセスことが原因です。
配列を使えば、このアクセス回数をなんとたった２回に減らすことができます。
Sub 配列で高速化()
Dim end_row As Long
Dim i As Long
Dim arrBC As Variant
Dim arrD As Variant
end_row = Cells(Rows.Count, 1).End(xlUp).Row
' B列とC列のデータを配列として取得（アクセス１回目）
arrBC = Range("B2").Resize(end_row - 1, 2).Value
' アウトプット用配列
ReDim arrD(1 To UBound(arrBC, 1), 1 To 1)
' 配列内計算
For i = 1 To UBound(arrBC, 1)
' 配列なのでメモリ内で計算しているだけ
arrD(i, 1) = arrBC(i, 1) * arrBC(i, 2)
Next i
' D列へアウトプット（アクセス２回目）
Range("D2").Resize(end_row - 1).Value = arrD
End Sub先ほどのコードを配列を使って高速化すると、このようになります。
このコードならば、先ほどの40.6秒かかったのがたったの0.03秒で終わります。
' B列とC列のデータを配列として取得（アクセス１回目）
arrBC = Range("B2").Resize(end_row - 1, 2).Value
配列を使う場合、まず全データを一気に配列に取り込みます。
複数範囲のRangeをValueプロパティで取り込むと、実は２次元配列として変数に取り込まれます。
一度配列に取り込んでしまえばこっちのもので、配列はメモリ上にあるので数万回アクセスしても全然時間がかかりません。
' 配列内計算
For i = 1 To UBound(arrBC, 1)
' 配列なのでメモリ内で計算しているだけ
arrD(i, 1) = arrBC(i, 1) * arrBC(i, 2)
Next i
このForループは先ほどの普通の例とほとんど変わりません。取り込んだ配列arrBCの内容を掛け算して、アウトプット用の配列arrDに代入しているだけです。
' D列へアウトプット（アクセス２回目）
Range("D2").Resize(end_row - 1).Value = arrDForループで計算が完了したら、後はarrDをD列に代入するだけです。
「インプット⇒計算⇒アウトプット」の流れでコードを作ると覚えてみてください。
コードが長くなるのが玉にきずですが、処理時間を大幅に短縮することができるので、処理時間が長くて困ったら試してみてはどうでしょうか。
今回は大量データの処理に特化したテクニックを紹介しましたが、高速化するテクニックは他にも存在します。私の知っている方法は以下のページにまとめたので興味のある方はご覧ください。
https://yaromai.jp/vba-highspeed/《VBA上級者になりたい人へ》
VBA上級者を目指したい人にはパーフェクトExcel VBA一択です。
この本を読み切れば間違いなくVBA上級者になれます。
