機能と数式 | VBA | セミナー | オンラインソフト | お問い合わせ | その他
Top > Excel > VBA > VBA高速化テクニック

関数も使え!



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

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

 Test1Test2%
1回目00:1000:000.0%
2回目00:1000:000.0%
3回目00:1000:000.0%
4回目00:1000:0110.0%
5回目00:1000:000.0%
6回目00:1000:000.0%
7回目00:1100:000.0%
8回目00:1000:000.0%
9回目00:1000:0110.0%
10回目00:1000:000.0%
平均00:1000:002.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に入力されています。

 Test1Test2%
1回目00:0600:0116.7%
2回目00:0600:000.0%
3回目00:0600:000.0%
4回目00:0600:000.0%
5回目00:0600:0116.7%
6回目00:0500:000.0%
7回目00:0600:000.0%
8回目00:0600:000.0%
9回目00:0600:0116.7%
10回目00:0600:000.0%
平均00:0600:005.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との比較をご覧ください。

 Test1Test3%
1回目00:0600:0116.7%
2回目00:0600:0116.7%
3回目00:0600:000.0%
4回目00:0600:0116.7%
5回目00:0600:000.0%
6回目00:0500:0120.0%
7回目00:0600:0116.7%
8回目00:0600:000.0%
9回目00:0600:0116.7%
10回目00:0600:0116.7%
平均00:0600:0111.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



このエントリーをはてなブックマークに追加