Excel VBA：高速化 >セル参照：セルの値をRangeに設定する

概要

原則、1セルずつ設定せずに行単位で Range に設定する。
大量データをセルに設定する場合は、表単位での Range への設定を検討する。

詳細

対応例


  Public Sub Test1(ByVal MAX_COUNT As Long)
      Dim y As Long
      For y = 1 To MAX_COUNT
          Cells(y, 1) = y
          Cells(y, 2) = y + 1
      Next
  End Sub


  Public Sub Test2(ByVal MAX_COUNT As Long)
      Dim y As Long
      For y = 1 To MAX_COUNT
          Range(Cells(y, 1), Cells(y, 2)) = Array(y, y + 1)
      Next
  End Sub


  Public Sub Test3(ByVal MAX_COUNT As Long)
      ReDim cs(MAX_COUNT, 2) As String
      Dim y As Long
      For y = 1 To MAX_COUNT
          cs(y, 1) = y
          cs(y, 2) = y + 1
      Next
      Range(Cells(1, 1), Cells(MAX_COUNT, 2)) = cs
  End Sub

説明

VBA で最も処理時間が掛かるのは、Excelシートに対してアクセスする処理です。
複数セルを一度に Range に設定することで、Excelシートへのアクセス回数が減らすことができます。一括で設定できる列数が増加するほど速度は向上します。

【特記事項】

行単位での Range への設定は、一次配列を使用する。（Test2 では Array関数で一次配列化をして設定）
表単位での Range への設定は、二次元配列を使用するため、予めデータ件数が分かっている必要がある。
大量セルを一括で設定する場合は、想定最大値が二次元配列に展開できるか確認が必要。

行単位の設定は、劇的な高速化は望めませんが、可読性や保守性をさほど低下せずに実装できるため、有効な手段と思われます。

参考

速度検証

MAX_COUNT	改修前 (a)	改修後1 (b)	改修後2 (c)	速度比：(b)/(a)	速度比：(c)/(a)
1,000	0.1898秒	0.1359秒	0.0578秒	71.6%	30.4%
10,000	1.1672秒	0.6898秒	0.1516秒	59.1%	13.0%
100,000	11.3195秒	7.7516秒	1.1945秒	68.5%	10.6%

処理時間：10回実施した結果の平均値。画面の再描画を止めて計測。（参考：『画面の再描画/自動計算を止める』）
実施環境：Excel 2010 / Intel(R) Core(TM) i5 CPU M450 2.40GHz / Windows 7 Home SP1 64bit。