配列を使う

---

パソコン通信時代に書いた、この「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列)を自動的に特定するのも難しいです。もちろん実際の実務では、さらに複雑になって難易度があがります。つまり、誰にでもできる簡単なマクロではないんです。教えて、誰もがすぐできるようになるわけじゃないですし、少なくともネットのコードを意味も分からずコピペしているレベルでは、おそらく無理でしょうね。そんなときは、潔くあきらめてくださいな。