ハウツーIT

ソフトやサービスの手順解説サイト

*

エクセルVBA 配列の速度比較

2014/05/28        この記事の所要時間:約 4 分

ExcelのVBAでプログラムを組む際に、大量のデータを処理するのに使うのが配列系の処理である。ただ、プログラムの組み方によっては処理時間に大きく差が出てくるようだ。データ量が少なければよいが、多くなってくると、処理時間が延びてしまい、使い勝手も悪くなる。そこでベストな配列の使い方を考慮してみる。

スポンサーリンク

VBAでは主に配列として使えるのが、静的配列、動的配列、コレクションである。3択になるわけだがどれを選ぶのがよいだろうか?

試しに単純に変数を宣言して、適当なデータ(10,000,000個)を配列に書き込む時間を測定してみた。できるだけ高速で処理時間を短縮できるものを選択したい。

■ 静的配列


Dim Dimtest(10000000) As String
Do
   DoEvents
   Dimtest(i) = "テスト" & i
   If i = 10000000 Then
      Exit Do
   End If
   i = i + 1
Loop

処理時間→35秒

 

■動的配列


Dim Dimtest() As String
Do
   DoEvents
   ReDim Preserve Dimtest(i)
   Dimtest(i) = "テスト" & i
   If i = 10000000 Then
      Exit Do
   End If
   i = i + 1
Loop

処理時間→102秒

 

■コレクション

Dim CL As Collection
Set CL = New Collection
Do
   DoEvents
   CL.Add "テスト" & i
   If i = 10000000 Then
      Exit Do
   End If
   i = i + 1
Loop

処理時間→35秒

 

動的配列はReDimが入るため処理がかなり遅くなるようだ。静的配列とコレクションは意外にもほぼ同じ処理時間であった。これらの結果からコレクションを使うのがベストだ。コレクションであればAddで簡単にデータを追加していけるし、Countでデータ数もすぐにわかる。(逆に静的配列だと固定の領域が作られているので、データが入っているのか、入っていないのかを確かめないといけないし、データ数も自前でカウントしないといけない)

また、このコレクションを読み出す方法でも、処理時間が違う。(下記は65000データをシートに書き出した処理時間である)

■For Eachを使う方法

For Each c In CL
   Workbooks(ThisWorkbook.Name).Sheets(1).Cells(i, 1) = c
   i = i + 1
Next

処理時間→12秒

 

■Forを使う方法

For i = 1 To CL.Count
   Workbooks(ThisWorkbook.Name).Sheets(1).Cells(i, 1) = CL(i)
Next

処理時間→16秒

For Eachを使ったほうが速いという結果が出た。もしかしたら環境によっても変わるかもしれないが、私の環境ではこのような結果となった。

あくまでも参考程度にして欲しいが、少しでも処理時間を短縮したいという方は試してみると良いだろう。

 ↓↓↓ 役立った!と思ったら、共有していただけると嬉しいです ↓↓↓

 - Excel

     

Message

メールアドレスが公開されることはありません。

CAPTCHA


※コメントは管理人承認後に表示されます。