関数も使え！

---

これはもう、高速化テクニックでは鉄板です。しかも、劇的な速度向上になります。使わない手はない。それは、VBAからワークシート関数を呼び出すテクニックです。

現在最新のExcel 2016には約480個のワークシート関数があります。それらすべてではありませんが、ほとんどはVBAから呼び出せます。VBAからワークシート関数を呼び出すときはWorksheetFunctionを使います。

WorksheetFunction.関数名(引数)

まずは簡単なケースから。セル範囲A1:A100000に数値が入力されています。これら数値の合計を求めます。合計を求めるといえば、誰もが知ってるSUM関数ですね。Excelは「SUMに始まりSUMに終わる」と言われています。まぁ、私が勝手に言ってるだけですけど。

Sub Test1()
    Dim i As Long, A As Long
    For i = 1 To 100000
        A = A + Cells(i, 1)
    Next i
End Sub

Sub Test2()
    Dim i As Long, A As Long
    A = WorksheetFunction.Sum(Range("A1:A100000"))
End Sub

回	変数	SUM	%
1	0.547	0.000	0.0%
2	0.344	0.000	0.0%
3	0.438	0.000	0.0%
4	0.359	0.000	0.0%
5	0.360	0.000	0.0%
6	0.437	0.000	0.0%
7	0.344	0.000	0.0%
8	0.406	0.000	0.0%
9	0.375	0.016	4.3%
10	0.344	0.000	0.0%
平均	0.395	0.002	0.4%

検証になりませんねw 笑っちゃうくらい高速です。つーか体感速度0です。

次は、データを探すのをやってみましょう。ここでは下図のようなデータで検証します。

A列に名前が入力されています。200,000件。B列には任意の数値が入力されています。こちらも200,000件。このA列から"田中"を探して、該当するB列の数値を取得します。ちなみに"田中"は最後のA200000に1件だけ存在しています。これ、いろいろな考え方ができます。まずは力わざで、A列のセルを1つずつ「"田中"かどうか」チェックする方法。

Sub Test3()
    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

あるいは、セルを検索するという手もあります。検索とくればFindステートメントですね。

Sub Test4()
    Dim FC As Range, A As Long
    Set FC = Range("A1:A200000").Find(What:="田中", Lookat:=xlWhole)
    If Not FC Is Nothing Then A = FC.Offset(0, 1)
End Sub

でも、このケースだったら、もっといい方法があるじゃないですか。そう、みんな大好きVLOOKUP関数です。

Sub Test5()
    Dim A As Long
    A = WorksheetFunction.VLookup("田中", Range("A1:B200000"), 2, False)
End Sub

回	力わざ	Find	VLOOKUP	%
1	0.750	0.063	0.031	4.1%
2	0.938	0.078	0.047	5.0%
3	0.656	0.079	0.047	7.2%
4	0.656	0.063	0.032	4.9%
5	0.641	0.078	0.047	7.3%
6	0.640	0.062	0.031	4.8%
7	0.641	0.078	0.047	7.3%
8	0.687	0.063	0.047	6.8%
9	0.641	0.063	0.031	4.8%
10	0.641	0.062	0.031	4.8%
平均	0.689	0.069	0.039	5.7%

Findステートメントも悪くないですけど、VLOOKUP関数にはかないません。なんつったってコードも1行ですから。 ％は力わざとVLOOKUP関数の比です。

VBAからワークシート関数を使うと、ものすごく便利です。何よりもコードが短くなるし、それにマクロの速度も速いです。私がよく使うのは、何と言ってもCOUNTIF関数ですね。データの存在確認で使います。

マクロで、たとえばA列に○○というデータが入力されているとき、その○○まで処理するとか、その○○だけを処理するとか。そういうこと多いです。でもそんなとき、そもそもA列に○○が存在しなかったらできないわけです。そんなとき、存在するかしないか、COUNTIF関数だったら一発です。何よりもエラーにならないので使い勝手最強です。

Sub Test6()
    If WorksheetFunction.CountIf(Range("A:A"), "田中") > 0 Then
        ''何かの処理
    Else
        MsgBox "田中は存在しません"
    End If
End Sub

ね、簡単でシンプルでしょ。これ、たとえばFindステートメントでやると

Sub Test7()
    Dim FC As Range
    Set FC = Range("A:A").Find(What:="田中")
    If FC Is Nothing Then
        MsgBox "田中は存在しません"
    Else
        ''何かの処理
    End If
End Sub

こうなりますね。まぁ、ちょっとトリッキーな書き方だったら

Sub Test7()
    If Range("A:A").Find(What:="田中") Is Nothing Then
        MsgBox "田中は存在しません"
    Else
        ''何かの処理
    End If
End Sub

みたいなこともできますけど、ビギナーには読みにくいでしょう。

これほど便利なWorksheetFunctionですけど、世間ではあまり使われていないように感じます。ワークシート関数で一発なのに、ガリガリVBAのコード書いたりして。なぜだろうって考えたんですけど。たとえば、WorksheetFunctionを使ったマクロを作るためには、まず自分のやりたい動作をイメージして「あ！これってワークシート関数で一発やん！」と閃かなければなりません。つまり「ワークシート関数を知っている」人じゃないと使えません。でも、閃いたところで、実際に書くのはVBAです。そう、このWorksheetFunctionを便利に使いこなせるためには「ワークシート関数もVBAも両方できる」ことが必要なんです。たいてい世の中のExcelユーザーは2つに分類できます。ワークシート関数が得意な人はVBAができません。逆にVBAが得意な人はワークシート関数を知りません。以前、私のVBAセミナーに来た人で、SUMIF関数を知らなくて、それをVBAで組んでいた人もいました。おそらく、そんな理由で今ひとつWorksheetFunctionが使われないのかな～って感じています。