非IT企業に勤める中年サラリーマンのIT日記

非IT企業でしかもITとは全く関係ない部署にいる中年エンジニア。唯一の趣味がプログラミングという”自称”プログラマー。

Excel/VBA: セルのデータを爆速で配列に格納する方法があった!

   

Excel/VBAを使う上で、セル上のデータを扱うことが多くあると思いますが、セルの参照って一番速度に影響します。なので、セルデータをいったん二元配列に格納してから処理すると速度アップします。が、この配列化にはどうしてもセルの参照が伴うので、ここで速度を落としてしまうんです。

ところが、Excel/VBAにはセルデータを一瞬で配列に格納する方法があります。今回それを紹介。

[ad#top-1]

まずは一般的な方法で速度測定

まずはExcelで100,000行×10行のデータを用意します。これを配列に格納していきましょう。

 

10万行までデータが埋まっています。↓

 

これをVBAで配列に格納します。一般的に下記のようにループ処理で1個1個データを入れていきます。

Sub Test1()
   Dim buf(100000, 10) As Double
   For i = 1 To 100000
      For j = 1 To 10
         buf(i, j) = Cells(i, j)
      Next j
   Next i
End Sub
 

 

この方法で速度測定すると、僕のパソコンで約16秒かかりました。

 

次にスピードアップバージョン

上のデータをそのままに、今度は以下の方法で配列化してみます。

Sub TEST2()
   Dim buf As Variant
   buf = Range("A1:J100000")
End Sub
 

 

前回に比べ、コードもすっきりしています。いちいちループ化せずとも、RangeオブジェクトでVariant型のbuf変数に一気に格納します。

この方法で速度を測定すると、なんとたったの1秒しかかかりませんでした。

 

まとめ

上で紹介したTEST1とTEST2の速度をまとめると下表のとおりです。

TEST1速度(秒) TEST2速度(秒) 割合
15.99999997 0.99999961 6.2%

TEST2の方がたった6.2%しか時間がかかっていません。

Excel/VBAには以前も紹介した通り速度を上げるコツが多くありますので、まずは調べてみてからプログラミングすると良いでしょう。

[ad#ad-1]

スポンサーリンク

 - Excel, VBA