大量のデータを高速でセルに書き込む方法

VBAを使用してエクセルのセルの中にデータを書き込む方法はいくつかあります。しかし、大量のデータをセルに書き込む時には、方法によって、かかる時間に大きな差異が生じます。
今回は最も早いと思われる書き込み方法を、一般的な方法と比較して説明します。

比較検証用データ

下の図のように、横軸に角度（度）、縦軸をsinの値としたSINカーブのデータを作成することを考えます。

データ数は多くしたいので、角度を0.01度刻みとすることで、5000度までのデータを作成するので、合計50万点のデータを扱うことにします。

以下により、サンプルプログラムを作成し、動画で処理時間の比較をして検証してみます。

一般的なセルへの書き込み方法

サンプルコード①

以下のサンプルコードのように、”For～Next"のループ中でSINカーブのデータを発生させています。
（X座標は"theta"の値、Y座標は”Sin(rtheta)"の値となります。

”For～Next"のループ中でセルに値を書き込んでいく方法としては
  Cells(行インデックス,列インデックス)
により単一のセルを指定しながら
  Cells(i,j)
等のループの変数をインデックスとして処理する方法が一般的でしょう。

Option Explicit

Sub test1()
    Const pi = 3.14159265358979 '円周率
    Const div = 0.01            '角度の分割（度）
    Const nRepeat = 500000      'データの点数
  
    Dim i As Long
    Dim theta As Double         '角度（度）
    Dim rtheta As Double        '角度（rad)
    
    For i = 0 To nRepeat
        theta = div * i
        rtheta = theta * pi / 180  '角度のラジアン変換
        
        Sheet1.Cells(i + 2, 2) = theta       'グラフの横軸（X座標の値）
        Sheet1.Cells(i + 2, 3) = Sin(rtheta)  'グラフの縦軸（Y座標の値）
    Next
        
    MsgBox "計算終了" '計算終了を分かりやすくする為にメッセージを表示
End Sub

計算が終了していることを分かりやすくするために、計算終了後、”計算終了”と表示されたメッセージBOXが現れるようにしています。

実行結果

実行結果を下の動画で見てみましょう。（1分間ほど計算中で画面が変化しませんが気長に待って下さい。）

動画の4秒目でプログラムを実行開始し、長々と計算を行っていますが、1分4秒目でメッセージボックスに”計算終了"の文字が現れます。

計算時間は使用するPCの性能により変わってきますが、使用したPCではちょうど1分の計算時間となりました

高速でセルへ書き込む方法

サンプルコード②

先の一般的な書き込み方法から、追加・変更している部分を赤字で示しています。

Option Explicit

Sub test2()
    Const pi = 3.14159265358979 '円周率
    Const div = 0.01          '角度の分割（度）
    Const nRepeat = 500000      'データの点数
  
    Dim i As Long
    Dim theta As Double         '角度（度）
    Dim rtheta As Double        '角度（rad)
    
    Dim sinCurve(nRepeat, 1) As Double
    
    For i = 0 To nRepeat
        theta = div * i
        rtheta = theta * pi / 180
        
        sinCurve(i, 0) = theta      'グラフの横軸（X座標の値）
        sinCurve(i, 1) = Sin(rtheta) 'グラフの縦軸（Y座標の値）
    Next
    
    Sheet1.Range(Cells(2, 2), Cells(nRepeat + 2, 3)) = sinCurve
        
    MsgBox "計算終了"

End Sub

先の一般的な書き込み方法との違いは、2次元配列である”sinCurve(nRepeat, 1)”を用いて、For～Nextのループの中でセルに書き込むのではなく、ループ内では配列の中にデータを格納するだけにしています。

そしてループによる繰り返し計算が終了した後で
  Sheet1.Range(Cells(2, 2), Cells(nRepeat + 2, 3)) = sinCurve
により、”Range”によりセルの範囲を指定して、その範囲全体に配列を書き込んでいます。

実行結果

今回の方法での、実行結果を下の動画で見てみましょう。

今回は実行開始してから、約1秒でメッセージボックスに”計算終了"の文字が現れ計算が終了しました。

結果、60倍ほど計算が早くなりました

VBAの計算が重い場合は、読み込み部分や計算分よりも、セルへデータを書き込む部分で今回のような貼り付け方法等の工夫をすることで、劇的に計算時間が早くなることが多いです。

Range(Cells(※, ※), Cells(※, ※))は要注意！

今回は何の問題もなく ”Range(Cells(※, ※), Cells(※, ※))" により書き込みを行うことが出来ました。
しかし、このセルの範囲の指定方法は知っておかないと必ず嵌ってしまう落とし穴があります。

また、2次元配列やその扱い方にも落とし穴が多く、次回の記事で、その落とし穴と対処方法を説明したいと思います。

  ⇒次回記事、セルの範囲の指定方法の注意点へ