ワークシートの関数をVBAから利用する方法

VBA用の関数もたくさんありますが、ワークシートに書き込む時に使用する関数もたくさんあります。ワークシート用の関数、たとえば「=Sum()」等がVBAで使用出来れば便利だと思いませんか？。ちゃんとサポートされています。ワークシートの関数を利用するには「WorksheetFunction.関数名（引数1,引数2・・・）」と書きます。以下合計関数のSum関数、表から検索するVLookup関数をVBAで使用してみましょう。

Sum関数

Sum関数は、Excelで作業する中で、最もよく使用される関数だと思います。Sum関数を使わないVBAと使うVBAを作成してみます。セルに以下の図のような数値を設定しました。

図5：Sum関数の例題用データ

まず、Sum関数を使わずにVBAで合計の計算をしてみましょう。

［リスト4］Rei_sum1（その他の関数.xlsm）

Sub Rei_sum1()
    g = 0
    For y = 2 To 8
        g = g + Cells(y, 2)
    Next
    Cells(9, 2) = g
End Sub

For文を使ってセルのデータを加算しています。加算部分をSum関数に変更してみます。

［リスト5］Rei_sum2（その他の関数.xlsm）

Sub Rei_sum2()
    g = WorksheetFunction.Sum(Range("B2:B8"))
    Cells(9, 2) = g
End Sub

今回の例のように、数値の数が少ない場合は、処理時間の差はあまり感じません。しかし数値の数が多くなると処理時間に差が出てきます。内部でまとめて計算するのと、毎回VBAの命令まで戻って一個一個計算するのとでは、内部で計算するほうがずいぶん早くなります。 セルの中に組み込むと、セルの変化があるたびに再計算を行います。（再計算無の設定もできますが）必要な時、VBAで一回のみ計算させるというのも、処理の中で便利ではないでしょうか。

VLookup関数

VLoopup関数は便利な関数ですが、設定がよくわからない、などで使っていない人が多いようです。VLookup関数とは、表を作成して、表の1項目のデータを指定すると、指定したデータが書かれている行の、指定した他の項目を取り出してくれるものです。言葉ではピンとこないかもしれないので、例題を書きます。以下が例題用の表です。

図6：VLookup関数の例題用データ

F2からG7までが取り出したい表になります。表の左のデータ100から105をB2に書きB4に表の右の商品名を表示したいとき、B4のセルにVLookup関数を書きます。以下が書き方です。

［構文］VLookup関数

VLookup（検索値、範囲、列番号、検索方法）
    検索値：検索するデータが入っているセル番号
    範囲：検索する表の範囲
    列番号：表の左を1として、取り出したい列の番号
    検索方法：TRUE（あいまい検索）かFALSE（完全一致）「省略時：TRUE」

検索するデータは必ず1列目（左端）に書きます。データは昇順に並べ替えておく必要があります。図6の例題の場合「=VLOOKUP(B2,F2:G7,2,FALSE)」となります。以下、設定したものを図示します。

図7：VLookup関数をセルに設定

VLookup関数をVBAのプログラムで再現してみましょう。

［リスト6］Rei_VLookup1（その他の関数.xlsm）

Sub Rei_VLookup1()
    y = 2
    Cells(4, 2) = ""
    Do While Cells(y, 6) <> ""
        If Cells(2, 2) = Cells(y, 6) Then
            Cells(4, 2) = Cells(y, 7)
            Exit Do
        End If
        y = y + 1
    Loop
 End Sub

Do While文で表を検索し、セルB2に書かれてある数値と表の左の数値と比較して同じであれば右側のデータをセルB4に設定します。同じことをワークシートの関数を使って作成してみます。

［リスト7］Rei_vlookup2（その他の関数.xlsm）

Sub Rei_VLookup2()
    Cells(4, 2) = WorksheetFunction.VLookup(Range("B2"), Range("F2:G7"), 2)
End Sub

Sum関数と同じく検索データが多くなるほど上記2つのVBAの処理速度に差が出てきます。 他のワークシート用関数もVBAを作成する中で使用するとより効率の良いプログラムが組めると思います。

まとめ

数値関数、文字列関数、日付関数以外の関数を扱いました。VBAはまだまだ沢山の関数を持っています。インターネット上にもいろいろな使い方は発表されています。検索サイトで検索して、調べてみると面白い発見があるのではないかと思います。

組み込み関数編は今回で区切りにしたいと思います。次回はSubプロシージャに注目して説明していきたいと思います。

WINGSプロジェクト 横塚利津子著/山田祥寛監修
＜WINGSプロジェクトについて＞テクニカル執筆プロジェクト(代表山田祥寛)。海外記事の翻訳から、主にWeb開発分野の書籍・雑誌/Web記事の執筆、講演等を幅広く手がける。2009年4月時点での登録メンバは30 名で、現在も一緒に執筆をできる有志を募集中。