Excel マクロ(VBA) で配列を使うことの意味（実用例）

問題点

「『ファイルコードと付随情報』のレコードを持つファイルがある。ファイル名にファイルコードを含むファイルに、レコードを転記したい。コードを書いてみたけれど時間がかかるのだが、処理時間を短くできないか？」という相談を受けました。（レコード数は600行程度）

仮説

「配列を使うと処理が早くできる」という話をよく聞くので、配列を利用して処理速度を上げられないかと考え、試してみました。

対応方法

「既存のコード」と「配列を利用したコード」を用意して、処理にかかる時間を比較する。時間の比較にはTimer関数を使用します。以下に掲載するコードでは、あくまでも配列の記載にフォーカスしたいため「ここをこう書けばもっと早くなるという基本的な記述（例:Application.ScreenUpdating = Falseなど）」は書いていません。

既存のコードと処理時間

Sub kizon()
   startTime = Timer '処理開始時刻を取得する
   For i = 1 To 4
       For j = 1 To 600
           If ThisWorkbook.Worksheets("Sheet1").Cells(i, 5) = ThisWorkbook.Worksheets("Sheet1").Cells(j, 1) Then
               Workbooks.Open Filename:=ThisWorkbook.Path & "\" & ThisWorkbook.Worksheets("Sheet1").Cells(i, 5) & ".xlsx"
               Set wb = ActiveWorkbook
               Last_Row = wb.Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
                   '以下２行（コピペ）はめっちゃ遅いので、比較対象としない
                   'ThisWorkbook.Worksheets("Sheet1").Rows(j).Copy
                   'wb.Worksheets("Sheet1").Cells(Last_Row + 1, 1).PasteSpecial (xlPasteAll)
               'セルを転記
               wb.Worksheets("Sheet1").Cells(Last_Row + 1, 1).Value = ThisWorkbook.Worksheets("Sheet1").Cells(j, 1)
               wb.Worksheets("Sheet1").Cells(Last_Row + 1, 2).Value = ThisWorkbook.Worksheets("Sheet1").Cells(j, 2)
               wb.Worksheets("Sheet1").Cells(Last_Row + 1, 3).Value = ThisWorkbook.Worksheets("Sheet1").Cells(j, 3)
           End If
       Next
   Next
   endTime = Timer '処理終了時刻を取得する
   processTime = endTime - startTime 'かかった時間を算出する
   MsgBox "配列を利用しない場合の処理時間：" & processTime 'メッセージボックスに処理時間を表示する
End Sub

配列を使ったコードと処理時間

Sub hairetsu()
   Dim a()
   Dim c(600, 2) As String 'work用の空の配列を宣言する。「要素数1」は行数を満たす(大きな)数、「要素数2」はカラム数-1（←ゼロから数えるから）
   a = Array("dummy", "001", "002", "003", "004") 'ファイル名コードを配列に格納する
   b = ActiveSheet.Range("A1").CurrentRegion 'シート上のテーブルを配列に格納する
   k = 0
   startTime = Timer '処理開始時刻を取得する
       For i = 1 To 4
               For j = 1 To 600
                   If a(i) = b(j, 1) Then '条件を満たしたレコードを、work配列に書き込む(※)
                       c(k, 0) = b(j, 1)
                       c(k, 1) = b(j, 2)
                       c(k, 2) = b(j, 3)
                       k = k + 1
                   End If
               Next
           Workbooks.Open Filename:=ThisWorkbook.Path & "\" & a(i) & ".xlsx"
           Set wb = ActiveWorkbook
           wb.Worksheets("Sheet1").Range("A1").Resize(UBound(c, 1), UBound(c, 2)) = c '一気に貼り付け
           Erase c '次の上記(※)の処理のために、workの配列を空っぽにする
           k = 0 '次の上記(※)の処理のために、行数もリセットする
       Next
   endTime = Timer　'処理終了時刻を取得する
   processTime = endTime - startTime　'かかった時間を算出する
   MsgBox "配列を利用した場合の処理時間：" & processTime　'メッセージボックスに処理時間を表示する
End Sub

結果

処理にかかる時間が約3分の1に短縮されました。やはり、配列内で処理を行うと早い！

参考にしたサイト

・すごい改善 (VBA講座：配列の活用による処理のスピードアップ策)…勝手に師と崇めている吉田先生のサイト。とてもわかりやすい。
・Yasucore Official Blog (【ExcelVBA】配列とは何か？実例つきで基礎から使い方まで解説！) …LboundやUboundの使い方についても書かれています。今回の配列を使ったコードもUboundを使えばもう少しキレイだったかな。
・SAMURAIENGINEER Blog (【ExcelVBA入門】処理時間を計測するためのTimer関数の使い方とは) …処理時間の計測に、参考にさせていただきました。