EXCELのVBA高速化対応 | データが多く遅いと感じたら
ExcelのVBA高速化の3要素
１．一括でデータを読み取る(データ取得でForは使わない)
データ取得時に下記のように、セルの範囲のデータを取得する際に、For分で回しながら一つづつ値を取得をしている場合、Excelオブジェクトは内部で様々な処理（検証や、変換等）が行われる為、それを、セル一つづつの単位で回すと、データ量が少ない場合は対して問題にならないが、データ量が増えてくると分単位でかかる場合がある。
この表を下の枠に写す悪いコード例実行前
実行後
Sub もっさり動作コード()
Dim iRow As Long
Dim iCol As Long
For iRow = 2 To 4
For iCol = 2 To 5
Sheet1.Cells(iRow + 4, iCol).Value = Sheet1.Cells(iRow, iCol)
Next
Next
End Sub
高速化後
Sub 高速化コード()
Dim iRow As Long
Dim iCol As Long
Dim OutputLayout() As Variant
'一括でデータを取得し
OutputLayout = Sheet1.Range(Cells(2, 2), Cells(4, 4))
'一括でデータを出力
Sheet1.Range("B6:D8").Value = OutputLayout
End Sub
２．自動計算機能のOFF
EXCELシートのセル内に、沢山の数式が埋め込まれている場合、シートの複製や、値をセットした際に、毎回式が評価され、自動的に再計算されてしまいます。
データが大量になってくると、この評価にものすごく時間が掛かってしまう為、OFFにすると良いでしょう。
Sub 処理()
Dim iRow As Long
Dim iCol As Long
Dim OutputLayout() As Variant
'自動計算のOFF
Application.Calculation = xlManual
'一括でデータを取得し
OutputLayout = Sheet1.Range(Cells(2, 2), Cells(4, 4))
'一括でデータを出力
Sheet1.Range("B6:D8").Value = OutputLayout
'自動計算のON
Application.Calculation = xlAutomatic
End Sub
３．画面更新のOFF
EXCELシートのセル内に値をセットしたり、書式設定等を行うと、画面に表現するために、描画処理が動作します。
データが大量になり、書式の設定等も増えてくると、1つ1つの動作で描画処理が動作してしまい、動作の過程を見たいのでは無い場合には、無駄な動作になってしまいます。
VBAのコード内で、画面の描画処理を抑制する事が出来るので、抑制する処理もセットで、実装すると良いでしょう。
Sub 処理()
Dim iRow As Long
Dim iCol As Long
Dim OutputLayout() As Variant
'画面の描画処理をOFF
Application.ScreenUpdating = False
'自動計算のOFF
Application.Calculation = xlManual
'一括でデータを取得し
OutputLayout = Sheet1.Range(Cells(2, 2), Cells(4, 4))
'一括でデータを出力
Sheet1.Range("B6:D8").Value = OutputLayout
'自動計算のON
Application.Calculation = xlAutomatic
'画面の描画処理をON
Application.ScreenUpdating = True
End Sub
他にも色々高速化する方法はありますが、Excelだけで完結させる主な高速化は、ざっとこんな感じだと思います。
他にも、集計処理を実装している場合、そもそもExcelの集計機能を使って集計させた方が早かったり等色々あると思いますが、今回は、大量のデータを他のシステム等から連携する事が多い方向けで高速化の紹介を書いてみました。
他リクエストがあれば、また載せていきたいと思います。
