配列を使う


パソコン通信時代に書いた、この「VBA高速化テクニック」の中で、間違って伝わっているな~と感じていたのは、冒頭の「画面を止める」と、これ「配列を使う」です。90年代のパソコンはスペックが低かったです。CPUは遅いし、メモリも少なく低速、Windowsの画面描画機能も遅かったし、そもそもExcelもそれほど速くはなかったです。そんな中で、大量のセルにアクセスするのなら、一度配列に入れて配列を操作する方が速いよって話でした。でも、何となく「配列に入れると速い」ということだけが一人歩きして、とにかく何でも「配列は速い!配列は速い!配列!配列ぅ!」って言われてます。

ちょっとやってみましょうか。まずは単純に、データを探す操作から。A列に200,000個の名前が入っています。この中から"田中"を探して、該当するB列の数値を取得します。ちなみに"田中"は一番下のセルA200000にあります。

Sub Test1()
    Dim i As Long, A As Long
    For i = 1 To 200000
        If Cells(i, 1) = "田中" Then A = Cells(i, 2)
    Next i
End Sub
Sub Test2()
    Dim i As Long, A As Long, B As Variant
    B = Range("A1:B200000")
    For i = 1 To 200000
        If B(i, 1) = "田中" Then A = B(i, 2)
    Next i
End Sub

でも、ただ探すだけならFindステートメントでも可能です。ついでに、Findステートメントの速度も計測してみましょう。

Sub Test3()
    Dim i As Long, A As Long, FC As Range
    Set FC = Range("A1:A200000").Find(What:="田中")
    A = FC.Offset(0, 1)
End Sub
力わざ 配列 Find %
1 0.734 0.078 0.125 160.3%
2 0.766 0.078 0.141 180.8%
3 0.656 0.078 0.140 179.5%
4 0.734 0.094 0.141 150.0%
5 0.750 0.094 0.140 148.9%
6 0.718 0.078 0.140 179.5%
7 0.704 0.094 0.156 166.0%
8 0.750 0.078 0.141 180.8%
9 0.750 0.078 0.141 180.8%
10 0.703 0.109 0.148 135.8%
平均 0.727 0.086 0.141 166.2%

%は配列方式とFindステートメントの比です。なるほど、配列を使うと速いです。でも、ちょっと待ってくださいよ。これ、200,000個のセルを探しているんです。200,000個ですよ。200,000個のセルを1つずつ見ていく力わざでも、約0.7秒しかかかっていないんです。これだけでも相当速いですよね。もちろんFindステートメントを使えばもっと速いです。0.14秒です。200,000個のセルですよ。確かに配列に入れると速いんですけど、そもそも今のExcelは、普通にセルを操作しても十分速いんです。

違うんです。配列を使って信じられないくらいマクロを速くするのは、こういう使い方じゃないんです。たとえば、次のようなケースで考えてください。A列には名前が10,000個入っています。これらの名前に該当する数値が、セル範囲D2:E6に入力されています。A列の名前に該当する数値を、VLOOKUP関数で調べて、それぞれB列に代入していきます。

ワークシート上のセルを1つずつ操作するのは遅いんでしたよね。

こんなときは一度配列に入れて、配列内を操作する方が速いはずです。

Sub Test4()
    Dim i As Long
    For i = 2 To 10001
        Cells(i, 2) = WorksheetFunction.VLookup(Cells(i, 1), Range("D2:E6"), 2, False)
    Next i
End Sub
Sub Test5()
    Dim i As Long, B As Variant
    B = Range("A1:A10001")
    For i = 2 To 10001
        Cells(i, 2) = WorksheetFunction.VLookup(B(i, 1), Range("D2:E6"), 2, False)
    Next i
End Sub
セル 配列 %
1 15.313 12.687 82.9%
2 13.313 12.844 96.5%
3 12.453 11.828 95.0%
4 12.640 11.500 91.0%
5 13.594 12.250 90.1%
6 12.672 12.656 99.9%
7 12.156 11.984 98.6%
8 11.609 11.375 98.0%
9 11.547 11.219 97.2%
10 12.156 13.000 106.9%
平均 12.745 12.134 95.2%

どうですか?速いですか?そんなに違わないでしょ。そうじゃないんです。遅いのは「セルの値を取得」するところじゃなく「セルに代入」するところなんです。上記のマクロは、いずれにしてもVLOOKUP関数の結果を、1つずつセルに代入しています。全部で10,000回です。この回数を減らすとマクロは劇的に速くなります。じゃ、どうしたらいいか。セルに代入する値をすべて配列に入れておいてその配列を1回だけ代入してやるんです。

Sub Test6()
    Dim i As Long, B As Variant
    ReDim B(9999, 0)
    For i = 2 To 10001
        B(i - 2, 0) = WorksheetFunction.VLookup(Cells(i, 1), Range("D2:E6"), 2, False)
    Next i
    Range("B2:B10001") = B
End Sub
セル 配列1 配列2 %
1 15.313 12.687 0.188 1.2%
2 13.313 12.844 0.203 1.5%
3 12.453 11.828 0.172 1.4%
4 12.640 11.500 0.157 1.2%
5 13.594 12.250 0.156 1.1%
6 12.672 12.656 0.172 1.4%
7 12.156 11.984 0.156 1.3%
8 11.609 11.375 0.172 1.5%
9 11.547 11.219 0.156 1.4%
10 12.156 13.000 0.172 1.4%
平均 12.745 12.134 0.170 1.3%

劇的に速くなるでしょ。ポイントは配列に入れることじゃなく、配列をセルに代入することなんです。ただ、こうなると実務では難易度が上がります。上記の例では、データの件数が10,000件と決め打ちしました。実務では毎回分かりませんよね。代入するデータを一時的に格納するには、動的配列を使わなければなりません。しかも二次元配列です。今回のケースで、もしデータの件数が分からなかったら次のようになります。

Sub Test7()
    Dim i As Long, B As Variant, n As Long
    n = Cells(Rows.Count, 1).End(xlUp).Row
    ReDim B(n - 2, 0)
    For i = 2 To n
        B(i - 2, 0) = WorksheetFunction.VLookup(Cells(i, 1), Range("D2:E6"), 2, False)
    Next i
    Range(Range("A2"), Cells(Rows.Count, 1).End(xlUp)).Offset(0, 1) = B
End Sub

どうです?難しいでしょ。動的な二次元配列をイメージするのも難しいですし、代入するセル範囲(ここではB列)を自動的に特定するのも難しいです。もちろん実際の実務では、さらに複雑になって難易度があがります。つまり、誰にでもできる簡単なマクロではないんです。教えて、誰もがすぐできるようになるわけじゃないですし、少なくともネットのコードを意味も分からずコピペしているレベルでは、おそらく無理でしょうね。そんなときは、潔くあきらめてくださいな。