Excelスキルアップコレクション
           〜 Something U Want 〜


Google  

WWW を検索
Excelスキルアップコレクション内 を検索
 @     Top  >     Excel VBA スキルアップコレクション Topics  >     (05) シート上のデータを高速に配列に取込む

 
(05) シート上のデータを高速に配列に取込む

 行番号+列番号が書出されたシートのデータをVBAの配列に取込む処理を考えます。

 行方向に1,000、列方向に100の範囲にて、VBAからこの処理を行う場合、どのようなプログラムが高速でしょうか?

 次の3つのプログラムを作成しパフォーマンス比較します。
 3つのプログラムを紹介する前に、各プログラムで次のユーザ定義型を利用するので紹介しておきます。String型の動的配列を要素に持つ構造体になっています。使い方は実際のプログラムを見て理解して下さい。

 1行目:  ' 文字列型動的配列を要素に持つ構造体
 2行目:  Public Type orgType
 3行目:      D() As String
 4行目:  End Type
[ プログラム1 ]

1行目:  Public Sub test1()
2行目:      Dim lp1 As Long, lp2 As Long
3行目:      
4行目:      ' Excelシート上のデータをメモリに取出す配列を用意する。
5行目:      Dim dataArr() As orgType
6行目:      ReDim dataArr(0) As orgType
7行目:  
8行目:      ' 行数分ループする。
9行目:      For lp1 = 1 To 1000
10行目:          
11行目:          ' データ取込み用の配列を用意する。
12行目:          Dim dataSize As Long
13行目:          dataSize = UBound(dataArr) + 1
14行目:          ReDim Preserve dataArr(dataSize) As orgType
15行目:          ReDim Preserve dataArr(dataSize).D(0) As String
16行目:          
17行目:          ' 列数分ループする。
18行目:          For lp2 = 1 To 100
19行目:              
20行目:              ' 配列をサイズ拡張しながらデータ格納する。
21行目:              ReDim Preserve dataArr(dataSize) _
22行目:                  .D(UBound(dataArr(dataSize).D) + 1) As String
23行目:              dataArr(dataSize).D(UBound(dataArr(dataSize).D)) = _
24行目:                  Range("A1").Offset(lp1 - 1, lp2 - 1).Value
25行目:          
26行目:          Next
27行目:      
28行目:      Next
29行目:  
30行目:      ' メモリの解放
31行目:      Erase dataArr
32行目:  End Sub
[ プログラム2 ]

 1行目:  Public Sub test2()
 2行目:      Dim lp1 As Long, lp2 As Long
 3行目:      
 4行目:      ' Excelシート上のデータをメモリに取出す配列を用意する。
 5行目:      Dim dataArr() As orgType
 6行目:      ' 配列サイズを少しずつ拡張するのではなく、一度に拡張する。
 7行目:      ReDim dataArr(1000) As orgType
 8行目:  
 9行目:      ' 行数分ループする。
10行目:      For lp1 = 1 To 1000
11行目:  
12行目:          ' データ取込み用の配列を用意する。
13行目:          ReDim Preserve dataArr(lp1).D(100) As String
14行目:          
15行目:          ' 列数分ループする。
16行目:          For lp2 = 1 To 100
17行目:              
18行目:              ' 配列へデータ格納する。
19行目:              dataArr(lp1).D(lp2) = _
20行目:                  Range("A1").Offset(lp1 - 1, lp2 - 1).Value
21行目:          
22行目:          Next
23行目:      
24行目:      Next
25行目:      
26行目:      ' メモリの解放
27行目:      Erase dataArr
28行目:  End Sub
[ プログラム3 ]

1行目:  Public Sub test3()
 2行目:      Dim lp1 As Long, lp2 As Long
 3行目:      Dim RV As Variant
 4行目:  
 5行目:      ' データ範囲のRangeオブジェクトを一旦Variant型の変数に設定する。
 6行目:      Set RV = Range("A1:CV1000")
 7行目:  
 8行目:      ' Excelシート上のデータをメモリに取出す配列を用意する。
 9行目:      Dim dataArr() As orgType
10行目:      ' 配列サイズを少しずつ拡張するのではなく、一度に拡張する。
11行目:      ReDim dataArr(1000) As orgType
12行目:  
13行目:      ' 行数分ループする。
14行目:      For lp1 = 1 To 1000
15行目:      
16行目:          ' データ取込み用の配列を用意する。
17行目:          ReDim Preserve dataArr(lp1).D(100) As String
18行目:          
19行目:          ' 列数分ループする。
20行目:          For lp2 = 1 To 100
21行目:              
22行目:              ' Variant変数から配列へデータ格納する。
23行目:              dataArr(lp1).D(lp2) = _
24行目:                  RV(lp1, lp2).Value
25行目:          
26行目:          Next
27行目:      
28行目:      Next
29行目:      
30行目:      ' メモリの解放
31行目:      Erase dataArr
32行目:  End Sub
簡単にプログラムについて説明します。
[ プログラム1 ]は、仕様を満たす最もスタンダードなプログラムです。二重ループ内で、セルの値を参照しながら配列にデータを取込みます。配列は、セルを参照しながら少しずつ拡張しています。
[ プログラム2 ]は、二重ループ内で、セルの値を参照しながら配列にデータを取込むプログラムです。[ プログラム1 ]と差ないように見えますが、データ取込み先の配列サイズは事前にわかっていることとし、配列のサイズ拡張作業を一回だけにしています。配列のサイズ拡張処理が、プログラム全体の処理にどれだけ影響するものなのかを調べる目的のプログラムです。
[ プログラム3 ]は、少し回りくどい処理を実施しています。6行目でVariant型の変数に、データ取込み対象範囲のRangeオブジェクト(A1:CV1000)を設定しています。その後、シートを参照するのではなく、このVariant変数からデータを読んで、配列に取込んでいます。
実行計測結果を以下に示します。
プログラム測定結果[秒]
111
211
32

 [ プログラム1 ][ プログラム2 ]の測定結果を見る限り、1,000×100程度のデータ量であれば、動的配列のサイズ拡張処理は、それほど処理全体に悪影響を与えるものではないと言えます。また、[ プログラム3 ]は少し回りくどい処理ではあるものの、爆発的に高速であることが実証されました。実行環境により測定結果には違いが生まれますが、これら3つのプログラムの速度比率はほぼ同程度なものが得られるでしょう。
ページの先頭へ