関数も使え！

Excelには多くの機能があります。並べ替えやオートフィルタなどメニューから操作する機能、SUMやAVERAGEなどのワークシート関数、多彩なグラフ機能、クロス集計も手軽に行えるピボットテーブル、そして操作を自動化できるマクロ機能。VBAを使ったマクロ作成というのは、Excelが持つ機能の一部を利用しているに過ぎません。ここで忘れていけないのは、我々はマクロ機能だけを相手にしているのではなく、Excelというアプリケーションソフトを操作しているということです。

今回は、VBAのコードの中でもワークシート関数を有効活用しようという話です。まずは数年前と同じ検証をしてみます。次の結果は、セル範囲M1:M100の数値を合計する検証です。Test1では「Ans = Ans + Cells(13, i).Value」とVBAのコードだけで合計を求め、Test2ではワークシート関数のSUM関数を使って合計しています。

	Test1	Test2	%
1回目	00:10	00:00	0.0%
2回目	00:10	00:00	0.0%
3回目	00:10	00:00	0.0%
4回目	00:10	00:01	10.0%
5回目	00:10	00:00	0.0%
6回目	00:10	00:00	0.0%
7回目	00:11	00:00	0.0%
8回目	00:10	00:00	0.0%
9回目	00:10	00:01	10.0%
10回目	00:10	00:00	0.0%
平均	00:10	00:00	2.0%

結果は明かですね。計測時間の00:00というのは、0秒未満で終了したという意味です。
Excelのワークシート関数は非常に高速です。それは、ワークシート上で使用したとき、セルのデータを変化させたことによる再計算の速さからもうかがい知れます。こうした高速な機能を、VBAでも使わない手はありません。

Sub Test1()
    Dim i As Long, j As Long, Ans As Long
    For j = 1 To 5000
        For i = 1 To 100
            Ans = Ans + Cells(13, i).Value
        Next i
    Next j
End Sub

Sub Test2()
    Dim i As Long, Ans As Long
    For i = 1 To 5000
        Ans = WorksheetFunction.Sum(Range("M1:M100"))
    Next i
End Sub

もちろん使用できるのはSUM関数だけではありません。次のようなデータベース的マクロではどうでしょう。

上のようなリストが10000人分(A1:B100001)あります。この中から「田中亨」さんの得点を調べます。ちなみに「田中亨」さんは、10000人目つまりセルA10001に入力されています。

	Test1	Test2	%
1回目	00:06	00:01	16.7%
2回目	00:06	00:00	0.0%
3回目	00:06	00:00	0.0%
4回目	00:06	00:00	0.0%
5回目	00:06	00:01	16.7%
6回目	00:05	00:00	0.0%
7回目	00:06	00:00	0.0%
8回目	00:06	00:00	0.0%
9回目	00:06	00:01	16.7%
10回目	00:06	00:00	0.0%
平均	00:06	00:00	5.1%

こちらも圧倒的な高速化です。
下のコードを見ると一目瞭然ですが、Test1ではFor Nextステートメントでセルを1つずつチェックしていきます。ゴールはセルA10001ですから、繰り返し処理も10,000回行われているのです。さらに、もし検索の対象が1行目で見つかっても、このコードでは毎回10,000回の無駄を繰り返します。

余談ですが、他のプログラミング言語に精通された方が「Excelって縦横集計するソフトだろ？VBAなんて簡単さ」なんてなめてかかると、マクロ記録のコードをそのまま使ったり、こうした超遅いコードを書いてしまいがちです。そのくせ「やっぱマクロは遅いな～」と訳知り顔で言われると落胆します。

VBAが遅いのではありません。あなたのコードが遅いのです。

Sub Test1()
    Dim i As Long, j As Long, Ans As Long
    For j = 1 To 30
        For i = 1 To 10001
            If Cells(i, 1) = "田中亨" Then
                Ans = Cells(i, 2).Value
            End If
        Next i
    Next j
End Sub

Sub Test2()
    Dim fc  As Variant, j As Long, Ans As Long
    For j = 1 To 30
        Ans = WorksheetFunction.VLookup("田中亨", Range("A1:B10001"), 2, False)
    Next j
End Sub

ちなみに、こうした検索ではFindステートメントも有効です。参考までに、Test1との比較をご覧ください。

	Test1	Test3	%
1回目	00:06	00:01	16.7%
2回目	00:06	00:01	16.7%
3回目	00:06	00:00	0.0%
4回目	00:06	00:01	16.7%
5回目	00:06	00:00	0.0%
6回目	00:05	00:01	20.0%
7回目	00:06	00:01	16.7%
8回目	00:06	00:00	0.0%
9回目	00:06	00:01	16.7%
10回目	00:06	00:01	16.7%
平均	00:06	00:01	11.9%

ここでは速度の差を検証するために、10,000行目を検索する処理を30回繰り返しています。
VLOOKUP関数やFindステートメントは、それでも1秒前後で終わるのです。1回だけの処理でしたら、時間を意識することもないでしょう。10,000件程度のデータベースならExcelで十分…と私が思っているのは、VBAがこうしたポテンシャルを持っているからです。

Sub Test3()
    Dim fc  As Variant, j As Long, Ans As Long
    For j = 1 To 30
        Set fc = Range("A1:A1001").Find(What:="田中亨")
        If Not fc Is Nothing Then Ans = fc.Offset(0, 1).Value
    Next j
End Sub